SQL plan baseline is one of the most used tuning technique in Oracle database. Basically, with SQL plan baseline, you can change the execution plan of a sql statement without modifying itself. Also, it is much better than using hints. Because, you enable the optimizer to evolve your query plan over time. In other words, If a better execution plan will be occured then the query could be adapted to it easily. I am not going to get into details about how does it work. Instead, I am going to explain that how you can easily use it.
Let’s create a test table.
CREATE TABLE TEST_TAB
AS
SELECT * FROM DBA_OBJECTS T, (SELECT * FROM DUAL CONNECT BY LEVEL <= 15);
CREATE INDEX IND_TEST_TAB1 ON TEST_TAB(OBJECT_TYPE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST_TAB');
Suppose that you have the following query in your production code and you want the execution plan to use the index range scan however you couldn’t change the production code.
SELECT /*+ FULL(A) */ * FROM TEST_TAB A WHERE OBJECT_TYPE = 'JAVA DATA';
Because we want the optimizer to choose the index range scan, SQL plan baseline comes into play at this point. First, let’s find the SQL_ID of the above query. I explain in this post that how you find the SQL_ID of a specific query.
SELECT SQL_ID, PLAN_HASH_VALUE, SQL_TEXT FROM V$SQLSTATS WHERE SQL_TEXT LIKE q'[SELECT /*+ FULL(A) */ * FROM TEST_TAB A WHERE OBJECT_TYPE = 'JAVA DATA']';
SQL_ID PLAN_HASH_VALUE SQL_TEXT
--------------- -------------------- ------------------------------------------------------------------------
1az9rj5sh2f46 3962208483 SELECT /*+ FULL(A) */ * FROM TEST_TAB A WHERE OBJECT_TYPE = 'JAVA DATA'
Time to create SQL plan baseline for the unconvertible query. Then, set the ENABLED attribute to false.
DECLARE
CNT NUMBER;
BEGIN
--Manuel plan loading
CNT := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'1az9rj5sh2f46');
END;
SELECT ACCEPTED, ENABLED, FIXED, ORIGIN, SQL_HANDLE, PLAN_NAME FROM DBA_SQL_PLAN_BASELINES WHERE TRUNC(CREATED) = TRUNC(SYSDATE);
ACCEPTED ENABLED FIXED ORIGIN SQL_HANDLE PLAN_NAME
---------- ---------- ---------- ------------------------------ ------------------------ -------------------------------
YES YES NO MANUAL-LOAD-FROM-CURSOR-CACHE SQL_fe666bc157228ad2 SQL_PLAN_gwtmbs5bk52qk94cd39d6
DECLARE
L_PLANS_ALTERED PLS_INTEGER;
BEGIN
L_PLANS_ALTERED := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
SQL_HANDLE => 'SQL_fe666bc157228ad2',
PLAN_NAME => 'SQL_PLAN_gwtmbs5bk52qk94cd39d6',
ATTRIBUTE_NAME => 'enabled',
ATTRIBUTE_VALUE => 'NO'
);
DBMS_OUTPUT.PUT_LINE('Plans Altered: ' || L_PLANS_ALTERED);
END;
Add the desired plan with using PLAN_HASH_VALUE parameter.
SET LINESIZE 200
SET AUTOTRACE TRACEONLY
SELECT * FROM TEST_TAB A WHERE OBJECT_TYPE = 'JAVA DATA';
18270 sat²rlar² seildi.
Y³r³tme Plan²
----------------------------------------------------------
Plan hash value: 432639878
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18270 | 2372K| 891 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TAB | 18270 | 2372K| 891 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEST_TAB1 | 18270 | | 53 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='JAVA DATA')
¦statistikler
----------------------------------------------------------
0 recursive calls
0 db block gets
2833 consistent gets
0 physical reads
0 redo size
2429438 bytes sent via SQL*Net to client
13867 bytes received via SQL*Net from client
1219 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
18270 rows processed
SET AUTOTRACE OFF
SELECT SQL_ID, PLAN_HASH_VALUE, SQL_TEXT FROM V$SQLSTATS WHERE SQL_TEXT LIKE q'[SELECT * FROM TEST_TAB A WHERE OBJECT_TYPE = 'JAVA DATA']';
SQL_ID PLAN_HASH_VALUE SQL_TEXT
--------------- -------------------- ------------------------------------------------------------------------
ctdy97gys1jj2 432639878 SELECT * FROM TEST_TAB A WHERE OBJECT_TYPE = 'JAVA DATA'
DECLARE
CNT NUMBER;
BEGIN
CNT := SYS.DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
SQL_ID=>'ctdy97gys1jj2',
PLAN_HASH_VALUE=>'432639878',
SQL_HANDLE=>'SQL_fe666bc157228ad2'
);
END;
SELECT ACCEPTED, ENABLED, FIXED, ORIGIN, SQL_HANDLE, PLAN_NAME FROM DBA_SQL_PLAN_BASELINES WHERE TRUNC(CREATED) = TRUNC(SYSDATE);
ACCEPTED ENABLED FIXED ORIGIN SQL_HANDLE PLAN_NAME
---------- ---------- ---------- ------------------------------ ------------------------ -------------------------------
YES NO NO MANUAL-LOAD-FROM-CURSOR-CACHE SQL_fe666bc157228ad2 SQL_PLAN_gwtmbs5bk52qk94cd39d6
YES YES NO MANUAL-LOAD-FROM-CURSOR-CACHE SQL_fe666bc157228ad2 SQL_PLAN_gwtmbs5bk52qkad68ac7a
As a result, we have two different execution plan right now. One of them is enabled only. Let’s check that if it is working.
SET AUTOTRACE TRACEONLY
SELECT /*+ FULL(A) */ * FROM TEST_TAB A WHERE OBJECT_TYPE = 'JAVA DATA';
18270 sat²rlar² seildi.
Y³r³tme Plan²
----------------------------------------------------------
Plan hash value: 432639878
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18270 | 2372K| 891 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TAB | 18270 | 2372K| 891 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEST_TAB1 | 18270 | | 53 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='JAVA DATA')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / A@SEL$1
U - FULL(A) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
Note
-----
- SQL plan baseline "SQL_PLAN_gwtmbs5bk52qkad68ac7a" used for this statement
¦statistikler
----------------------------------------------------------
50 recursive calls
18 db block gets
2852 consistent gets
3 physical reads
4076 redo size
2429438 bytes sent via SQL*Net to client
13867 bytes received via SQL*Net from client
1219 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
18270 rows processed
As you can see from the note section SQL plan baseline is used for the query. In case of using multiple plan baselines for different queries, you can use the below query to find out the SQL_HANDLE and PLAN_NAME corresponding to SQL_ID.
SELECT
T.SQL_HANDLE, V.SQL_TEXT, T.PLAN_NAME, T.ENABLED
FROM DBA_SQL_PLAN_BASELINES T, V$SQLSTATS V
WHERE T.SIGNATURE = V.EXACT_MATCHING_SIGNATURE
AND V.SQL_ID = '1az9rj5sh2f46';
Moreover, in order to get the execution plan of any baseline. You can use DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE function.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(plan_name=>'SQL_PLAN_gwtmbs5bk52qkad68ac7a'));
--------------------------------------------------------------------------------
SQL handle: SQL_fe666bc157228ad2
SQL text: SELECT /*+ FULL(A) */ * FROM TEST_TAB A WHERE OBJECT_TYPE = 'JAVA DATA'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_gwtmbs5bk52qkad68ac7a Plan id: 2909318266
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD-FROM-CURSOR-CACHE
Plan rows: From dictionary
--------------------------------------------------------------------------------
Plan hash value: 432639878
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 891 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TAB | 18270 | 2372K| 891 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEST_TAB1 | 18270 | | 53 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='JAVA DATA')
In addition, you can drop the sql plan baseline with using the following code. I hope this post helps you.
SET AUTOTRACE OFF
DECLARE
L_PLANS_DROPPED PLS_INTEGER;
BEGIN
L_PLANS_DROPPED := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
SQL_HANDLE => 'SQL_fe666bc157228ad2'
--,plan_name => 'SQL_PLAN_30sq1b9nr9y6v94cd39d6'
);
END;