Plan change using load_plans_from_cursor_cache

This post is more of a note for myself and might be helpful to few other.

Assuming db is 11gR2 and baselines/spm is used.

When a new query is introduced in db, it might be that it runs with the good plan, but sometimes it picks up wrong plan. It could be that Index Range Scan could have been done, but the sql is going for Full Table Scan (FTS) causing performance issue.

Few of the times we do have issues were the sql is newly introduced to the system and starts taking execution time longer than expected or increase the CPU or sessions starts piling up with some particular session wait event.

Now supposing, a new sql is introduced and you see lots of sessions running it doing ‘direct path reads’ and the sql is taking more than expected time as mostly it would be doing FTS, what can be done in such case?

If we have the same sqlid running fine as expected in some other prod db we can simply migrate the baseline using the below steps (just overview) —

1. dbms_spm.create_stgtab_baseline
2. dbms_spm.pack_stgtab_baseline
3. Export the staging table from source db
4. Import the dump in target db
5. dbms_spm.unpack_stgtab_baseline

In case you don’t have it running anywhere and you know an Index scan would be better you can fake the plan by generating an execution plan using index and transfer its execution plan to bad performing sql using dbms_spm.load_plans_from_cursor_cache().

Original sqlid 9x7g8gyjzr95d is doing FTS and its sql_handle is SQL_677er77f1f7bf077 and we have index which can be used for better performance.

sql> !more bind.sql
accept SQL_ID prompt 'Enter SQL_ID:- '
col name format a10;
col VALUE_STRING format a30;
select sql_id, NAME, position,DATATYPE_STRING, VALUE_STRING from v$sql_bind_capture where sql_id = '&SQL_ID';

Enter SQL_ID:- 9x7g8gyjzr95d
old   1: select sql_id, NAME, position,DATATYPE_STRING, VALUE_STRING from v$sql_bind_capture where sql_id = '&SQL_ID'
new   1: select sql_id, NAME, position,DATATYPE_STRING, VALUE_STRING from v$sql_bind_capture where sql_id = '9x7g8gyjzr95d'

------------- ---------- ---------- --------------- ------------------------------
9x7g8gykrz95d :1		  1 VARCHAR2(128)   test.xxxxxx

sql>variable lu VARCHAR2(128)
sql>exec :lu:='test.xxxxxx'

PL/SQL procedure successfully completed.

sql>select /*+ index(ia i_demand_src) */ creation_date - time_util.local_time_offset_from_utc() creation_date, ia.* from invent_audits ia where demand_src = :lu


2 rows selected

sql>select sql_id,plan_hash_value from v$sql where sql_text like 'select /*+ index(ia i_demand_src) */ %';

------------- ---------------
vbw0xrhd0nv00	    1852372768

Now we want the plan_hash_value 1852372768 to be used by our original query.So,

  l_plans_loaded  PLS_INTEGER;
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '&sql_id',
    plan_hash_value => '&plan_hash_value',
    sql_handle => '&handle');
  DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
Enter value for sql_id: vbw0xrhd0nv00  (The new sql_id, with index)
Enter value for plan_hash_value: 1852372768 (The new plan_hash_value for the new sql_id using index)
Enter value for handle: SQL_677er77f1f7bf077 (The sql_handle of the original/problemetic sql with sql_id 9x7g8gyjzr95d)

SQL_Id and plan_hash_value is more than enough to make it possible to create an SQL Baseline for one query using the execution plan for the other query.

The new baseline will be Enabled and Accepted ‘YES’ and for older baseline ACCEPTED will be changed to NO.

Flush the sql_id once after the new baseline is created 😉

Hope this helps!!!!

One thought on “Plan change using load_plans_from_cursor_cache

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s