By Mike Dietrich-Oracle on Apr 28, 2016
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 126.96.36.199.
This is the follow-up blog post to:
- Incremental Statistics Collection in Oracle 188.8.131.52 - Upgrade Pitfalls (Apr 27. 2016)
A while back I blogged already about Incremental Statistics collection in Oracle Database 184.108.40.206:
- Incremental Statistics Collection Improved in Oracle Database 12c (Nov 13, 2014)
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
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');
Furthermore we always recommend to:
- 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
Actually the synopsis table in this particular case did contain "only" 300GB of data. But as the starting point was already Oracle Database 220.127.116.11 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.
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 18.104.22.168 (see also MOS Note:2041541.1 - Gather_Database_Stats_Job_Proc Taking More Time in 22.214.171.124 Than 126.96.36.199)
and the customer requested:
Patch 22893653: MERGE REQUEST ON TOP OF DATABASE PSU 188.8.131.52.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.
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;
the issues could be identified as:
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 184.108.40.206
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.
Actually Oracle Support released a very helpful and important note just a few weeks ago (too late for this customer):
It contains not only links to the patches for the issues the customer hit here - but also a long list for Oracle 220.127.116.11 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.
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!!!