原贴地址：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]. - REPEAT : Collects histograms only on the columns that already have histograms. - AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns. - SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns. The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_PARAM Procedure. |
从字面上解读，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的猜测性的结论。
这个结论也欢迎其他同学帮我佐证一下。
此文到此。。。下次再见。
扫一扫
2736
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
