自從Oracle8.1.5引入dbms_stats包，EXPerts們便推薦使用dbms_stats取代analyze。 理由如下 dbms_stats可以并行分析 dbms_stats有自動(dòng)分析的功能(alter table monitor ) analyze 分析統(tǒng)計(jì)信息的不準(zhǔn)確some times 1,2好理解，且第2點(diǎn)實(shí)際上在VLDB中是最吸引人的；3以前比較模糊，看了metalink236935.1 解釋，analyze在分析Partition表的時(shí)候，有時(shí)候會(huì)計(jì)算出不準(zhǔn)確的Global statistics . 原因是，dbms_stats會(huì)實(shí)在的去分析表全局統(tǒng)計(jì)信息（當(dāng)指定參數(shù)）；而analyze是將表分區(qū)（局部）的statistics 匯總計(jì)算成表全局statistics ,可能導(dǎo)致誤差。drop table T3;CREATE TABLE T3(i number, p number,sp number)PARTITION BY RANGE(p)
SUBPARTITION BY HASH(sp) SUBPARTITIONS 2 (PARTITION q1 VALUES LESS THAN(3) TABLESPACE USERS,
PARTITION q2 VALUES LESS THAN(MAXVALUE) TABLESPACE USERS);declare
i number;
begin
for i in 1..100000 loop
insert into T3 values(i,mod(i,7), mod(i,8));
if( mod(i, 10000) = 0) then commit; end if;
end loop;
for i in 1..50000 loop
insert into T3 values(i,mod(i,7), mod(i,8)+5);
if( mod(i, 10000) = 0) then commit; end if;
end loop;
end;/ 如上建立一個(gè)分區(qū)表SQL> exec DBMS_STATS.GATHER_TABLE_STATS('ORACLE','T3',granularity => 'ALL');PL/SQL PRocedure sUCcessfully completed.SQL> select GLOBAL_STATS from dba_tables where table_name='T3';GLO
---
YES 計(jì)算了Global StatisticsSQL> select COLUMN_NAME,NUM_DISTINCT,DENSITY,SAMPLE_SIZE,AVG_COL_LEN from user_TAB_COL_STATISTICS where table_name='T3';COLUMN_NAME NUM_DISTINCT DENSITY SAMPLE_SIZE AVG_COL_LEN
------------------------------ ------------ ---------- ----------- -----------
I 100000 .00001 150000 5
P 7 .142857143 150000 3
SP 13 .076923077 150000 3
SQL> analyze table t3 delete statistics;Table analyzed.SQL> select GLOBAL_STATS from dba_tables where table_name='T3';GLO
---
YES 看來analyze不能刪除dbms_stats的統(tǒng)計(jì)信息？SQL> analyze table t3 compute statistics;Table analyzed.SQL> select COLUMN_NAME,NUM_DISTINCT,DENSITY,SAMPLE_SIZE,AVG_COL_LEN from user_TAB_COL_STATISTICS where table_name='T3';COLUMN_NAME NUM_DISTINCT DENSITY SAMPLE_SIZE AVG_COL_LEN
------------------------------ ------------ ---------- ----------- -----------
I 100000 .00001 150000 5
P 7 .142857143 150000 3
SP 13 .076923077 150000 3 沒有變，還是一樣。 Confused ?SQL> select GLOBAL_STATS from dba_tables where table_name='T3';GLO
---
YES
SQL> exec dbms_stats.DELETE_TABLE_STATS('oracle','t3');PL/SQL procedure successfully completed.SQL> select GLOBAL_STATS from dba_tables where table_name='T3';
GLO
---
NO 《 ----- 有效果了
SQL> analyze table t3 compute statistics;Table analyzed.SQL> select COLUMN_NAME,NUM_DISTINCT,DENSITY,SAMPLE_SIZE,AVG_COL_LEN from user_TAB_COL_STATISTICS where table_name='T3';COLUMN_NAME NUM_DISTINCT DENSITY SAMPLE_SIZE AVG_COL_LEN
------------------------------ ------------ ---------- ----------- -----------
I 150000 6.6667E-06 4
P 7 .142857143 2
SP 8 .125 2SQL> select GLOBAL_STATS from dba_tables where table_name='T3';GLO
---
NO 可見，sometimes，analyze出錯(cuò)了。當(dāng)CBO只需要partition的統(tǒng)計(jì)信息的時(shí)候還好，當(dāng)使用表上的全局統(tǒng)計(jì)信息的時(shí)候，有可能產(chǎn)生不正確的執(zhí)行計(jì)劃。
