Incremental Statistics Collection in Oracle 12.1.0.2 – A True Story

Recently I came across a really interesting customer case in the UK dealing with Incremental Statistics Collection issues in regards to an upgrade to Oracle Database 12.1.0.2.

This is the follow-up blog post to:

A while back I blogged already about Incremental Statistics collection in Oracle Database 12.1.0.2:

And you’ll find more information in our documentation and in posts by our optimizer folks:

• Database SQL Tuning Guide 12c:
  Gathering Incremental Statistics on Partitioned Objects
• Incremental Statistics Maintenance – what statistics will be gathered after DML occurs on the table? – Mar 18, 2012
  https://blogs.oracle.com/optimizer/entry/incremental_statistics_maintenance_what_statistics 
  .

The Basics

The idea of Incremental Statistics Collection is simply to save time and resources when gathering statistics for partitioned tables in order to update the global stats. In Oracle Database 12c we added the very important features of:

• Incremental stats working now with partition exchange as well
• “Changed” partitions won’t be eligible for new stats generation until a certain stale percentage (default: 10%) has been reached – this has to be enabled and can be tweaked

  SQL> exec DBMS_STATS.SET_DATABASE_PREFS('INCREMENTAL_STALENESS','USE_STALE_PERCENT');
  SQL> exec DBMS_STATS.SET_DATABASE_PREFS('STALE_PERCENT','12');

• Not enable incremental stats collection globally but only for specific tables. Otherwise the footprint for the synopsis on disk can grow fairly large. Biggest footprint I’ve seen so far was almost 1TB in size in the SYSAUX tablespace
• Enable it mostly for range-partitioned tables where only a few partitions undergo DML changes

The Case

Actually the synopsis table in this particular case did contain “only” 300GB of data. But as the starting point was already Oracle Database 11.2.0.3 just a change from Range-Hash to List-Hash Partitioning would happen. As this happens via metadata swapping the impact should be small.

But the issue coming up after the upgrade didn’t have to do with this change in partitioning layout.

Issue No.1

During the maintenance window the incremental stats job did not finish and actually this statement caused plenty of trouble:

delete from sys.wri$_optstat_synopsis$ where bo# = :tobjn and group# in (select * from table(:groups)

Not completing this statement within the 4 hours of the default maintenance window led to a rollback of the delete – and its rollback alone took 14 hours. It turned out that the delete has to happen (and complete) before the regathering of stats could start.

I did recommend:
patch 21498770: AUTOMATIC INCREMENTAL STATISTICS JOB TAKING MORE TIME ON 12.1.0.2  (see also MOS Note:2041541.1 – Gather_Database_Stats_Job_Proc Taking More Time in 12.1.0.2 Than 11.2.0.4)

and the customer requested:
Patch 22893653: MERGE REQUEST ON TOP OF DATABASE PSU 12.1.0.2.160119 FOR BUGS 19450139 20807398
on top of their January 2016 PSU – the merge included the patch I mentioned.

Besides that another issue got discovered.

Issue No.2

The daily purge of statistics didn’t really work on large synopsis as the default degree of parallelism introduced with Oracle 12c gets derived from the number of blocks on the synopsis table – bigger table means a higher parallel degree for the purge. It ended up with a PARALLEL hint of 60 – and that was counterproductive. Once a purge got started manually in serial mode or with a low DOP it completed in less than 1 minute.

With a similar trace as:

set serveroutput on;
EXEC dbms_output.enable(999999999);
EXEC dbms_stats.set_global_prefs('trace',1+4);
EXEC dbms_stats.gather_table_stats(ownname=>'&TABLE_OWNER',tabname=>'&TABLE_NAME');
EXEC dbms_stats.set_global_prefs('trace',0);

the issues could be identified as:

Bug 21258096 – UNNECESSARY INCREMENTAL PARTITION GATHERS/HISTOGRAM REGATHERS

The customer requested another merge patch 22926433 which contains the following fixes:

19450139: KN:LNX:PERFORMANCE ISSUE WHEN RUNNING GATHER TABLE STATS WITH INCREMENTAL STATS
20807398: ORA-00600 [KGL-HASH-COLLISION] WITH FIX TO BUG 20465582
21258096: UNNECESSARY INCREMENTAL PARTITION GATHERS/HISTOGRAM REGATHERS
21498770: AUTOMATIC INCREMENTAL STATISTICS JOB TAKING MORE TIME ON 12.1.0.2

Finally the customer agreed with Support’s recommendation to truncate the two synopsis tables, WRI$_OPTSTAT_SYNOPSIS_HEAD$ andWRI$_OPTSTAT_SYNOPSIS$, and regathered incremental statistics the following weekend. Of course they validated this action plan on their performance testing environment first – with the merge patch applied – and it had the desired effect and solved the issue.

Incremental statistic gathering works now as expected, the job fits into the maintenance window.

Lessons Learned

Actually Oracle Support released a very helpful and important note just a few weeks ago (too late for this customer):

• MOS Note: 2107602.1
  Things to Consider When Using Incremental Statistics

It contains not only links to the patches for the issues the customer hit here – but also a long list for Oracle 11.2.0.4 as well.

Another MOS Note is worth to mention here:

• MOS Note:1953961.1 – How to Delete Unwanted Incremental Partition Statistics Synopsis Information From WRI$_OPTSTAT_SYNOPSIS$ in the SYSAUX Tablespace  

But these were not all issues the customer faced – so I may write up another blog post in addition within the next days.

–Mike

PS. All credits go to David Butler and Rob Dawley – thanks for your hard work, sorry for all the inconvenience – and especially thanks for writing it all together and forwarding it to me!!!