I am not so familiar with the DBMS_STATS procedure, so I always use the "ANALYZE" command. One day I got the following problem. Let's start a demo, first step is creating demo partitioned table.
SQL> CREATE TABLE T_PARTDEMO(
2 COL1 NUMBER(10),
3 COL2 VARCHAR2(30)
4 ) PARTITION BY HASH(COL1) PARTITIONS 4;
Table created.
Then I gather the table statistics with DBMS_STATS package. And check the global level table statistics.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_PARTDEMO', CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT TABLE_NAME, BLOCKS
2 FROM USER_TABLES WHERE TABLE_NAME='T_PARTDEMO';
TABLE_NAME BLOCKS
------------------------------ ----------
T_PARTDEMO 0
Then I insert few rows and run "ANALYZE" command, and check the statistics data again, I found that the partition level statistics data was changed, but the global level was not changed.
SQL> SELECT TABLE_NAME, BLOCKS
2 FROM USER_TABLES WHERE TABLE_NAME='T_PARTDEMO';
TABLE_NAME BLOCKS
------------------------------ ----------
T_PARTDEMO 0
To get analyze command work, I have to delete the statistics first with DBMS_STATS package. Then the analyze command do update the global level statistics.
SQL> SELECT TABLE_NAME, BLOCKS
2 FROM USER_TABLES WHERE TABLE_NAME='T_PARTDEMO';
TABLE_NAME BLOCKS
------------------------------ ----------
T_PARTDEMO 20
Yesterday I was doing performance tuning, and this blocked me half an hour.