○自从Oracle8.1.5引入dbms_...... 自从Oracle8.1.5引入dbms_stats包,Experts们便推荐使用dbms_stats取代analyze。 理由如下 dbms_stats可以并行分析 dbms_stats有自动分析的功能(alter table monitor ) analyze 分析统计信息的不准确some times 1,2好理解,且第2点实际上在VLDB中是最吸引人的;3以前比较模糊,看了metalink236935.1 解释,analyze在分析Partition表的时候,有时候会计算出不准确的Global statistics . 原因是,dbms_stats会实在的去分析表全局统计信息(当指定参数);而analyze是将表分区(局部)的statistics 汇总计算成表全局statistics ,可能导致误差。 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; / 如上建立一个分区表 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 计算了Global Statistics 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 SQL> analyze table t3 delete statistics; Table analyzed. SQL> select GLOBAL_STATS from dba_tables where table_name='T3'; GLO --- YES 看来analyze不能删除dbms_stats的统计信息? 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 2 SQL> select GLOBAL_STATS from dba_tables where table_name='T3'; GLO --- NO 可见,sometimes,analyze出错了。当CBO只需要partition的统计信息的时候还好,当使用表上的全局统计信息的时候,有可能产生不正确的执行计划。 |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-9-30 03:31 , Processed in 0.132297 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.