OraFAQ Forum: Performance Tuning » A question about SPM

Home » RDBMS Server » Performance Tuning » A question about SPM (11.2.0.4 on Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

A question about SPM [message #619707]

Thu, 24 July 2014 11:49

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