Wednesday, June 1, 2016

How to pin a plan to a sql_id with dbms_spm

Important: This package is not RAC aware, so running LOAD_PLANS_FROM_CURSOR_CACHE on the wrong node means it won't load. use gv$sql, identify on which node it's running by checking column inst_id and logon to that specific node to complete the procedure below.

This procedure is very useful, because it allows the association of a better plan, maybe obtained using a hint, typically after migration from a version to another, say using alter session optimizer_features_enable='11.2.0.4' to a SQL which is not performing as well as it was before in the new version.

Identify the sql_id and the bad plan

Every SQL commands are assigned a sql_id, this sql_id using a hash function based on the body of the SQL text. This includes formatting and comments.
So a SQL can perform the exact same task, a developer may call it the "same" query, but if it's written with an extra carriage return, it has a different sql_id.

So make sure that the sql_id you are looking for is the one coming from the application.

These views will help with the task:
  • v$sql and gv$sql in RAC.
  • dba_hist_sqltext - If the query can't be found in v$sql
  • dba_hist_sqlstat to have some history. Here's a query using dba_hist_sqlstat

Load the bad plan

declare
l_pls PLS_INTEGER;
begin
l_pls :=
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (  sql_id=>'', plan_hash_value =>,fixed =>'NO',enabled=>'NO');
dbms_output.put_line(l_pls);
end;
/
Once loaded, obtain the sql_handle from dba_sql_plan_baselines.
select *
from dba_sql_plan_baselines
order by created desc;

Now associate the good plan (plan_hash_value) with the sql_id/sql_handle for the sql_id obtained above. 

The good plan generally comes from a different source, possibly a developer working on that SQL with hints or alter session statements or simply the plan changed for worse and a better plan exists in the AWR views. For example running a query with explain plan.




declare
l_pls PLS_INTEGER;
begin
l_pls :=
dbms_spm.load_plans_from_cursor_cache(  sql_id=>'',plan_hash_value=> 1509555798, sql_handle =>'');
end;
/

No comments :

Post a Comment