Creating SQL Plan Baseline from a different SQL statement

Really just documenting this for myself because I couldn’t easily find this on the web.  Most of the steps are taken from this page:

http://askdba.org/weblog/2011/12/plan-stability-using-sql-profiles-and-sql-plan-management/

Observe the following query doing a full table scan:

select * from table(dbms_xplan.display_cursor('8jng4anj19km4',
                                               format=>'peeked_binds'));

SELECT COUNT(1) FROM TOTS_QUE WHERE OTQUE_MODULE = :B3 AND OTQUE_DOCTYP 
= :B2 AND OTQUE_DOCRUNNUM = :B1 AND NVL(OTQUE_ERROR,'N') = 'N'

SQL_ID  8jng4anj19km4, child number 0
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |    16 |            |          |
|*  2 |   TABLE ACCESS FULL| TOTS_QUE |     1 |    16 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
   1 - :B3 (VARCHAR2(30), CSID=871): 'PR'
   2 - :B2 (VARCHAR2(30), CSID=871): 'PR'
   3 - :B1 (VARCHAR2(30), CSID=871): '8631426

Check the efficiency of the plan with the following query:

SELECT EXECUTIONS, ROUND(BUFFER_GETS/EXECUTIONS) FROM V$SQL 
   WHERE SQL_ID = '8jng4anj19km4';   
19808    29348

Executed 20,000 times since the last parse, average of 29,000 buffer gets per execution.  Note I am not going to go into the reasons why this inefficient plan is being chosen.

If we use the peeked bind variables to test, and force dynamic sampling, we can produce a more suitable plan:

SELECT /*+GATHER_PLAN_STATISTICS DYNAMIC_SAMPLING(11) */ COUNT(1) FROM TOTS_QUE 
WHERE OTQUE_MODULE = 'PR'  AND 
      OTQUE_DOCTYP = 'PR' AND 
      OTQUE_DOCRUNNUM = '8631426' AND 
      NVL(OTQUE_ERROR,'N') = 'N';

select * from table(dbms_xplan.display_cursor(FORMAT=>'ALLSTATS LAST'));

SQL_ID  87fcdy0wm5xhd, child number 0
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |            |      1 |        |      1 |00:00:00.01 |      59 |
|   1 |  SORT AGGREGATE                      |            |      1 |      1 |      1 |00:00:00.01 |      59 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TOTS_QUE   |      1 |      1 |      0 |00:00:00.01 |      59 |
|*  3 |    INDEX RANGE SCAN                  | OTQUE_IDX1 |      1 |     13 |      0 |00:00:00.01 |      59 |
-------------------------------------------------------------------------------------------------------------

Note the SQL_ID here.  Observe that with the index access, only 59 buffer gets are required, compared to 29,000 using full table scan.

Lets create a sql plan baseline for the inefficient plan.  Note it is created as not enabled, so it won’t actually be used.

DECLARE
   dummy pls_integer;
BEGIN
   dummy := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
               sql_id => '8jng4anj19km4',
               enabled =>'NO');   
END;
/
SELECT sql_handle FROM DBA_SQL_PLAN_BASELINES ORDER BY CREATED DESC;
SQL_6d2f47d2acbf3c9b
..

The next bit it the important step, we can load the plan from the efficient plan (even though it has slightly different sql text, and a different sql_id) and associate it with the SQL_HANDLE  for the baseline we have just created.

DECLARE
   dummy pls_integer;
BEGIN
   dummy := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
               sql_id => '87fcdy0wm5xhd', 
               sql_handle =>'SQL_6d2f47d2acbf3c9b', 
               enabled => 'YES');   
END;
/
DECLARE
   dummy pls_integer;
BEGIN
   dummy := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_HANDLE => 'SQL_6d2f47d2acbf3c9b',
                                              ATTRIBUTE_NAME=>'AUTOPURGE', 
                                              ATTRIBUTE_VALUE=>'NO');   
END;
/
Note the AUTO_PURGE attribute change is just personal preference to prevent these records getting deleted.

SELECT child_number, 
       SQL_PLAN_BASELINE, 
       EXECUTIONS, 
       ROUND(BUFFER_GETS/EXECUTIONS) FROM V$SQL 
WHERE SQL_ID = '8jng4anj19km4';
CHILD_NUMBER SQL_PLAN_BASELINE              EXECUTIONS ROUND(BUFFER_GETS/EXECUTIONS)                                                                
------------ ------------------------------ ---------- -----------------------------                                                                
           0                                     20648                         29371                                                                
           1 SQL_PLAN_6ubu7uaqbyg4v1b716894       2252                            12                                                                                                                                                                                                                      


Note a new sql child cursor has been created, associated with the baseline, and that it is much more efficient than the previous one.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s