Enforce Execution Plan of “hinted” SQL To “non-hinted” SQL

Sometimes a SQL performs better when given an Optimizer hint.  However in some scenarios it is not possible to modify the SQL (maybe it is vendor code).  Or a SQL performs better in one database but it is using different plan in production database and it is very slow.  In this article, I’ll cover how to force an execution plan generated by “hinted” SQL to “non-hinted” SQL using SQL Plan Management (SPM) with example.

Before we start you’ll need following three information:

  • Original “non-hinted” SQL ID
  • “Hinted” SQL ID
  • Plan Hash Value (PHV) of “Hinted” SQL 

Step-by-step guide

Let’s assume we have following information and SQLs are still in cache:

  • Original “non-hinted” SQL ID: 7m64ushfczttq 
  • “Hinted” SQL ID:  c2w8ztp115a5g
  • Plan Hash Value (PHV) of “hinted” SQL:  2159323895

1. Load original “non-hinted” SQL to SPM

--load non-hinted (normal) sql 
SET SERVEROUTPUT ON
DECLARE
L_PLANS_LOADED PLS_INTEGER;
BEGIN L_PLANS_LOADED :=  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
SQL_ID => '7m64ushfczttq');
DBMS_OUTPUT.PUT_LINE ('Plans Loaded: '|| L_PLANS_LOADED);
END;


Plans Loaded: 1
PL/SQL procedure successfully completed.

2. Get SQL Handle and Plan name for “non-hinted” SQL

--get sql_handle
col sql_handle for a22
col plan_name for a40
col enabled for a5
SELECT sql_handle, plan_name, enabled
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%INSERT INTO T1 (COL1, COL2, COL3, COL4)%';

SQL_HANDLE             PLAN_NAME                                ENABL
---------------------- ---------------------------------------- -----
SQL_4d6c0be445ee1b45   SQL_PLAN_4uv0bwj2yw6u5314cc063           YES

1 row selected.

3. Plan Baseline has been loaded with the current execution plan which we don’t want. So we must disable it using SQL Handle and Plan name from above.

--disable plan
SET SERVEROUTPUT ON
DECLARE
l_plans_altered   PLS_INTEGER;
BEGIN
l_plans_altered :=DBMS_SPM.alter_sql_plan_baseline (
sql_handle        => 'SQL_4d6c0be445ee1b45',
plan_name         => 'SQL_PLAN_4uv0bwj2yw6u5314cc063',
attribute_name    => 'enabled',
attribute_value   => 'NO');
DBMS_OUTPUT.put_line ('Plans Altered: ' || l_plans_altered);
END;
/

Plans Altered: 1
PL/SQL procedure successfully completed.

4. Verify that SQL Plan is disabled

--Verify
col sql_handle for a22
col plan_name for a40
col enabled for a5
SELECT sql_handle, plan_name, enabled
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%INSERT INTO T1 (COL1, COL2, COL3, COL4)%';

SQL_HANDLE             PLAN_NAME                                ENABL
---------------------- ---------------------------------------- -----
SQL_4d6c0be445ee1b45   SQL_PLAN_4uv0bwj2yw6u5314cc063           NO 

1 row selected.

5. Now we need to associate execution plan of “hinted” SQL with original “non-hinted” SQL.  In the load procedure we will provide the SQL Handle of “non-hinted” SQL from above step.

--Associate hinted plan with original SQL Stmt's SQL Handle
SET SERVEROUTPUT ON
DECLARE
L_PLANS_LOADED   PLS_INTEGER;
BEGIN
L_PLANS_LOADED := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
SQL_ID            => 'c2w8ztp115a5g',
PLAN_HASH_VALUE   => '2159323895',
SQL_HANDLE        => 'SQL_4d6c0be445ee1b45');
DBMS_OUTPUT.PUT_LINE ('Plans Loaded: ' || L_PLANS_LOADED);
END;
/

Plans Loaded: 1
PL/SQL procedure successfully completed.

6. Verify that only one Plan is enabled

