Incremental statistics on partitioned tables

Preamble

As we have seen in a previous post aggregated global statistics is not the best statistics gathering strategy. Gathering statistics at global level remains a must while at same time can negatively impact the time it takes to gather them. Of course to be negatively impacted you need to have objects with millions of rows, your partitioned table with few thousands rows per partition will most probably not see the difference.

To improve the situation, starting with 11gR2, Oracle has introduced incremental statistics. Even if the implementation is not so obvious the idea behind is simple. When you gather statistics on a partition Oracle is creating, at same time, what they call a synopsis. These synopsis will be used to generate the global statistics without the need to re-gather all partition each time. Number of distinct values (NDV) and density will be well updated (not like aggregated global statistics).

In other words it will be as fast as aggregated global statistics (with the very small overhead of aggregating the synopsis) with the precision of gathered global statistics.

Testing has been done on Oracle enterprise edition 11.2.0.4 running on HPUX 11.31 (4 sockets 8 Itanium cores at 1.6 GHz).

Incremental statistics

To test this I have used the same test table as previous article on global statistics. But this time I have extended it to have 75 numeric/character columns (coming from one of our big BI table) and created it with only one partition:

CREATE TABLE test01(
partition_code NUMBER,
.
.
.
)
NOLOGGING
TABLESPACE users
PARTITION BY RANGE (partition_code)
(
PARTITION p00 VALUES LESS THAN (1)
);

The source table I’m using has 1,414,513 rows and sizes 473 MB.

Then I add partitions one by one and compute statistics on this newly added partition with something like:

ALTER TABLE test01 ADD PARTITION p01 VALUES LESS THAN (2);
EXEC dbms_stats.gather_table_stats(ownname=>USER, tabname=>'test01', partname=>'p00', degree=>16);

So overall I have reproduced the process two times: one without incremental features and another one with incremental features.

By default the incremental feature is not activated, change the properties of your object with:

SQL> SELECT DBMS_STATS.GET_PREFS('incremental',USER,'test01') FROM dual;
 
DBMS_STATS.GET_PREFS('INCREMENTAL',USER,'TEST01')
------------------------------------------------------------------------------------------------------------------------------------------------------
FALSE
 
SQL> EXEC DBMS_STATS.set_table_prefs(USER,'test01','incremental','true');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT DBMS_STATS.GET_PREFS('incremental',USER,'test01') FROM dual;
 
DBMS_STATS.GET_PREFS('INCREMENTAL',USER,'TEST01')
------------------------------------------------------------------------------------------------------------------------------------------------------
TRUE

I have inherited below query from My Oracle Support and it allows you to display the synopsis of an objects. The synopsis are stored is SYSAUX tablespace:

SQL> SET lines 150 pages 1000
SQL> col OBJECT_NAME FOR a15
SQL> WITH tabpart AS
      (SELECT obj#,dataobj#, synop.bo#
       FROM sys.tabpart$ tap, (SELECT DISTINCT(bo#) FROM sys.wri$_optstat_synopsis$) synop
       WHERE tap.bo#(+) = synop.bo#)
     SELECT owner, NVL(object_name,'**ORPHANED') AS object_name, NVL(subobject_name,'**ORPHANED') AS subobject_name, object_type, bo#
     FROM dba_objects DO, tabpart tp
     WHERE DO.object_id(+) = tp.obj#
     AND DO.data_object_id(+) = dataobj#
     AND owner=USER
     AND object_name='TEST01';
 
OWNER                          OBJECT_NAME     SUBOBJECT_NAME                 OBJECT_TYPE                BO#
------------------------------ --------------- ------------------------------ ------------------- ----------
YJAQUIER                       TEST01          P00                            TABLE PARTITION       21230467
YJAQUIER                       TEST01          P01                            TABLE PARTITION       21230467
YJAQUIER                       TEST01          P02                            TABLE PARTITION       21230467
YJAQUIER                       TEST01          P03                            TABLE PARTITION       21230467
YJAQUIER                       TEST01          P04                            TABLE PARTITION       21230467
YJAQUIER                       TEST01          P05                            TABLE PARTITION       21230467
YJAQUIER                       TEST01          P06                            TABLE PARTITION       21230467
YJAQUIER                       TEST01          P07                            TABLE PARTITION       21230467
YJAQUIER                       TEST01          P08                            TABLE PARTITION       21230467
YJAQUIER                       TEST01          P09                            TABLE PARTITION       21230467
YJAQUIER                       TEST01          P10                            TABLE PARTITION       21230467
YJAQUIER                       TEST01          P11                            TABLE PARTITION       21230467
YJAQUIER                       TEST01          P12                            TABLE PARTITION       21230467
YJAQUIER                       TEST01          P13                            TABLE PARTITION       21230467
YJAQUIER                       TEST01          P14                            TABLE PARTITION       21230467
YJAQUIER                       TEST01          P15                            TABLE PARTITION       21230467
YJAQUIER                       TEST01          P16                            TABLE PARTITION       21230467
YJAQUIER                       TEST01          P17                            TABLE PARTITION       21230467
YJAQUIER                       TEST01          P18                            TABLE PARTITION       21230467
YJAQUIER                       TEST01          P19                            TABLE PARTITION       21230467
 
20 ROWS selected.

Results

I have added up to 20 partitions on my test table. A small graph speaks better than a table:

incremental_statistics01
incremental_statistics01

If interested by raw figures the incremental statistics Excel..

We clearly see with incremental statistics that the time to gather statistics on a newly added partition is almost constant. In the case of incremental statistics Oracle only aggregates the synopsis to generate the global statistics. In standard (default case) Oracle would regather the whole table, so all partitions to generate the global statistics. That’s why the trend is almost linear without incremental statistics.

References

  • Collect Incremental Statistics For a Large Partitioned Table in 10g and in 11g (Doc ID 1319225.1)
  • How to Delete Unwanted Incremental Partition Statistics Synopsis Information From WRI$_OPTSTAT_SYNOPSIS$ in the SYSAUX Tablespace (Doc ID 1953961.1)

About Post Author

This entry was posted in Oracle and tagged , . Bookmark the permalink.

4 thoughts on “Incremental statistics on partitioned tables

  1. Sachin Kumar VERMA says:

    Dear Yannick,

    Can you please post with steps of doing de-fragmentation (SHRINKING) of partitioned tables.

    Regards,
    Sachin

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>