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’);
- SQL> exec DBMS_STATS.SET_DATABASE_PREFS(‘INCREMENTAL_STALENESS’,
- Change (only if desired) the stale percentage of default of 10%:
- SQL> exec DBMS_STATS.SET_DATABASE_PREFS(‘STALE_PERCENT’,’12’);
1 Response
[…] Incremental Statistics Collection improved in Oracle 12c […]