风哥教程

培训 . 交流 . 分享
Make progress together!

数据库Oracle10g与Oracle11g分区表统计信息收集的方法

[复制链接]
内容发布:风哥| 发布时间:2014-2-10 13:52:18
oracle数据库10g与11g分区表统计信息收集的方法
How To Collect Statistics On Partitioned Table in 10g and 11g (文档ID1417133.1)

General guidelines

  • Gather optimizer statistics, dynamic sampling is not an adequate solution for large volume of data that are typical in tables in a Datawarehousing environment.
  • If feasible run queries against empty table ( before data load ) to populate the columns usage. This provides valuable information for statistics gathering later.
  • Statistics collection should be taking place after the data loaded to the table, but before indexes are created (Oracle automatically gathers statistics on indexes as they are created).
  • For estimate percent:  in 11g, use auto sample size, it has a new hash algorithm, very accurate for NDV (Number of Distinct Values), yet it takes minimal time.
  • For sample size in 10g use 100% if possible, use histogram if you know data is skewed, otherwise be cautious. (Auto sample size tends to use small samples, gives poor estimates of NDVs, often obtains histograms due to column usage statistics, as columns are used in predicated over time.)
  • Use Incremental Statistics Collection in 10g and 11g
    Partitioned Tables Benefit from Global Statistics. Without Global Stats, CBO estimates stats from individual partitions, which can be inaccurate for NDVs. Global stats take longer to obtain and reduce manageability. Without incremental stats collection full table scans used to update global stats, with incremental stats collection when new partitions added, CBO gathers stats on new partition, global stats updated by scanning only updated partitions.
Incremental statistics collection in 10g

In 10.2.0.4 + Patch 6526370 and in 10.2.0.5 new value, 'APPROX_GLOBAL AND PARTITION' for the GRANULARITY parameter of the GATHER_TABLE_STATS procedures simulates the incremental maintenance of global statistics.

We update the NDV of the table. For partitioned column we update the NDV as the sum of NDV at the partition levels. Also we set the NDV of columns of local unique indexes as the number of rows of the table. For non-partitioning columns we do not update the NDV and number of distinct keys of the index at the global level.

In the following example we consider table SALES_INC which is partitioned by time_id, and has statistics gathered. The last partition is empty SALES_Q4_2001. After the partition SALES_Q4_2001 is loaded, we gather statistics by specifying 'APPROX_GLOBAL AND PARTITION' for the GRANULARITY.

Before loading partition SALES_Q4_2001

USER_TABLES show the following values for columns NUM_ROWS and Last_Analyzed:

TABLE_NAME        NUM_ROWS Last Analyzed                                    GLOBAL_ST
--------------- ---------- ------------------------------------------------ ---------
SALES_INC           849094 02/20/2012 14:24                                 YES

USER_TAB_PARTITIONS shows partition SALES_Q4_2001 empty:
TABLE_NAME      PARTITION_NAME  Last Analyzed                                    GLOBAL_ST   NUM_ROWS AVG_ROW_LEN HIGH_VALUE
--------------- --------------- ------------------------------------------------ --------- ---------- ----------- -------------------------------------------------- ...
SALES_INC       SALES_Q4_2000   02/20/2012 14:24                                 YES            55984          28 TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:
SALES_INC       SALES_Q4_2001   02/20/2012 14:24                                 YES                0           0 TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:

USER_TAB_COLUMNS
shows the following NDVs  for the columns of the table:
TABLE_NAME      COLUMN_NAME          NUM_DISTINCT  
--------------- -------------------- ------------
SALES_INC       AMOUNT_SOLD                  3296
SALES_INC       CHANNEL_ID                      4
SALES_INC       CUST_ID                      7056
SALES_INC       PROD_ID                        72
SALES_INC       PROMO_ID                        4
SALES_INC       QUANTITY_SOLD                   1
SALES_INC       TIME_ID                      1368

Then data into partition SALES_Q4_2001 has been loaded.
Statistics has been collected at 14:50 with
EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SH', tabname =>'SALES_INC', partname=>'SALES_Q4_2001', GRANULARITY => 'APPROX_GLOBAL AND PARTITION');

