ITPub博客

首页 > 数据库 > Oracle > [20200120]12c在线统计收集问题.txt

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

Oracle 作者:lfree 时间:2020-01-20 09:47:13 0 删除 编辑

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2674064/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2634
  • 访问量
    6397116