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:
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.