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.
