Home » RDBMS Server » Performance Tuning » A question about SPM (11.2.0.4 on Linux)
A question about SPM [message #619707] Thu, 24 July 2014 11:49 Go to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi Experts,


I am on Oracle 11.2.0.4 on Linux. For a very problematic sql we are using SPM (we will find root cause of issues also , but for now we are using SPM, so please take that as a given fact.) This sql has this problem: One plan takes about 11 seconds and another plan takes about one second. So using SPM in our test environment I fixed the good plan and disabled the bad plan. I used the SPM procedures like DBMS_SPM.load_plans_from_cursor_cache to load plans in the baseline. It loaded 2 plans. I then identified a good and bad plan and then fixed a good plan by this procedure:

 exec DBMS_SPM.alter_sql_plan_baseline(

 sql_handle      => 'SQL_sdfljsdjflsdfklsdjf',

    plan_name       => 'SQL_PLAN_sdsdsdsdss',

    attribute_name  => 'fixed',

    attribute_value => 'YES');


However in another smaller test environment , I found that there was just one plan loaded by DBMS_SPM.load_plans_from_cursor_cache and not two plans. In this environment, there was much less data and slowness was not seen. I was assuming here too this procedure will load 2 plans but it loaded only one plan.

So I have this question: When we apply this to other higher environment, is it sure that DBMS_SPM.alter_sql_plan_baseline procedure will load both good and bad plan? My intention here is this: I know exactly what is the good plan and want to fix it. However is there a chance that DBMS_SPM.load_plans_from_cursor_cache may not even load that plan when we try to deploy this into the higher environment?

Thank you.

[Updated on: Thu, 24 July 2014 11:50]

Report message to a moderator

Re: A question about SPM [message #619712 is a reply to message #619707] Thu, 24 July 2014 12:47 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Guess you posted duplicate questions. Please respond to the post which you want to keep open since the topic name is different.
Re: A question about SPM [message #619713 is a reply to message #619712] Thu, 24 July 2014 12:48 Go to previous message
orausern
Messages: 826
Registered: December 2005
Senior Member
yes, to this one, please use this post.
Previous Topic: performance lag of the quey
Next Topic: Performance issue
Goto Forum:
  


Current Time: Thu Mar 28 13:03:39 CDT 2024