SQL Plan Baselines

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² seildi.


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² seildi.


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;

Posted in DBA
Leave a Reply

Your email address will not be published.