After data load and statistics collection for partition SALES_Q4_2001

USER_TAB_PARTITIONS shows partition level statistics were collected for SALES_Q4_2001 (compare columns NUM_ROWS and LAST_ANALYZED with the result obtained above)

TABLE_NAME      PARTITION_NAME  Last Analyzed                                    GLOBAL_ST   NUM_ROWS AVG_ROW_LEN HIGH_VALUE
--------------- --------------- ------------------------------------------------ --------- ---------- ----------- -------------------------------------------------- ...
SALES_INC       SALES_Q4_2000   02/20/2012 14:24                                 YES            55984          28 TO_DATE(' 2001-01-01 00:00:00', 'SYY
SALES_INC       SALES_Q4_2001   02/20/2012 14:50                                 YES            67872          28 TO_DATE(' 2002-01-01 00:00:00', 'SYY


Note last analyzed for the partition on which statistics needed to be collected shows 14:50. No stats collection was done on other partitions as those did not change since previous collection.
USER_TABLES shows NUM_ROWS increased by the rows of inserted during the data load to the latest partition. The value for the LAST_ANALYZED for the table changed to 14:50,

showing the last update time stamp of the global stats of the table.
TABLE_NAME        NUM_ROWS Last Analyzed                                    GLOBAL_ST
--------------- ---------- ------------------------------------------------ ---------
SALES_INC           916966 02/20/2012 14:50                                 YES

Note NDV of the partition key TIME_ID in USER_TAB_COLUMNS has been made up-to-date
TABLE_NAME      COLUMN_NAME          NUM_DISTINCT
--------------- -------------------- ------------
SALES_INC       AMOUNT_SOLD                  3296      
SALES_INC       CHANNEL_ID                      4      
SALES_INC       CUST_ID                      7056      
SALES_INC       PROD_ID                        72      
SALES_INC       PROMO_ID                        4      
SALES_INC       QUANTITY_SOLD                   1      
SALES_INC       TIME_ID                      1460      

Incremental Statistics Collection in 11g
Oracle will update the global table statistics by scanning only the partitions or subpartitions that have been changed instead of the entire table if all of the following conditions hold:


  • value of the INCREMENTAL property for the partitioned table is set to TRUE (default is false)
    DBMS_STATS.SET_TABLE_PREFS(<owner>,<tablename>,'INCREMENTAL','TRUE');
  • value of the PUBLISH property for the partitioned table is set to TRUE (default)
    DBMS_STATS.SET_TABLE_PREFS(<owner>,<tablename>,'PUBLISH','TRUE');/To query the current value of the above properties for the table see dbms_stats.get_prefs function./
  • statistics collection is running on the table with auto sample size and granularity auto
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZEgranularity      => 'AUTO'

Note about further restrictions:

  • Granularity set to AUTO does not collect statistics on hash sub-partition tables, hence incremental statistics is not happening when the sub-partiton type is hash, see Note 1390718.1 for more information.



See Note 1592404.1 about how and when statistics are collected on partitioned table by the Automatic Optimizer Statistics Collection job.


上一篇:Oracle分区表的维护和索引的相关注意事项
下一篇:AIX操作系统上创建裸设备lv出现的问题
专业提供Oracle/MySQL/NoSQL/Linux数据库培训与技术支持服务,QQ号:113257174
关注风哥教程微信公众号itpux_com  ,了解本站最新技术资料的分享.

欢迎加QQ群,提供超多高质量Oracle/Unix/Linux技术文档与视频教程的下载。

Oracle/MySQL/Linux群4-5:189070296  150201289  
Oracle/MySQL/Linux群6-8:244609803   522261684   522651731
备注:请勿重复加群,另请注明 from itpux
回复

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

热门文章教程

  • 大数据技术与应用入门培训教程(电子版下载
  • Oracle 12cR2 九大新功能全面曝光_详解云数
  • Oracle OCP认证考试IZ0-053题库共712题数据
  • MySQL5权威指南(第3版)PDF电子版下载
  • 风哥Oracle数据库巡检工具V1.0(附2.6网页
  • Oracle19c数据库发布与下载地址
快速回复 返回顶部 返回列表