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):
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:
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!!!
Hello, Mike,
You wrote “Not enable incremental stats collection globally but only for specific tables. Otherwise the footprint for the synopsis on disk can grow fairly large.”
Can you explain more details why you not advise enable incremental stats globally ?
What will happen bad ? Will Oracle collect extra stats for not-partitioned tables ?
As I understand there is no difference if we set incremental stats for all partitioned tables or set incremental stats globally, because stats is binded to partitioned tables only.
Thank you !
Yury,
there’s actually a difference between Oracle 12.2 and newer, and before Oracle 12.2.
With 12.2 with have new algorithms (hyperloglog) leading to much smaller synopsis – at this point I don’t see a risk in enabling it globally.
But with releases before 12.2 the old algorithm created HUGE synopsis – I had to deal with customer systems having almost 1TB synopsis in SYSAUX. Even worse, during the upgrade cycles from 11.2.0.1 onwards, the partitioning design for the underlying dictionary tables got changed twice. In such cases you needed another 1TB of temp storage for the redesign, and it caused the upgrade to take VERY long. When coming from 11.1.0.7 or 11.2.0.1 to a higher release, two tables are affected. Wri$_optstat_synopsis$ and wri$_optstat_synopsis_head$. We are renaming the existing wri$_optstat_synopsis$, create a new wri$_optstat_synopsis$ and repopulate the new table from the old table using insert. So in the interim period when the two table coexist, we can use 2X of the space of the wri$_optstat_synopsis$ tables.
In addition, according to the developer who implemented most of the inc stats feature, it works most efficient with range-partitioned
tables where only a few partitions undergo DML changes. Incremental stats is not meant for VERY partitioned table.
That is why it is disabled by default.
Cheers,
Mike