Incremental Statistics Collection improved in Oracle 12c

Traveling right now through Asia. It was Beijing for 32 hours, Toyko for 24 hours – and now we are running an internal 2-day workshop with colleagues from Korea, New Zealand, India and some other countries in Seoul. And yesterday I had the pleasure to listen to Tom Kyte to his optimizer talk at the OTN Conference in Tokyo. And I learned a lot – as always when having the chance to listen to Tom, Graham Wood and the other great experts.

Oracle Database 11.1 offered a great new feature: Incremental Statistics Collection which helped a lot to make stats collection on partitioned tables way more efficient. But it had a few flaws and it took a while to work as expected. And it had one side effect when you used it heavily: It stored tons of data in WRI$_OPSTAT_SYNOPSIS. We saw it on some databases at almost 300GB. 

Now the thing with such a huge WRI$_OPSTAT_SYNOPSIS can be: It gets a new partitioning layout during upgrades twice:

  • Upgrade from Oracle 11.1.0.x/11.2.0.1 to Oracle 11.2.0.2/3/4:
    • Change to Range.Hash Partitioning for WRI$_OPSTAT_SYNOPSIS
    • This can cause a lot of data movement.
  • Upgrade from Oracle 11.2.0.2/3/4 to Oracle 12.1.0.x:
    • Change to List-Hash Partitioning
    • This will cause not as much data movement as in the previous change

Tom explained yesterday that in Oracle Database 12c Incremental Statistics Collection has gotten a few excellent extensions making it more efficient:

  • Smaller footprint on disk for synopses compared to previous releases
  • Incremental stats with partition exchange operations
  • Ability to define a stale percentage for existing partitions

The latter one is very interesting as it meant: Before Oracle Database 12c whenever you did change a single row within an existing partition during a recalculation of the Global Stats this particular partition need to be examined again – even though just one record has been changed – instead of still using the stored synopsis.

In Oracle Database 12c you can now define a stale percentage. First you’ll have to enable it, second you can set a stale percentage by yourself – otherwise the default of 10% would apply – but only if it has been enabled. Otherwise the pre-12c default will be kept (and this is the behavior in Oracle Database 12c out of the box):

  • Switch incremental statistics on for a specific partitioned table:
    • SQL> exec DBMS_STATS.SET_TABLE_PREFS(‘SH’,’SALES’,’INCREMENTAL’,’TRUE’); 
  • Switch on the new 12c stale percentage feature globally:
    • SQL> exec DBMS_STATS.SET_DATABASE_PREFS(‘INCREMENTAL_STALENESS’,
      ‘USE_STALE_PERCENT’);
  • Change (only if desired) the stale percentage of default of 10%:
    • SQL> exec DBMS_STATS.SET_DATABASE_PREFS(‘STALE_PERCENT’,’12’);
-Mike

One thought on “Incremental Statistics Collection improved in Oracle 12c

  1. Pingback: Incremental Statistics Collection in Oracle 12.1.0.2 – Upgrade Pitfalls | Upgrade your Database - NOW!

Leave a Reply

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