Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Shibaji.

Asked: March 24, 2017 - 3:29 am UTC

Last updated: March 28, 2017 - 12:23 pm UTC

Version: 11G

Viewed 1000+ times

You Asked

Hi,

It would be very helpful for me if you share the detailed steps for forcing a query to select a particular hash plan value whenever the same is executed using SPM(Sequel Performance Management). I would like to know the tables and query which will be used for fetching the sql id and plan hash value for a particular query.

Thanks in Advance....

Regards
Shibaji

and Connor said...

Step 1 - capture the SQL you are interested in. You can do this in various ways, for example, if you know the SQL_ID then

variable pls number
begin
  :pls := dbms_spm.load_plans_from_cursor_cache( 
                  attribute_name => 'SQL_ID', 
                  attribute_value => '[your SQL ID]'); 
end;


or if you know the sql text (or part thereof) you can do:

variable pls number
begin
  :pls := dbms_spm.load_plans_from_cursor_cache( 
                  attribute_name => 'SQL_TEXT', 
                  attribute_value => '%select%from%my_table%where%'); 
end;


and in either case, you can limit to the desired plan hash value.

Once you've done that, you're pretty much done - we'll use that plan until directed otherwise. New plans will be picked up, but you would need to manually evolve those plans to activate them.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 28, 2017 - 10:41 am UTC

Hi,

Thanks for your reply. It would be helpful for me if you can let me know how to tag a query to a particular has plan hash value...

Also kindly let me know how to activate plans manually using SPM.

Thanks In Advance

Regards
Shibaji

More to Explore

DBMS_SPM

More on PL/SQL routine DBMS_SPM here