SQL Plan — loading a good base line from a similar statement for the original SQL statement

Previously I have posted 2 articles (one & two) about SQL Patch — changing the execution plan for a query without touching the original statement. Today I am going to talk about using DBMS_SPM package to load a good execution plan from a modified statement for the original statement which has a bad plan.

Often you need to tune a query, but you cannot find a good plan in the shared pool, otherwise you could just create a baseline using the good plan to fix the plan. It’s also not like an index missng or statistics outdated, or data skewed which needs histogram.

As a DBA, you cannot touch the code from the application, however as you test, you find out a slight modification of the query could make the optimizer to choose a different but much faster plan. Here are steps o replace the bad plan of the original query with the good plan from the modified query.

Create baselines for both querys.
Using SQL_HANDLE to create a good baseline from the modified query for the original query.
Drop the bad baseline for the original query.

Below are step by step examples for two queries with SQL_ID:

7avs45cravjh3 — original query
ftm9g7mh93tmw — modified query but has the exact same logic as original query

1. For the modified query, create a baseline on it.

This is the baseline you want to use for the original query because the execution plan is good. Query v$sql to get the plan_hash_value of a good plan.

SET SERVEROUTPUT ON
DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => 'ftm9g7mh93tmw',
  plan_hash_value => 3470630914,
  fixed => 'YES');
    
  DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/

SQL> select SQL_HANDLE, PLAN_NAME, enabled, accepted from dba_sql_plan_baselines where CREATED > sysdate - 0.1;

SQL_HANDLE                     PLAN_NAME                                          ENA ACC
------------------------------ -------------------------------------------------- --- ---
SQL_985322eee10507ad           SQL_PLAN_9hnt2xvhha1xd5224d442                     YES YES

2. For the original query, create a baseline on it as well.

Keep it in mind that it corresponds to a bad plan. But we need to get the SQL_HANDLE which is the identifier used by Oracle SQL Plan management.

SET SERVEROUTPUT ON
DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '7avs45cravjh3',
  plan_hash_value => 970238074,
  fixed => 'YES');
    
  DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;

Now I have two baselines:

SQL> select SQL_HANDLE, PLAN_NAME, enabled, accepted, created from dba_sql_plan_baselines where CREATED > sysdate -0.1 order by created;

SQL_HANDLE                     PLAN_NAME                                ENA ACC CREATED
------------------------------ ---------------------------------------- --- --- ----------------------------------
SQL_985322eee10507ad           SQL_PLAN_9hnt2xvhha1xd5224d442           YES YES 30-SEP-21 10.42.53.341968 AM
SQL_4c607d7558e54cf1           SQL_PLAN_4ss3xfpcfam7jda7d9d05           YES YES 30-SEP-21 11.17.47.233563 AM

3. With the SQL Handle — SQL_4c607d7558e54cf1, I am loading the good plan from ftm9g7mh93tmw for 7avs45cravjh3

SET SERVEROUTPUT ON
DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => 'ftm9g7mh93tmw',
  plan_hash_value => 3470630914,
  sql_handle => 'SQL_4c607d7558e54cf1',
  fixed => 'YES');
    
  DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/

Now I have 3 base lines. For 7avs45cravjh3, there are two — one is good and another is bad. They have the same SQL_HANDLE, but different PLAN_NAME.

SQL> select SQL_HANDLE, PLAN_NAME, enabled, accepted, created from dba_sql_plan_baselines where CREATED > sysdate -1 order by created;

SQL_HANDLE                     PLAN_NAME                                ENA ACC CREATED
------------------------------ ---------------------------------------- --- --- ----------------------------------
SQL_985322eee10507ad           SQL_PLAN_9hnt2xvhha1xd5224d442           YES YES 30-SEP-21 10.42.53.341968 AM
SQL_4c607d7558e54cf1           SQL_PLAN_4ss3xfpcfam7jda7d9d05           YES YES 30-SEP-21 11.17.47.233563 AM
SQL_4c607d7558e54cf1           SQL_PLAN_4ss3xfpcfam7j5224d442           YES YES 30-SEP-21 11.21.06.382028 AM

4. Drop the bad base line: SQL_PLAN_4ss3xfpcfam7jda7d9d05 for 7avs45cravjh3

SET SERVEROUTPUT ON
DECLARE
  l_plans_dropped  PLS_INTEGER;
BEGIN
 
  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
    sql_handle => NULL,
    plan_name  => 'SQL_PLAN_4ss3xfpcfam7jda7d9d05');
  DBMS_OUTPUT.put_line(l_plans_dropped);

END;
/

confirm two good base lines left for 7avs45cravjh3 and ftm9g7mh93tmw

SQL> select SQL_HANDLE, PLAN_NAME, enabled, accepted, created from dba_sql_plan_baselines where CREATED > sysdate -1 order by created;

SQL_HANDLE                     PLAN_NAME                                ENA ACC CREATED
------------------------------ ---------------------------------------- --- --- ----------------------------------
SQL_985322eee10507ad           SQL_PLAN_9hnt2xvhha1xd5224d442           YES YES 30-SEP-21 10.42.53.341968 AM
SQL_4c607d7558e54cf1           SQL_PLAN_4ss3xfpcfam7j5224d442           YES YES 30-SEP-21 11.21.06.382028 AM

Monitroing v$sql for the two statements:

SQL> select sql_id, hash_value, ELAPSED_TIME,child_number, EXECUTIONS, plan_hash_value, LAST_ACTIVE_TIME, ELAPSED_TIME/EXECUTIONS each, sql_plan_baseline from v$sql where sql_id in ('7avs45cravjh3','ftm9g7mh93tmw') order by LAST_ACTIVE_TIME, sql_id;

SQL_ID        HASH_VALUE ELAPSED_TIME CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE LAST_ACTIVE_TIME          EACH SQL_PLAN_BASELINE
------------- ---------- ------------ ------------ ---------- --------------- ------------------- ---------- -----------------------------------

ftm9g7mh93tmw 3767658108       262575            2          6      3470630914 2021-09-30 11:11:10    43762.5 SQL_PLAN_9hnt2xvhha1xd5224d442
7avs45cravjh3  783140355        82720            0          1      3470630914 2021-09-30 11:22:37      82720 SQL_PLAN_4ss3xfpcfam7j5224d442

References: