analyze与dbms_stat逻辑读对比测试

1. analyze

SQL> select segment_name,owner,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where tablespace_name='TEST';
 
 
SEGMENT_NAME                   OWNER                          SEGMENT_TYPE       TABLESPACE BYTES/1024/1024
------------------------------ ------------------------------ ------------------ ---------- ---------------
TEST                           SYS                            TABLE PARTITION    TEST                   144
TEST                           SYS                            TABLE PARTITION    TEST                   144
TEST                           SYS                            TABLE PARTITION    TEST                   136
TEST                           SYS                            TABLE PARTITION    TEST                    72
 
SQL> select * from v$mystat  where STATISTIC#=9;
 
       SID STATISTIC#      VALUE
---------- ---------- ----------
        10          9     102842
 
SQL> analyze table test compute statistics;
 
Table analyzed.
 
SQL> select * from v$mystat  where STATISTIC#=9;
 
       SID STATISTIC#      VALUE
---------- ---------- ----------
        10          9     139314
 
SQL> analyze table test delete  statistics;
 
Table analyzed.
 
SQL> select 139314-102842 from dual;
 
139314-102842
-------------
       36472

2. dbms_stats

SQL>  select * from v$mystat  where STATISTIC#=9;
 
       SID STATISTIC#      VALUE
---------- ---------- ----------
        15          9       1196
 
SQL> exec dbms_stats.gather_table_stats(user,'test',cascade=>true);
 
PL/SQL procedure successfully completed.
 
SQL> select * from v$mystat  where STATISTIC#=9;
 
       SID STATISTIC#      VALUE
---------- ---------- ----------
        15          9      75250
 
SQL> select 75250-1192 from dual;
 
75250-1192
----------
     74058

       可见，对于分区表的分析，用dbms_stats来分析，要比analyze要消耗更多的系统资源，这是因为dbms_stats分析更加准确，包括了对partition的分析，这是要付出代价的。呵呵