Becareful when using DBMS_UTILITY to analyze

Hello,

If you are still using DBMS_UTILITY.ANALYZE_DATABASE or DBMS_UTILITY.ANALYZE_SCHEMA to analyze your database/schema's then you need to be very cautious.

I have observed a very strange behavior of this procedure against partitioned tables in one of our databases. Statistics are not being updated at table level. Although, partitions statistics are up to date.

The reason for incorrect statistics is:

If you use DBMS_STATS package to gather table statistics on a partitioned table and then later you use DBMS_UTILITY.ANALYZE_SCHEMA, table-level statistics are NOT updated, rather, statistics on partitions and indexes are modified.

This peculiar behavior is observed only with partitioned tables.

ANALYZE_SCHEMA procedure is obsolete and any one of us using this to gather statistics should seriously think of moving to DBMS_STATS package.

Following is a simple demo:

SQL> CREATE TABLE part_tab
  2    (id  NUMBER(5),
  3     dt    DATE)
  4     PARTITION BY RANGE(dt)
  5     (
  6     PARTITION part1_jan2008 VALUES LESS THAN(TO_DATE('01/02/2008','DD/MM/YYYY')),
  7     PARTITION part2_feb2008 VALUES LESS THAN(TO_DATE('01/03/2008','DD/MM/YYYY')),
  8     PARTITION part3_mar2008 VALUES LESS THAN(TO_DATE('01/04/2008','DD/MM/YYYY')),
  9     PARTITION part4_apr2008 VALUES LESS THAN(TO_DATE('01/05/2008','DD/MM/YYYY'))
 10    );

Table created.

SQL>
SQL>
SQL> create table non_part_tab (id number, dt date);

Table created.

SQL> select table_name, num_rows, last_analyzed from user_tables ;

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
NON_PART_TAB
PART_TAB

SQL> select partition_name, num_rows, last_analyzed from user_tab_partitions;

PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
PART2_FEB2008
PART1_JAN2008
PART3_MAR2008
PART4_APR2008

SQL> exec dbms_utility.analyze_schema('TEST', 'COMPUTE');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, last_analyzed from user_tables ;

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
NON_PART_TAB                            0 05-05-2008 00:05:43
PART_TAB                                0 05-05-2008 00:05:43

SQL> select partition_name, num_rows, last_analyzed from user_tab_partitions;

PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
PART2_FEB2008                           0 05-05-2008 00:05:43
PART1_JAN2008                           0 05-05-2008 00:05:43
PART3_MAR2008                           0 05-05-2008 00:05:43
PART4_APR2008                           0 05-05-2008 00:05:43

SQL> exec dbms_utility.analyze_schema('TEST', 'COMPUTE');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, last_analyzed from user_tables ;

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
NON_PART_TAB                            0 05-05-2008 00:05:59
PART_TAB                                0 05-05-2008 00:05:59

SQL> select partition_name, num_rows, last_analyzed from user_tab_partitions;

PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
PART2_FEB2008                           0 05-05-2008 00:05:59
PART1_JAN2008                           0 05-05-2008 00:05:59
PART3_MAR2008                           0 05-05-2008 00:05:59
PART4_APR2008                           0 05-05-2008 00:05:59

SQL> exec dbms_stats.gather_schema_stats('TEST');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, last_analyzed from user_tables ;

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
NON_PART_TAB                            0 05-05-2008 00:06:11
PART_TAB                                0 05-05-2008 00:06:11

SQL> select partition_name, num_rows, last_analyzed from user_tab_partitions;

PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
PART2_FEB2008                           0 05-05-2008 00:06:11
PART1_JAN2008                           0 05-05-2008 00:06:11
PART3_MAR2008                           0 05-05-2008 00:06:11
PART4_APR2008                           0 05-05-2008 00:06:11

SQL> exec dbms_utility.analyze_schema('TEST', 'COMPUTE');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, last_analyzed from user_tables ;

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
NON_PART_TAB                            0 05-05-2008 00:06:23
PART_TAB                                0 05-05-2008 00:06:11 ---> Statistics are NOT updated.

SQL> select partition_name, num_rows, last_analyzed from user_tab_partitions;

PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
PART2_FEB2008                           0 05-05-2008 00:06:23
PART1_JAN2008                           0 05-05-2008 00:06:23
PART3_MAR2008                           0 05-05-2008 00:06:23
PART4_APR2008                           0 05-05-2008 00:06:23

SQL> exec dbms_utility.analyze_schema('TEST', 'COMPUTE');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, last_analyzed from user_tables ;

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
NON_PART_TAB                            0 05-05-2008 00:08:53
PART_TAB                                0 05-05-2008 00:06:11 ---> Statistics are NOT updated.

SQL> select partition_name, num_rows, last_analyzed from user_tab_partitions;

PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
PART2_FEB2008                           0 05-05-2008 00:08:53
PART1_JAN2008                           0 05-05-2008 00:08:53
PART3_MAR2008                           0 05-05-2008 00:08:53
PART4_APR2008                           0 05-05-2008 00:08:53

SQL> exec dbms_stats.gather_schema_stats('TEST');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, last_analyzed from user_tables ;

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
NON_PART_TAB                            0 05-05-2008 00:09:05
PART_TAB                                0 05-05-2008 00:09:05

SQL> select partition_name, num_rows, last_analyzed from user_tab_partitions;

PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
PART2_FEB2008                           0 05-05-2008 00:09:05
PART1_JAN2008                           0 05-05-2008 00:09:05
PART3_MAR2008                           0 05-05-2008 00:09:05
PART4_APR2008                           0 05-05-2008 00:09:05

SQL>

Useful references:

1)How to Move from ANALYZE (using DBMS_UTILITY) to DBMS_STATS (Note: 237397.1)

2) Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2)

Happy reading !!!