Table of contents
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:
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)
Sachin Kumar VERMA says:
Dear Yannick,
Can you please post with steps of doing de-fragmentation (SHRINKING) of partitioned tables.
Regards,
Sachin
Yannick Jaquier says:
Dear Sachin,
I’m sure Google can be your firend on this:
Managing Fragmentation and Table Shrink in Oracle Database
Thanks,
Yannick.