Re: Survey: 11g plan management (DBMS_SPM)

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

Job

--- On Mon, 6/20/11, Marcin Przepiorowski <pioro1@xxxxxxxxx> wrote:

From: Marcin Przepiorowski <pioro1@xxxxxxxxx>
Subject: Re: Survey: 11g plan management (DBMS_SPM)
To: Laimutis.Nedzinskas@xxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Date: Monday, June 20, 2011, 8:59 AM

2011/6/20  <Laimutis.Nedzinskas@xxxxxx>:
> 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.
>

Hi,

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.


regards,
-- 
Marcin Przepiorowski
http://oracleprof.blogspot.com
--
http://www.freelists.org/webpage/oracle-l

Other related posts: