今天在研究histogram的时候，很偶然的观察到一个skew列，当我指定size auto并收集统计信息后产生的histogram不对。
由此引入了一个问题：
"当一个表的某一列的值的分布是skew的时候，这时候如果调用DBMS_STATS.GATHER_TABLE_STATS，并且指定size auto，那么上述skew的列是否一定会产生histogram？"
答案是----不一定。
对skew的列指定size auto并采集统计信息的时候，如果col_usage$里没有skew列的使用信息，那么oracle就不会对上述skew列产生histogram。
对上述问题，557594.1里有很详细的解释。
我这里转一下557594.1里的测试结果，很能说明问题：
SQL> conn dras/acca@armsdev;
Connected to Oracle9i
Connected as dras
SQL> create table jk_demo (mycol number);
Table created
SQL> begin
2 -- create a lot of -1's records
3 for i in 1..100000 loop
4 insert into jk_demo values (-1);
5 end loop;
6 -- not many 0's
7 for i in 1..1000 loop
8 insert into jk_demo values (0);
9 end loop;
10 --- not many 7's
11 for i in 1..100 loop
12 insert into jk_demo values (7);
13 end loop;
14 commit;
15 end;
16 /
PL/SQL procedure successfully completed
SQL> create index jkdemoidx on jk_demo (mycol);
Index created
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'DRAS'
3 ,TABNAME =>'JK_DEMO'
4 ,METHOD_OPT =>'FOR ALL INDEXED COLUMNS SIZE AUTO'
5 ,CASCADE =>TRUE);
6 END;
7 /
PL/SQL procedure successfully completed
SQL> select lpad(TABLE_NAME,10), lpad(COLUMN_NAME, 5),
2 endpoint_number, endpoint_value
3 from user_histograms
4 where table_name = 'JK_DEMO';
LPAD(TABLE_NAME,10) LPAD(COLUMN_NAME,5) ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- ------------------- --------------- --------------
JK_DEMO MYCOL 0 -1
JK_DEMO MYCOL 1 7
我们从结果里可以看到这时候对于表JK_DEMO的skew列mycol，实际上是没有正确的histogram信息的。
因为这时候col_usage$里没有列mycol的使用信息：
SQL> select object_id from dba_objects where object_name='JK_DEMO';
OBJECT_ID
----------
57519
SQL> select * from sys.col_usage$ where obj#=57519;
OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -----------
现在我们来让col_usage$里产生列mycol的使用信息：
SQL> select count(*) from jk_demo where mycol = 1;
COUNT(*)
----------
0
SQL> select * from sys.col_usage$ where obj#=57519;
OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -----------
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'DRAS'
3 ,TABNAME =>'JK_DEMO'
4 ,METHOD_OPT =>'FOR ALL INDEXED COLUMNS SIZE AUTO'
5 ,CASCADE =>TRUE);
6 END;
7 /
PL/SQL procedure successfully completed
SQL> select lpad(TABLE_NAME,10), lpad(COLUMN_NAME, 5),
2 endpoint_number, endpoint_value
3 from user_histograms
4 where table_name = 'JK_DEMO';
LPAD(TABLE_NAME,10) LPAD(COLUMN_NAME,5) ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- ------------------- --------------- --------------
JK_DEMO MYCOL 100000 -1
JK_DEMO MYCOL 101000 0
JK_DEMO MYCOL 101100 7
SQL> select * from sys.col_usage$ where obj#=57519;
OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -----------
57519 1 1 0 0 0 0 0 2009-9-4 17
Leave a comment