--Verify
col sql_handle for a22
col plan_name for a40
col enabled for a5
SELECT sql_handle, plan_name, enabled
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%INSERT INTO T1 (COL1, COL2, COL3, COL4)%';

SQL_HANDLE             PLAN_NAME                                ENABL
---------------------- ---------------------------------------- -----
SQL_4d6c0be445ee1b45   SQL_PLAN_4uv0bwj2yw6u5314cc063 NO  
SQL_4d6c0be445ee1b45   SQL_PLAN_4uv0bwj2yw6u5d2c58f83           YES 

7. To confirm that execution plan we want is loaded, we can display it using following SQL.

SET LONG 10000 pages 0 lines 200 trim on trims on
SELECT *
FROM   TABLE( DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_4uv0bwj2yw6u5d2c58f83')); 
--------------------------------------------------------------------------------
SQL handle: SQL_4d6c0be445ee1b45
SQL text: INSERT INTO T1 (COL1, COL2, COL3, COL4)...
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan name: SQL_PLAN_4uv0bwj2yw6u5d2c58f83        
Plan id: 3536162691
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
Plan rows: From dictionary
--------------------------------------------------------------------------------
Plan hash value: 2159323895
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |                          |       |       |       |  1440K(100)|          |
|   1 |  LOAD TABLE CONVENTIONAL       | T1    |       |       |       |     0   (0)|          |
|   2 |   HASH UNIQUE                  |                          |     1 |   111 |       |  1440K  (1)| 00:00:57 |
|*  3 |    HASH JOIN RIGHT OUTER       |                          |     1 |   111 |       |  1440K  (1)| 00:00:57 |
|   4 |     VIEW                       | VW_DTP_DA2E497E          |     1 |    30 |       |   717K  (1)| 00:00:29 |
|   5 |      SORT UNIQUE               |                          |     1 |    30 |       |   717K  (1)| 00:00:29 |
|   6 |       VIEW                     |                          |     1 |    30 |       |   717K  (1)| 00:00:29 |
|*  7 |        HASH JOIN               |                          |     1 |    78 |    17M|   717K  (1)| 00:00:29 |
|   8 |         TABLE ACCESS FULL      | T1   |   286K|    13M|       |  2953   (2)| 00:00:01 |
|*  9 |         HASH JOIN              |                          |  7530K|   193M|       |   699K  (1)| 00:00:28 |
|* 10 |          TABLE ACCESS FULL     | T2         |     7 |    14 |       |     2   (0)| 00:00:01 |
|* 11 |          VIEW                  | index$_join$_008         |    63M|  1522M|       |   699K  (1)| 00:00:28 |
|* 12 |           HASH JOIN            |                          |       |       |       |     0   (0)|          |
|* 13 |            INDEX RANGE SCAN    | IX4 |    63M|  1522M|       |   119K  (2)| 00:00:05 |
|  14 |            INDEX FAST FULL SCAN| IX1     |    63M|  1522M|       |   462K  (1)| 00:00:19 |
|* 15 |     HASH JOIN                  |                          |     1 |    81 |    17M|   723K  (1)| 00:00:29 |
|  16 |      TABLE ACCESS FULL         | T1   |   286K|    13M|       |  2953   (2)| 00:00:01 |
|* 17 |      HASH JOIN                 |                          |  9694K|   277M|       |   699K  (1)| 00:00:28 |
|  18 |       TABLE ACCESS FULL        | T2         |    52 |   260 |       |     2   (0)| 00:00:01 |
|* 19 |       VIEW                     | index$_join$_004         |    63M|  1522M|       |   699K  (1)| 00:00:28 |
|* 20 |        HASH JOIN               |                          |       |       |       |     0   (0)|          |
|* 21 |         INDEX RANGE SCAN       | IX4 |    63M|  1522M|       |   119K  (2)| 00:00:05 |
|  22 |         INDEX FAST FULL SCAN   | IX1     |    63M|  1522M|       |   462K  (1)| 00:00:19 |
-------------------------------------------------------------------------------------------------------------------

82 rows selected.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *