[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.