[20200120]12c在线统计收集问题.txt

[20200120]12c在线统计收集问题.txt --//昨天看链接：http://blog.go-faster.co.uk/2020/01/on-line-statistics-gathering-disabled.html --//重复测试： 1.环境： SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 CREATE TABLE t1(a number, b varchar2(1000), c number); CREATE TABLE t2(a number, b varchar2(1000), c number); exec dbms_stats.set_table_prefs(user,'t1','METHOD_OPT','FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 254 C'); TRUNCATE TABLE t1; EXEC dbms_stats.delete_table_stats(user,'T1'); INSERT /*+APPEND*/ INTO t1 SELECT ROWNUM a, TO_CHAR(TO_DATE(rownum,'J'),'Jsp') b, CEIL(SQRT(rownum)) c FROM dual CONNECT BY level <= 1e4; commit ; TRUNCATE TABLE t2; EXEC dbms_stats.delete_table_stats(user,'T2'); INSERT /*+APPEND*/ INTO t2 SELECT ROWNUM a, TO_CHAR(TO_DATE(rownum,'J'),'Jsp') b, CEIL(SQRT(rownum)) c FROM dual CONNECT BY level <= 1e4; COMMIT; 2.测试： SCOTT@test01p> SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name LIKE 'T_' ORDER BY 1; TABLE_NAME             NUM_ROWS LAST_ANALYZED -------------------- ---------- ------------------- T1 T2                        10000 2020-01-20 09:42:46 SCOTT@test01p> SELECT table_name, column_name, num_distinct, histogram, num_buckets FROM user_tab_columns WHERE table_name LIKE 'T_' ORDER BY 1,2; TABLE_NAME           COLUMN_NAME          NUM_DISTINCT HISTOGRAM       NUM_BUCKETS -------------------- -------------------- ------------ --------------- ----------- T1                   A                                 NONE T1                   B                                 NONE T1                   C                                 NONE T2                   A                           10000 NONE                      1 T2                   B                           10000 NONE                      1 T2                   C                             100 NONE                      1 6 rows selected. --//可以发现T1表没有统计信息，因为它事先使用dbms_stats.set_table_prefs定义了特殊统计收集方式。 --//摘要：http://blog.go-faster.co.uk/2020/01/on-line-statistics-gathering-disabled.html It appears that I don't get statistics on T1 because I have specified a table statistics preference that is specific to some named columns. It doesn't have to specify creating a histogram, it might be preventing a histogram from being created. For example, this preference does not disable on-line statistics collection. EXEC dbms_stats.set_table_prefs(user,'t2','METHOD_OPT','FOR ALL COLUMNS SIZE 1'); But these preferences do disable on-line statistics collection. EXEC dbms_stats.set_table_prefs(user,'t2','METHOD_OPT','FOR COLUMNS SIZE 1 B C'); EXEC dbms_stats.set_table_prefs(user,'t2','METHOD_OPT','FOR COLUMNS SIZE 1 A B C'); I have not found any other statistics preferences (for other DBMS_STATS parameters) that cause this behaviour. Conclusion Table preferences are recommended as a method of controlling statistics collection declaratively and consistently. You don't have to specify parameters to DBMS_STATS into scripts that collect statistics ad-hoc. The table statistics preferences provide a method that every time statistics are collected on a particular table, they are collected consistently, albeit in a way that may be different from the default. However, take the example of an ETL process loading data into a data warehouse. If you rely on on-line statistics gathering to collect table statistics as a part of a data load process, you must now be careful not to disable statistics collection during the load with METHOD_OPT statistics preferences. Autonomous Addendum In the Oracle Autonomous Data Warehouse, in order to make statistics collection as self-managing as possible, Oracle sets two undocumented parameters. _optimizer_gather_stats_on_load_hist=TRUE - so that histograms are created on every column when on-line statistics are gathered _optimizer_gather_stats_on_load_all=TRUE - so that on-line statistics are collected for every direct-path insert, not just the first one. Creating a column specific METHOD_OPT statistics preference disables this behaviour.