dbms_stats收集统计信息包的默认粒度为AUTO，对于AUTO没有非常明确的解释，一般认为它会收集分区的统计信息，但不包含子分区subpartition。对于这种说明我们加以核实：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> select dbms_stats.get_param('cascade') from dual;
select dbms_stats.get_param('degree') from dual;
DBMS_STATS.GET_PARAM('CASCADE')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_CASCADE
SQL>
DBMS_STATS.GET_PARAM('DEGREE')
--------------------------------------------------------------------------------
NULL
SQL> select dbms_stats.get_param('estimate_percent') from dual;
DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE
SQL> select dbms_stats.get_param('method_opt') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
SQL> select dbms_stats.get_param('no_invalidate') from dual;
DBMS_STATS.GET_PARAM('NO_INVALIDATE')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE
SQL> select dbms_stats.get_param('granularity') from dual;
DBMS_STATS.GET_PARAM('GRANULARITY')
--------------------------------------------------------------------------------
AUTO
CREATE TABLE composite_rng_hash (
cust_id NUMBER(10),
cust_name VARCHAR2(25),
amount_sold NUMBER(10,2),
time_id DATE)
PARTITION BY RANGE(time_id)
SUBPARTITION BY HASH(cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE users,
SUBPARTITION sp2 TABLESPACE users,
SUBPARTITION sp3 TABLESPACE users,
SUBPARTITION sp4 TABLESPACE users) (
PARTITION sales_pre05
VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')),
PARTITION sales_2005
VALUES LESS THAN(TO_DATE('01/01/2006','DD/MM/YYYY')),
PARTITION sales_2006
VALUES LESS THAN(TO_DATE('01/01/2007','DD/MM/YYYY')),
PARTITION sales_2007
VALUES LESS THAN(TO_DATE('01/01/2008','DD/MM/YYYY')),
PARTITION sales_2008
VALUES LESS THAN(TO_DATE('01/01/2009','DD/MM/YYYY')),
PARTITION sales_future
VALUES LESS THAN(MAXVALUE));
Table created.
SQL> select num_rows , blocks from dba_tables where table_name='COMPOSITE_RNG_HASH';
NUM_ROWS BLOCKS
---------- ----------
SQL> select partition_name,num_rows , blocks from dba_tab_partitions where table_name='COMPOSITE_RNG_HASH';
PARTITION_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
SALES_PRE05
SALES_2005
SALES_2006
SALES_2007
SALES_2008
SALES_FUTURE
SQL> select partition_name,SUBPARTITION_NAME,num_rows , blocks from dba_tab_subpartitions where table_name='COMPOSITE_RNG_HASH';
PARTITION_NAME SUBPARTITION_NAME NUM_ROWS BLOCKS
------------------------------ ------------------------------ ---------- ----------
SALES_PRE05 SALES_PRE05_SP4
SALES_PRE05 SALES_PRE05_SP3
SALES_PRE05 SALES_PRE05_SP2
SALES_PRE05 SALES_PRE05_SP1
SALES_2005 SALES_2005_SP4
SALES_2005 SALES_2005_SP3
SALES_2005 SALES_2005_SP2
SALES_2005 SALES_2005_SP1
SALES_2006 SALES_2006_SP4
SALES_2006 SALES_2006_SP3
SALES_2006 SALES_2006_SP2
SALES_2006 SALES_2006_SP1
SALES_2007 SALES_2007_SP4
SALES_2007 SALES_2007_SP3
SALES_2007 SALES_2007_SP2
SALES_2007 SALES_2007_SP1
SALES_2008 SALES_2008_SP4
SALES_2008 SALES_2008_SP3
SALES_2008 SALES_2008_SP2
SALES_2008 SALES_2008_SP1
SALES_FUTURE SALES_FUTURE_SP4
SALES_FUTURE SALES_FUTURE_SP3
SALES_FUTURE SALES_FUTURE_SP2
SALES_FUTURE SALES_FUTURE_SP1
24 rows selected.
SQL> exec dbms_stats.gather_table_stats('SYS','COMPOSITE_RNG_HASH');
PL/SQL procedure successfully completed.
SQL> select num_rows , blocks from dba_tables where table_name='COMPOSITE_RNG_HASH';
NUM_ROWS BLOCKS
---------- ----------
0 0
SQL>
SQL>
SQL> select partition_name,num_rows , blocks from dba_tab_partitions where table_name='COMPOSITE_RNG_HASH';
PARTITION_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
SALES_PRE05 0 0
SALES_2005 0 0
SALES_2006 0 0
SALES_2007 0 0
SALES_2008 0 0
SALES_FUTURE 0 0
1* select partition_name,SUBPARTITION_NAME,num_rows , blocks from dba_tab_subpartitions where table_name='COMPOSITE_RNG_HASH'
SQL> /
PARTITION_NAME SUBPARTITION_NAME NUM_ROWS BLOCKS
------------------------------ ------------------------------ ---------- ----------
SALES_PRE05 SALES_PRE05_SP4
SALES_PRE05 SALES_PRE05_SP3
SALES_PRE05 SALES_PRE05_SP2
SALES_PRE05 SALES_PRE05_SP1
SALES_2005 SALES_2005_SP4
SALES_2005 SALES_2005_SP3
SALES_2005 SALES_2005_SP2
SALES_2005 SALES_2005_SP1
SALES_2006 SALES_2006_SP4
SALES_2006 SALES_2006_SP3
SALES_2006 SALES_2006_SP2
SALES_2006 SALES_2006_SP1
SALES_2007 SALES_2007_SP4
SALES_2007 SALES_2007_SP3
SALES_2007 SALES_2007_SP2
SALES_2007 SALES_2007_SP1
SALES_2008 SALES_2008_SP4
SALES_2008 SALES_2008_SP3
SALES_2008 SALES_2008_SP2
SALES_2008 SALES_2008_SP1
SALES_FUTURE SALES_FUTURE_SP4
SALES_FUTURE SALES_FUTURE_SP3
SALES_FUTURE SALES_FUTURE_SP2
SALES_FUTURE SALES_FUTURE_SP1
以上证明了默认dbms_stats的GRANULARITY AUTO在常规情况下一般是收集分区统计信息， 而不收集子分区的统计信息。
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1278164
