Incremental Statistics Collection in Oracle – 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

This is the follow-up blog post to:

Incremental Statistics Collection in Oracle – Upgrade Pitfalls

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

Incremental Statistics Collection improved in Oracle 12c

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

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
  • 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 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  (see also MOS Note:2041541.1 – Gather_Database_Stats_Job_Proc Taking More Time in Than

and the customer requested:
Patch 22893653: MERGE REQUEST ON TOP OF DATABASE PSU 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:


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

20807398: ORA-00600 [KGL-HASH-COLLISION] WITH FIX TO BUG 20465582

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 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.


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!!!

4 thoughts on “Incremental Statistics Collection in Oracle – A True Story

  1. Pingback: Oracle Optimizer Statistics Advisor in Oracle Database

  2. Pingback: Incremental Statistics – A Real World Scenario | Data Warehousing with Oracle

  3. 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 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 or 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.


Leave a Reply

Your email address will not be published. Required fields are marked *