原贴地址:http://blog.itpub.net/15415488/viewspace-591765
dbms_stats.gather_table_stats的参数method_opt有很多种用法,其中SIZE语法中有两个关键字是AUTO和SKEWONLY。通过查阅10g官方文档:
method_opt |
Accepts:
size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
- integer : Number of histogram buckets. Must be in the range [1,254]. The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_PARAM |
从字面上解读,AUTO和SKEWONLY的区别就在于收集histograms时ORACLE的选择条件不同。
AUTO会根据column数据分布情况以及column的负载情况进行判断是不是收集这个column的histogram.
而SKEWONLY只会根据column的数据分布情况决定是否收集histogram。
这里,我会强调这个“只”字,因为在SKEWONLY时,只要Oracle觉得数据分布可能不均,就会收集列的柱状图。
而AUTO时,即使某一个column它的数据分布不均,但只要这个column的workload很低,或者说没有workload,那么Oracle就不会收集这个column的histogram。
为什么我会突然想起讨论这个问题呢?
因为今天在tunning一个走full table scan的SQL的时候,这个SQL本可以走一个不错的index,但由于这个index上的第一列数据分布很不均,导致了CBO计算出来的走full table scan的cost小于走这个index的cost(这是通过10053 trace出来的。)。于是我在好几个有同样这个SQL的databases上用dbms_stats.gather_table_stats(method_opt=>for
columns size auto <that column>)收集这个column的histogram。
但是奇怪的是,在某几个用RBO(optimizer_mode=rule)的databases上,通过这样收集统计信息后并没有在那一列上生成histogram。
后来,我就用了method_opt=>for columns size skewonly <that column> 进行收集,结果就成功生成histogram。
于是就产生了我在本文开头的论述和疑问。
这两种收集信息的方式到底有什么不同呢?
于是我在测试坏境中进行了10046 trace event,针对这AUTO和SKEWONLY这两种收集统计信息的方式。
从生成的两份trace文件进行比较,终于被我找到了可疑之处。
AUTO模式的trace文件中清楚地记录了Oracle的一个可疑的行为的一个SQL:
SELECT /*+ ordered use_nl(o c cu h) index(u i_user1) index(o i_obj2)
index(ci_obj#) index(cu i_col_usage$)
index(h i_hh_obj#_intcol#) */
C.NAME COL_NAME, C.TYPE# COL_TYPE, C.CHARSETFORM. COL_CSF,
C.DEFAULT$ COL_DEF, C.NULL$ COL_NULL, C.PROPERTY COL_PROP,
C.COL# COL_UNUM, C.INTCOL# COL_INUM, C.OBJ# COL_OBJ, C.SCALE COL_SCALE,
H.BUCKET_CNT H_BCNT,
(T.ROWCNT-H.NULL_CNT)/GREATEST(H.DISTCNT,1) H_PFREQ, C.LENGTH COL_LEN, CU.TIMESTAMP CU_TIME,
CU.EQUALITY_PREDS CU_EP, CU.EQUIJOIN_PREDS CU_EJP, CU.RANGE_PREDS CU_RP,
CU.LIKE_PREDS CU_LP, CU.NONEQUIJOIN_PREDS CU_NEJP, CU.NULL_PREDS NP FROM
SYS.USER$ U, SYS.OBJ$ O, SYS.TAB$ T, SYS.COL$ C, SYS.COL_USAGE$ CU, SYS.HIST_HEAD$
H WHERE :B3 = '0' AND U.NAME = :B2 AND O.OWNER# = U.USER#
AND O.TYPE# = 2 AND O.NAME = :B1 AND O.OBJ# = T.OBJ#
AND O.OBJ# = C.OBJ# AND C.OBJ# = CU.OBJ#(+) AND C.INTCOL# = CU.INTCOL#(+)
AND C.OBJ# = H.OBJ#(+)
AND C.INTCOL# = H.INTCOL#(+) UNION ALL SELECT /*+ ordered use_nl(c) */
C.KQFCONAM COL_NAME, C.KQFCODTY COL_TYPE, DECODE(C.KQFCODTY, 1, 1, 0)
COL_CSF, NULL COL_DEF, 0 COL_NULL, 0 COL_PROP, C.KQFCOCNO COL_UNUM, C.KQFCOCNO COL_INUM,
O.KQFTAOBJ COL_OBJ, DECODE(C.KQFCODTY, 2, -127, 0) COL_SCALE, H.BUCKET_CNT H_BCNT,
(ST.ROWCNT-NULL_CNT)/GREATEST(H.DISTCNT,1) H_PFREQ, DECODE(C.KQFCODTY, 2, 22, C.KQFCOSIZ) COL_LEN,
CU.TIMESTAMP CU_TIME, CU.EQUALITY_PREDS CU_EP, CU.EQUIJOIN_PREDS CU_EJP,
CU.RANGE_PREDS CU_RP, CU.LIKE_PREDS CU_LP, CU.NONEQUIJOIN_PREDS CU_NEJP,
CU.NULL_PREDS NP FROM SYS.X$KQFTA O, SYS.TAB_STATS$ ST, SYS.X$KQFCO C,
SYS.COL_USAGE$ CU, SYS.HIST_HEAD$ H
WHERE :B3 != '0' AND :B2 = 'SYS' AND O.KQFTANAM = :B1 AND O.KQFTAOBJ = ST.OBJ#(+)
AND O.KQFTAOBJ = C.KQFCOTOB AND C.KQFCOTOB = CU.OBJ#(+) AND C.KQFCOCNO = CU.INTCOL#(+)
AND C.KQFCOTOB = H.OBJ#(+) AND C.KQFCOCNO = H.INTCOL#(+);
而这个SQL并不在SKEWONLY模式的trace文件里。这难道就是传说中的AUTO模式会多去考虑这个列的workload?
答案是肯定的!(其实这个结论在我看到IT-pub上另一篇文章之前,我也不敢如此肯定。。。)
这篇文章论证得其实很清楚,于是post上我论证时参考的文章:
http://www.itpub.net/thread-1096181-1-1.html
当看完此篇文章,顿时心生英雄所见略同的感慨。
我也在此之后做了相似的实验,先把实验结论奉上:
这个SQL果然就是AUTO模式下用来检测the workload of column的,它查询的最重要的一个系统视图就是COL_USAGE$.
当这个视图里有属于这个表这个列当前的monitoring信息时,使用AUTO模式进行收集数据,并且在这列分布情况很skew的情况下,会收集此列的histogram。
反之,当这个视图里没有对此表此列当前monitoring信息时,使用AUTO模式是不会产生此列histogram的,不管此列是否数据极其skew。
而,使用SKEWONLY模式时,则不会去检测COL_USAGE$,只要此列数据skew,则会收集此列histogram。
具体实验如下:
-----------TEST1-----------
SQL> create table test (id number,name varchar2(100));
Table created.
SQL> insert into test select 1,object_name from dba_objects;
23078 rows created.
SQL> insert into test values(2,'xxx');
1 row created.
SQL> commit;
Commit complete.
SQL> create index testidx on test(id);
Index created.
SQL> select TABLE_NAME,COLUMN_NAME,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS,HISTOGRAM from dba_tab_columns
2 where TABLE_NAME='TEST' and COLUMN_NAME='ID';
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------------------------------------------------------------------
LOW_VALUE HIGH_VALUE DENSITY NUM_BUCKETS
---------------------------------------------------------------- ---------------------------------------------------------------- ---------- -----------
HISTOGRAM
---------------------------------------------
TEST ID
NONE
SQL> set lines 180 pages 999
SQL> select ENDPOINT_NUMBER,ENDPOINT_VALUE from dba_histograms where TABLE_NAME='TEST' and COLUMN_NAME='ID';
no rows selected
SQL> select OWNER, OBJECT_ID,OBJECT_NAME from dba_objects where OBJECT_NAME='TEST' and wner='LONGRAW_USER';
OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
LONGRAW_USER 44990 TEST
SQL> select * from sys.COL_USAGE$ where OBJ#=44990; à when
there is no rows in COL_USAGE$, the AUTO will not collect histograms.
no rows selected
SQL> exec dbms_stats.gather_table_stats('LONGRAW_USER','TEST',estimate_percent=>100,cascade=>true,no_invalidate=>true,method_opt=>'for columns size auto ID');
PL/SQL procedure successfully completed.
SQL> set lines 180 pages 999
SQL> select ENDPOINT_NUMBER,ENDPOINT_VALUE from dba_histograms where TABLE_NAME='TEST' and COLUMN_NAME='ID';
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 1
1 2
SQL> select TABLE_NAME,COLUMN_NAME,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS,HISTOGRAM from dba_tab_columns
2 where TABLE_NAME='TEST' and COLUMN_NAME='ID';
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------------------------------------------------------------------
LOW_VALUE HIGH_VALUE DENSITY NUM_BUCKETS
---------------------------------------------------------------- ---------------------------------------------------------------- ---------- -----------
HISTOGRAM
---------------------------------------------
TEST ID
C102 C103 .5 1
NONE
------------TEST2---------------
SQL> select * from test where id=2; à it
is to generate some statistics in COL_USAGE$
ID
----------
NAME
-------------------------------------------------------------------------------------------------------------------------------------------------------------
2
xxx
(waiting for some while)
SQL> select * from sys.COL_USAGE$ where OBJ#=44990; à We
can see that there is more row with EQUALITY_PREDS/RANGE_PREDS
OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- ---------------
44990 1 1 0 0 1 0 0 24-APR-09
SQL> exec dbms_stats.gather_table_stats('LONGRAW_USER','TEST',estimate_percent=>100,cascade=>true,no_invalidate=>true,method_opt=>'for columns size auto ID');
PL/SQL procedure successfully completed.
SQL> set lines 180 pages 999
SQL> select ENDPOINT_NUMBER,ENDPOINT_VALUE from dba_histograms where TABLE_NAME='TEST' and COLUMN_NAME='ID';
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
23078 1
23079 2
SQL> select TABLE_NAME,COLUMN_NAME,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS,HISTOGRAM from dba_tab_columns
2 where TABLE_NAME='TEST' and COLUMN_NAME='ID';
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------------------------------------------------------------------
LOW_VALUE HIGH_VALUE DENSITY NUM_BUCKETS
---------------------------------------------------------------- ---------------------------------------------------------------- ---------- -----------
HISTOGRAM
---------------------------------------------
TEST ID
C102 C103 .000021665 2
FREQUENCY
----------------TEST finishes--------------------
COL_USAGE$是关键的一个视图,在metalink上也会找到此视图的很多Bug。
既然说到它了,就再介绍下其中有个bug是说这个视图会经常无限增长。直到不能再增长。。。
而workaround是:
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
如果某些表没用了,你也可以drop table XXX purge来删除此表相对应的monitoring stats.
在搞清楚了这些问题之后,接着回到我先前遇到的一个问题:
为什么在RBO的数据库下用AUTO模式收集histogram失败了呢?
于是又引出了一个我自己通过实验得出的猜测性结论,还没有经过全方位的确认,仅仅是经验上的结论:
RBO一般情况下不会自动收集monitoring stats在COL_USAGE$中。
举个例子,其中一个RBO database:
SQL> select * from COL_USAGE$ where OBJ#=7840;
OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- ---------
7840 1 10 10 0 0 0 0 15-APR-09
而拿一个CBO database作为比较:
SQL> select * from COL_USAGE$ where OBJ#=7840;
OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- ---------------
7840 1 50 15 0 0 0 024-APR-09
7840 2 14 0 0 0 0 024-APR-09
7840 4 18 0 0 0 0 024-APR-09
7840 15 20 0 0 0 0 1524-APR-09
7840 12 6 0 0 0 0 1 24-APR-09
7840 3 4 0 0 0 0 0 24-APR-09
7840 16 7 0 0 0 0 19 24-APR-09
7840 5 7 0 0 0 0 0 23-APR-09
7840 10 42 0 0 2 0 0 24-APR-09
7840 6 11 0 0 0 0 0 23-APR-09
7840 7 6 0 0 0 0 0 23-APR-09
7840 13 7 0 0 0 0 3 23-APR-09
7840 14 1 0 0 0 0 5 23-APR-09
7840 8 2 0 0 0 0 0 15-APR-09
7840 17 2 0 0 0 0 3 23-APR-09
ps,今天是2009-4-24,从上可以看出RBO以前那row很明显已经过期了,但不知道什么时候记录下来对的了。
而CBO的monitoring信息非常current,而这个SQL在这两台数据库上都是每分钟都在跑的,于是得出了前面对RBO的猜测性的结论。
这个结论也欢迎其他同学帮我佐证一下。
此文到此。。。下次再见。