Have discussed about incremental partition enough in the past, this post is for incremental statistics enhancements in Oracle 12c database.
By default increment maintenance does not use the staleness status to decide when to update statistics, if a partition or sub-partition is subjected to even a single DML operation, statistics will be re-gathered, the appropriate synopsis will be updated and the global-level statistics will be re-calculated from the synopsis. This behavior can be changed in Oracle database 12c, allowing us to use staleness threshold to define when incremental statistics will be re-calculated.
Prior to 12c database, when we update one row in a partition containing one million rows, the partition won’t be marked STALE (if we assume a 10% staleness threshold) but fresh statistics will be re-gathered. Oracle 12c exhibits the same behavior by default, but gives us an option to allow multiple dml changes to occur against (sub) partition before it is a target for incremental refresh. You can enable this behavior by changing the DBMS_STATS preference INCREMENTAL_STALENESS from its default value (NULL) to USE_STALE_PERCENT
Once this preference is set at the table, statistics will not be re-gathered for a (sub) partition if the number of DML changes is below the STALE_PERCENT threshold.
Here is the test case from Oracle 11g (11.2.0.4) database.
demo@ORA11G> create table t
2 partition by range(created_dt)
3 interval( numtoyminterval(1,'year') )
4 ( partition p2010 values less than
5 (to_date('01-jan-2010','dd-mon-yyyy')) )
6 as
7 select owner,object_id,object_name,object_type,
8 to_date('01-Jan-2012','dd-mon-yyyy')+rownum created_dt
9 from all_objects
10 where rownum <= (365*3);
Table created.
demo@ORA11G> begin
2 dbms_stats.set_table_prefs(user,'T','INCREMENTAL','TRUE');
3 dbms_stats.set_table_prefs(user,'T','GRANULARITY','AUTO');
4 end;
5 /
PL/SQL procedure successfully completed.
demo@ORA11G> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
demo@ORA11G> select t1.table_name,t1.partition_name,t1.object_type,
2 t1.last_analyzed,t2.high_value
3 from user_tab_statistics t1 ,
4 user_tab_partitions t2
5 where t1.table_name ='T'
6 and t1.table_name = t2.table_name (+)
7 and t1.partition_name = t2.partition_name (+)
8 order by 1,2 nulls first;
TABLE_NAME PARTITION_ OBJECT_TYPE LAST_ANALYZED HIGH_VALUE
---------- ---------- ------------ -------------------- --------------------
T TABLE 02-JAN-2017 04:45:00
T P2010 PARTITION 02-JAN-2017 04:44:57 TO_DATE(' 2010-01-01
T SYS_P62406 PARTITION 02-JAN-2017 04:44:58 TO_DATE(' 2013-01-01
T SYS_P62407 PARTITION 02-JAN-2017 04:44:58 TO_DATE(' 2014-01-01
T SYS_P62408 PARTITION 02-JAN-2017 04:44:58 TO_DATE(' 2015-01-01
5 rows selected.
Incremental statistics been enabled on table ‘T’, synopsis got created at each partition and global statistics generated by aggregating partition level statistics and synopsis.
Let’s now insert a single row for partition that belong to 2013 and a whole bunch of records in 2017 and re-gather the stats to see what partition level statistics got refreshed.
demo@ORA11G> insert into t(owner,object_id,object_name,object_type,created_dt)
2 select owner,object_id,object_name,object_type,
3 to_date('13-feb-2013','dd-mon-yyyy') created_dt
4 from all_objects
5 where rownum = 1;
1 row created.
demo@ORA11G> insert into t(owner,object_id,object_name,object_type,created_dt)
2 select owner,object_id,object_name,object_type,
3 to_date('01-Jan-2017','dd-mon-yyyy')+rownum created_dt
4 from all_objects
5 where rownum < 365;
364 rows created.
demo@ORA11G> commit;
Commit complete.
demo@ORA11G> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
demo@ORA11G> select t1.table_name,t1.partition_name,t1.object_type,
2 t1.last_analyzed,t2.high_value
3 from user_tab_statistics t1 ,
4 user_tab_partitions t2
5 where t1.table_name ='T'
6 and t1.table_name = t2.table_name (+)
7 and t1.partition_name = t2.partition_name (+)
8 order by 1,2 nulls first;
TABLE_NAME PARTITION_ OBJECT_TYPE LAST_ANALYZED HIGH_VALUE
---------- ---------- ------------ -------------------- --------------------
T TABLE 02-JAN-2017 04:46:55
T P2010 PARTITION 02-JAN-2017 04:44:57 TO_DATE(' 2010-01-01
T SYS_P62406 PARTITION 02-JAN-2017 04:44:58 TO_DATE(' 2013-01-01
T SYS_P62407 PARTITION 02-JAN-2017 04:46:53 TO_DATE(' 2014-01-01
T SYS_P62408 PARTITION 02-JAN-2017 04:44:58 TO_DATE(' 2015-01-01
T SYS_P62442 PARTITION 02-JAN-2017 04:46:50 TO_DATE(' 2018-01-01
6 rows selected.
So inserting a single row into this partition SYS_P62407 has made the incremental stats gathering process to refresh the statistics on that partition.
In 11g any DML on older partitions triggered partition statistics to be re-gathered, this is the default in 12c, however you could change this behavior by changing the DBMS_STATS preference INCREMENTAL_STALENESS from its default value (NULL) to USE_STALE_PERCENT (that allows partition level statistics to be re-gathered once it exceeds the threshold value)
demo@ORA12C> begin
2 dbms_stats.set_table_prefs(user,'T','INCREMENTAL','TRUE');
3 dbms_stats.set_table_prefs(user,'T','GRANULARITY','AUTO');
4 end;
5 /
PL/SQL procedure successfully completed.
demo@ORA12C> select dbms_stats.get_prefs('INCREMENTAL_STALENESS',user,'T')
2 from dual;
DBMS_STATS.GET_PREFS('INCREMENTAL_STALENESS',USER,'T')
------------------------------------------------------
1 row selected.
demo@ORA12C> begin
2 dbms_stats.set_table_prefs(
3 ownname=>user,
4 tabname=>'T',
5 pname=>'INCREMENTAL_STALENESS',
6 pvalue=>'USE_STALE_PERCENT' );
7 end;
8 /
PL/SQL procedure successfully completed.
demo@ORA12C> select dbms_stats.get_prefs('INCREMENTAL_STALENESS',user,'T')
2 from dual;
DBMS_STATS.GET_PREFS('INCREMENTAL_STALENESS',USER,'T')
-------------------------------------------------------
USE_STALE_PERCENT
1 row selected.
demo@ORA12C> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
demo@ORA12C> select t1.table_name,t1.partition_name,t1.object_type,
2 t1.last_analyzed,t2.high_value
3 from user_tab_statistics t1 ,
4 user_tab_partitions t2
5 where t1.table_name ='T'
6 and t1.table_name = t2.table_name (+)
7 and t1.partition_name = t2.partition_name (+)
8 order by 1,2 nulls first;
TABLE_NAME PARTITION_ OBJECT_TYPE LAST_ANALYZED HIGH_VALUE
---------- ---------- ------------ -------------------- --------------------
T TABLE 02-JAN-2017 16:31:18
T P2010 PARTITION 02-JAN-2017 16:31:18 TO_DATE(' 2010-01-01
T SYS_P22309 PARTITION 02-JAN-2017 16:31:17 TO_DATE(' 2013-01-01
T SYS_P22310 PARTITION 02-JAN-2017 16:31:17 TO_DATE(' 2014-01-01
T SYS_P22311 PARTITION 02-JAN-2017 16:31:17 TO_DATE(' 2015-01-01
5 rows selected.
Let’s now insert a single row for partition that belong to 2013 and a whole bunch of records in 2017 and re-gather the stats to see what partition level statistics got refreshed in 12c database.
demo@ORA12C> insert into t(owner,object_id,object_name,object_type,created_dt)
2 select owner,object_id,object_name,object_type,
3 to_date('13-feb-2013','dd-mon-yyyy') created_dt
4 from all_objects
5 where rownum = 1;
1 row created.
demo@ORA12C> insert into t(owner,object_id,object_name,object_type,created_dt)
2 select owner,object_id,object_name,object_type,
3 to_date('01-Jan-2017','dd-mon-yyyy')+rownum created_dt
4 from all_objects
5 where rownum < 365;
364 rows created.
demo@ORA12C> commit;
Commit complete.
demo@ORA12C> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
demo@ORA12C> select t1.table_name,t1.partition_name,t1.object_type,
2 t1.last_analyzed,t2.high_value
3 from user_tab_statistics t1 ,
4 user_tab_partitions t2
5 where t1.table_name ='T'
6 and t1.table_name = t2.table_name (+)
7 and t1.partition_name = t2.partition_name (+)
8 order by 1,2 nulls first;
TABLE_NAME PARTITION_ OBJECT_TYPE LAST_ANALYZED HIGH_VALUE
---------- ---------- ------------ -------------------- --------------------
T TABLE 02-JAN-2017 16:31:39
T P2010 PARTITION 02-JAN-2017 16:31:18 TO_DATE(' 2010-01-01
T SYS_P22309 PARTITION 02-JAN-2017 16:31:17 TO_DATE(' 2013-01-01
T SYS_P22310 PARTITION 02-JAN-2017 16:31:17 TO_DATE(' 2014-01-01
T SYS_P22311 PARTITION 02-JAN-2017 16:31:17 TO_DATE(' 2015-01-01
T SYS_P22361 PARTITION 02-JAN-2017 16:31:39 TO_DATE(' 2018-01-01
6 rows selected.
demo@ORA12C> select dbms_stats.get_prefs('STALE_PERCENT',user,'T')
2 from dual;
DBMS_STATS.GET_PREFS('STALE_PERCENT',USER,'T')
--------------------------------------------------------------------
10
1 row selected.
demo@ORA12C>
In Oracle 12c even after a single row change in this partition SYS_P22310 which is still below the threshold, so no stats refresh to that partition, wherever the changes is above the threshold limit, only those (sub) partitions will be considered for stats refresh process.
By default increment maintenance does not use the staleness status to decide when to update statistics, if a partition or sub-partition is subjected to even a single DML operation, statistics will be re-gathered, the appropriate synopsis will be updated and the global-level statistics will be re-calculated from the synopsis. This behavior can be changed in Oracle database 12c, allowing us to use staleness threshold to define when incremental statistics will be re-calculated.
Prior to 12c database, when we update one row in a partition containing one million rows, the partition won’t be marked STALE (if we assume a 10% staleness threshold) but fresh statistics will be re-gathered. Oracle 12c exhibits the same behavior by default, but gives us an option to allow multiple dml changes to occur against (sub) partition before it is a target for incremental refresh. You can enable this behavior by changing the DBMS_STATS preference INCREMENTAL_STALENESS from its default value (NULL) to USE_STALE_PERCENT
Once this preference is set at the table, statistics will not be re-gathered for a (sub) partition if the number of DML changes is below the STALE_PERCENT threshold.
Here is the test case from Oracle 11g (11.2.0.4) database.
demo@ORA11G> create table t
2 partition by range(created_dt)
3 interval( numtoyminterval(1,'year') )
4 ( partition p2010 values less than
5 (to_date('01-jan-2010','dd-mon-yyyy')) )
6 as
7 select owner,object_id,object_name,object_type,
8 to_date('01-Jan-2012','dd-mon-yyyy')+rownum created_dt
9 from all_objects
10 where rownum <= (365*3);
Table created.
demo@ORA11G> begin
2 dbms_stats.set_table_prefs(user,'T','INCREMENTAL','TRUE');
3 dbms_stats.set_table_prefs(user,'T','GRANULARITY','AUTO');
4 end;
5 /
PL/SQL procedure successfully completed.
demo@ORA11G> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
demo@ORA11G> select t1.table_name,t1.partition_name,t1.object_type,
2 t1.last_analyzed,t2.high_value
3 from user_tab_statistics t1 ,
4 user_tab_partitions t2
5 where t1.table_name ='T'
6 and t1.table_name = t2.table_name (+)
7 and t1.partition_name = t2.partition_name (+)
8 order by 1,2 nulls first;
TABLE_NAME PARTITION_ OBJECT_TYPE LAST_ANALYZED HIGH_VALUE
---------- ---------- ------------ -------------------- --------------------
T TABLE 02-JAN-2017 04:45:00
T P2010 PARTITION 02-JAN-2017 04:44:57 TO_DATE(' 2010-01-01
T SYS_P62406 PARTITION 02-JAN-2017 04:44:58 TO_DATE(' 2013-01-01
T SYS_P62407 PARTITION 02-JAN-2017 04:44:58 TO_DATE(' 2014-01-01
T SYS_P62408 PARTITION 02-JAN-2017 04:44:58 TO_DATE(' 2015-01-01
5 rows selected.
Incremental statistics been enabled on table ‘T’, synopsis got created at each partition and global statistics generated by aggregating partition level statistics and synopsis.
Let’s now insert a single row for partition that belong to 2013 and a whole bunch of records in 2017 and re-gather the stats to see what partition level statistics got refreshed.
demo@ORA11G> insert into t(owner,object_id,object_name,object_type,created_dt)
2 select owner,object_id,object_name,object_type,
3 to_date('13-feb-2013','dd-mon-yyyy') created_dt
4 from all_objects
5 where rownum = 1;
1 row created.
demo@ORA11G> insert into t(owner,object_id,object_name,object_type,created_dt)
2 select owner,object_id,object_name,object_type,
3 to_date('01-Jan-2017','dd-mon-yyyy')+rownum created_dt
4 from all_objects
5 where rownum < 365;
364 rows created.
demo@ORA11G> commit;
Commit complete.
demo@ORA11G> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
demo@ORA11G> select t1.table_name,t1.partition_name,t1.object_type,
2 t1.last_analyzed,t2.high_value
3 from user_tab_statistics t1 ,
4 user_tab_partitions t2
5 where t1.table_name ='T'
6 and t1.table_name = t2.table_name (+)
7 and t1.partition_name = t2.partition_name (+)
8 order by 1,2 nulls first;
TABLE_NAME PARTITION_ OBJECT_TYPE LAST_ANALYZED HIGH_VALUE
---------- ---------- ------------ -------------------- --------------------
T TABLE 02-JAN-2017 04:46:55
T P2010 PARTITION 02-JAN-2017 04:44:57 TO_DATE(' 2010-01-01
T SYS_P62406 PARTITION 02-JAN-2017 04:44:58 TO_DATE(' 2013-01-01
T SYS_P62407 PARTITION 02-JAN-2017 04:46:53 TO_DATE(' 2014-01-01
T SYS_P62408 PARTITION 02-JAN-2017 04:44:58 TO_DATE(' 2015-01-01
T SYS_P62442 PARTITION 02-JAN-2017 04:46:50 TO_DATE(' 2018-01-01
6 rows selected.
So inserting a single row into this partition SYS_P62407 has made the incremental stats gathering process to refresh the statistics on that partition.
In 11g any DML on older partitions triggered partition statistics to be re-gathered, this is the default in 12c, however you could change this behavior by changing the DBMS_STATS preference INCREMENTAL_STALENESS from its default value (NULL) to USE_STALE_PERCENT (that allows partition level statistics to be re-gathered once it exceeds the threshold value)
demo@ORA12C> begin
2 dbms_stats.set_table_prefs(user,'T','INCREMENTAL','TRUE');
3 dbms_stats.set_table_prefs(user,'T','GRANULARITY','AUTO');
4 end;
5 /
PL/SQL procedure successfully completed.
demo@ORA12C> select dbms_stats.get_prefs('INCREMENTAL_STALENESS',user,'T')
2 from dual;
DBMS_STATS.GET_PREFS('INCREMENTAL_STALENESS',USER,'T')
------------------------------------------------------
1 row selected.
demo@ORA12C> begin
2 dbms_stats.set_table_prefs(
3 ownname=>user,
4 tabname=>'T',
5 pname=>'INCREMENTAL_STALENESS',
6 pvalue=>'USE_STALE_PERCENT' );
7 end;
8 /
PL/SQL procedure successfully completed.
demo@ORA12C> select dbms_stats.get_prefs('INCREMENTAL_STALENESS',user,'T')
2 from dual;
DBMS_STATS.GET_PREFS('INCREMENTAL_STALENESS',USER,'T')
-------------------------------------------------------
USE_STALE_PERCENT
1 row selected.
demo@ORA12C> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
demo@ORA12C> select t1.table_name,t1.partition_name,t1.object_type,
2 t1.last_analyzed,t2.high_value
3 from user_tab_statistics t1 ,
4 user_tab_partitions t2
5 where t1.table_name ='T'
6 and t1.table_name = t2.table_name (+)
7 and t1.partition_name = t2.partition_name (+)
8 order by 1,2 nulls first;
TABLE_NAME PARTITION_ OBJECT_TYPE LAST_ANALYZED HIGH_VALUE
---------- ---------- ------------ -------------------- --------------------
T TABLE 02-JAN-2017 16:31:18
T P2010 PARTITION 02-JAN-2017 16:31:18 TO_DATE(' 2010-01-01
T SYS_P22309 PARTITION 02-JAN-2017 16:31:17 TO_DATE(' 2013-01-01
T SYS_P22310 PARTITION 02-JAN-2017 16:31:17 TO_DATE(' 2014-01-01
T SYS_P22311 PARTITION 02-JAN-2017 16:31:17 TO_DATE(' 2015-01-01
5 rows selected.
Let’s now insert a single row for partition that belong to 2013 and a whole bunch of records in 2017 and re-gather the stats to see what partition level statistics got refreshed in 12c database.
demo@ORA12C> insert into t(owner,object_id,object_name,object_type,created_dt)
2 select owner,object_id,object_name,object_type,
3 to_date('13-feb-2013','dd-mon-yyyy') created_dt
4 from all_objects
5 where rownum = 1;
1 row created.
demo@ORA12C> insert into t(owner,object_id,object_name,object_type,created_dt)
2 select owner,object_id,object_name,object_type,
3 to_date('01-Jan-2017','dd-mon-yyyy')+rownum created_dt
4 from all_objects
5 where rownum < 365;
364 rows created.
demo@ORA12C> commit;
Commit complete.
demo@ORA12C> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
demo@ORA12C> select t1.table_name,t1.partition_name,t1.object_type,
2 t1.last_analyzed,t2.high_value
3 from user_tab_statistics t1 ,
4 user_tab_partitions t2
5 where t1.table_name ='T'
6 and t1.table_name = t2.table_name (+)
7 and t1.partition_name = t2.partition_name (+)
8 order by 1,2 nulls first;
TABLE_NAME PARTITION_ OBJECT_TYPE LAST_ANALYZED HIGH_VALUE
---------- ---------- ------------ -------------------- --------------------
T TABLE 02-JAN-2017 16:31:39
T P2010 PARTITION 02-JAN-2017 16:31:18 TO_DATE(' 2010-01-01
T SYS_P22309 PARTITION 02-JAN-2017 16:31:17 TO_DATE(' 2013-01-01
T SYS_P22310 PARTITION 02-JAN-2017 16:31:17 TO_DATE(' 2014-01-01
T SYS_P22311 PARTITION 02-JAN-2017 16:31:17 TO_DATE(' 2015-01-01
T SYS_P22361 PARTITION 02-JAN-2017 16:31:39 TO_DATE(' 2018-01-01
6 rows selected.
demo@ORA12C> select dbms_stats.get_prefs('STALE_PERCENT',user,'T')
2 from dual;
DBMS_STATS.GET_PREFS('STALE_PERCENT',USER,'T')
--------------------------------------------------------------------
10
1 row selected.
demo@ORA12C>
In Oracle 12c even after a single row change in this partition SYS_P22310 which is still below the threshold, so no stats refresh to that partition, wherever the changes is above the threshold limit, only those (sub) partitions will be considered for stats refresh process.
No comments:
Post a Comment