Thursday Nov 13, 2014

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

Friday Feb 10, 2012

Note:1389592.1 Public - Speed Up xTTS

As we have already mentioned in our recent workshops the MOS Note:1389592.1 is public now:

  • MOS Note:1389592.1
    Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backups

This technique currently works for migrations to Linux64 and is supported by Oracle Support for Exadata migrations only. It utiliuzes RMAN incremental backups to reduce the large amount of downtime it takes to copy and convert the datafiles cross Endianness.

Two requirements must be met to use this new functionality:

  • Oracle 11.2.0.2 plus Exadata BP12 (patch 12982245)
  • One-off patch 13340675 contains the RMAN extension and is currently available on top of BP12 on Oracle 11.2.0.2

Let me know if you have further questions - and this restriction on Oracle 11.2.0.2 with BP12 applies to the intermediate instance you'll use to roll forward your converted incremental backups only. Source might be Oracle 10.2 whereas target might be also an Oracle 11.2.0.3 installation.

About

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle Corp

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite or remotely with reference customers. Acting as interlink between customers/partners and the Upgrade Development.

Follow me on TWITTER

Contact me via LinkedIn or XING

Search

Archives
« July 2015
SunMonTueWedThuFriSat
   
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
       
Today
Oracle related Tech Blogs
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers