Re: Survey: 11g plan management (DBMS_SPM)

From: Job Miller <>
Date: Mon, 20 Jun 2011 06:24:23 -0700 (PDT)
Message-ID: <>

There will be an OOW session from a bank that is using SPM for one of their applications.

I worked with an organization that was using SPM selectively and preemptively to mine prior AWR reports for Top SQL (by execution/cpu/gets) to create baselines for all the most frequently executed statements. 

That doesn't mean you won't need to create new baselines for things that were low volume and fine before upgrade that go awry in an upgrade, but when you pair SPM with SPA (from Real Application Testing) you have a powerful capability.

You can mine the cursor cache for a few hundred thousand SQL statements over a period of time, put those into an STS, re-execute those SQL statements proactively in the new environment, and then ONLY create baselines for the regressed statements that benefit from the 10g plan or a profile creation (which creates a plan that can be added to the baseline).


  • On Mon, 6/20/11, Marcin Przepiorowski <> wrote:

From: Marcin Przepiorowski <> Subject: Re: Survey: 11g plan management (DBMS_SPM) To:
Date: Monday, June 20, 2011, 8:59 AM

2011/6/20  <>:
> Thank you all.
> But did anyone try to use SPM on a global scale? To freeze a stable
> production application ? Sounds scarry to me but how to know w/o trying ?
> I can guess some issue like slower sql parsing, latching/"mutexing", etc
> with a large number of SQL baselines. Would be interesting to know load on
> CPU either.


I'm using it to freeze plans for whole application - and it is running quite well even with 300 - 400 plans in dba_sql_plan_baseline. Of course there are issues with plans and I'm going to blog about that soon - there is a few gotcha but if you are aware it is nice to use.


Marcin Przepiorowski

Received on Mon Jun 20 2011 - 08:24:23 CDT

Original text of this message