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) –
3. Export the staging table from source db
4. Import the dump in target db
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'; sql>@bind 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' SQL_ID NAME POSITION DATATYPE_STRING VALUE_STRING ------------- ---------- ---------- --------------- ------------------------------ 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) */ %'; SQL_ID PLAN_HASH_VALUE ------------- --------------- vbw0xrhd0nv00 1852372768
Now we want the plan_hash_value 1852372768 to be used by our original query.So,
SET SERVEROUTPUT ON DECLARE l_plans_loaded PLS_INTEGER; BEGIN 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); END; / 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!!!!