SQL Plan Management

SQL Plan Management provide the platform to control the execution plan usability for oracle database. With help of SQL Plan management , You can have ability to manual interpretation to choose the best plan for you.

Create SQL plan baselines for SQL queries means accepted execution plans for all statements. The SQL plan baselines are stored in a plan history in the SYSAUX tablespace.

In Oracle database 12c the evolution job is done by an automated job as an advisor task called SYS_AUTO_SPM_EVOLVE_TASK, which is triggered by the Job “sql tuning advisor” present in auto task list table. If you can disable the job, it automatically stop the evolution task for sql baseline.

Check the automatic job status:
SELECT * FROM dba_autotask_task;

Automatic capturing of SQL Plan
Automatic capture plan enabled for SQL queries by setting the parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true.(By default is false)
It’s start capturing the execution plan for SQL queries in SQL baseline. First plan is always has accepted state.

Note: Using SQL Plan Baseline,Parameter OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to TRUE.(By default is true)

Check the SQL Plan baselines having SQL plan:
select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines;

Manually capturing of SQL Plan
We have SCOTT schema as default provided by Oracle. We proceed with an example:

1. Checked the SQL Plan Baseline table is empty or using any plan by your oracle system.

SQL> SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines;
no rows selected

2. Execute following query which will proceed for FULL Table Scan

SQL> select * from scott.emp where ename='SCOTT';

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

3. For manual load the SQL plan, Find the SQL ID from V$SQL view.

SELECT sql_id,sql_text FROM v$sql WHERE sql_text LIKE '%select * from scott.emp%';

SQL_ID        SQL_TEXT
------------- -------------------------------------------
b4xzjkzz30q5j select * from scott.emp where ename='SCOTT'

4. Load the Plan manually in SQL baseline with help of package:

SET SERVEROUTPUT ON
DECLARE
l_plans_captured PLS_INTEGER;
BEGIN
l_plans_captured := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => 'b4xzjkzz30q5j');DBMS_OUTPUT.put_line('Plans captured: ' || l_plans_captured);
END;
/

5. Check again the SQL baselines table for SQL plan

SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines WHERE sql_text LIKE '%emp%'

SQL_HANDLE             PLAN_NAME                      ENA ACC
--------------------- ------------------------------ --- ---
SQL_e523596056c54c6e   SQL_PLAN_fa8utc1bcam3fd8a279cc YES YES

6. Created the index on ename column and checked the execution plan again

Create index scott.emp_name_idx on scott.emp(ename);

set autotrace traceonly
select * from scott.emp where ename=’SCOTT’;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

7. Full scan is used in query,On Check SQL plan baseline table.One more plan but not in accepted state.

select sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselinesSELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines WHERE sql_text LIKE '%emp%';

SQL_HANDLE             PLAN_NAME                      ENA ACC
---------------------- ------------------------------ --- ---
SQL_e523596056c54c6e   SQL_PLAN_fa8utc1bcam3f38fa3838 NO  NO 
SQL_e523596056c54c6e   SQL_PLAN_fa8utc1bcam3fd8a279cc YES NO

 

8. Manually Evolve the SQL Plan in 11g/12c
In 11g for envolve the plan:

SET LONG 10000
SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_e523596056c54c6e')
FROM dual;

In 12c envolve the plan:

Step 1: Create task
SET SERVEROUTPUT ON
DECLARE
l_return VARCHAR2(32767);
BEGIN
l_return := DBMS_SPM.create_evolve_task(sql_handle => 'SQL_e523596056c54c6e');
DBMS_OUTPUT.put_line('Task Name: ' || l_return);
END;
/
Task Name: TASK_1644

Step 2: Evolve task
SET SERVEROUTPUT ON
DECLARE
l_return VARCHAR2(32767);
BEGIN
l_return := DBMS_SPM.execute_evolve_task(task_name => ‘TASK_1644’);
DBMS_OUTPUT.put_line(‘Execution Name: ‘ || l_return);
END;
/
Execution Name: EXEC_1591

Step 3: Generate the report of task generated above.

SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100
SELECT DBMS_SPM.report_evolve_task(task_name => ‘TASK_1644’, execution_name => ‘EXEC_1591’) AS output FROM dual;

OUTPUT
--------------------------------------------------------------------------------GENERAL INFORMATION SECTION
--------------------------------------------------------------------------------
Task Information:
---------------------------------------------
Task Name            : TASK_1644
Task Owner           : SYS
Execution Name       : EXEC_1591
Execution Type       : SPM EVOLVE
Scope                : COMPREHENSIVE
Status               : COMPLETED
Started              : 01/24/2017 00:32:24
Finished             : 01/24/2017 00:32:25
Last Updated         : 01/24/2017 00:32:25
Global Time Limit    : 2147483646
Per-Plan Time Limit  : UNUSED
Number of Errors     : 0
--------------------------------------------------------------------------------
SUMMARY SECTION
--------------------------------------------------------------------------------
  Number of plans processed  : 1
  Number of findings         : 1
  Number of recommendations  : 1
  Number of errors           : 0
--------------------------------------------------------------------------------
DETAILS SECTION
--------------------------------------------------------------------------------
 Object ID          : 2
 Test Plan Name     : SQL_PLAN_fa8utc1bcam3f38fa3838
 Base Plan Name     : SQL_PLAN_fa8utc1bcam3fd8a279cc
 SQL Handle         : SQL_e523596056c54c6e
 Parsing Schema     : SYS
 Test Plan Creator  : SYS
 SQL Text           : select * from scott.emp where ename='SCOTT'
Execution Statistics:
-----------------------------
                    Base Plan                     Test Plan
                    ----------------------------  ------------------
 Elapsed Time (s):  .000009                       .000007
 CPU Time (s):      0                             0
 Buffer Gets:       0                             0
 Optimizer Cost:    3                             2
 Disk Reads:        0                             0
 Direct Writes:     0                             0
 Rows Processed:    0                             0
 Executions:        10                            10

FINDINGS SECTION
--------------------------------------------------------------------------------
Findings (1):
-----------------------------
 1. The plan was verified in 0.04600 seconds. It passed the benefit criterion
    because its verified performance was 3.50000 times better than that of the
    baseline plan.

Recommendation:
-----------------------------
 Consider accepting the plan. Execute
 dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_1644', object_id => 2,
 task_owner => 'SYS');

EXPLAIN PLANS SECTION
--------------------------------------------------------------------------------
Baseline Plan
-----------------------------
 Plan Id          : 101
 Plan Hash Value  : 3634526668

---------------------------------------------------------------------
| Id  | Operation           | Name | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    1 |    38 |    3 | 00:00:01 |
| * 1 |   TABLE ACCESS FULL | EMP  |    1 |    38 |    3 | 00:00:01 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("ENAME"='SCOTT')

Test Plan
-----------------------------
 Plan Id          : 102
 Plan Hash Value  : 955922488
--------------------------------------------------------------------------------
| Id | Operation                            | Name         | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |              |    1 |    38 |    2 | 00:00:01 |
|  1 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP          |    1 |    38 |    2 | 00:00:01 |
| *2 |    INDEX RANGE SCAN                  | EMP_NAME_IDX |    1 |       |    1 | 00:00:01 |
--------------------------------------------------------------------------------Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ENAME"='SCOTT')
--------------------------------------------------------------------------------

Step 4: Accept the plan in oracle 12c
exec dbms_spm.accept_sql_plan_baseline(task_name => ‘TASK_1644’, object_id => 2, task_owner => ‘SYS’);
OR
SET SERVEROUTPUT ON
DECLARE
l_return NUMBER;
BEGIN
l_return := DBMS_SPM.implement_evolve_task(task_name => ‘TASK_21’);
DBMS_OUTPUT.put_line(‘Plans Accepted:’ || l_return);
END;
/

9. Check the status in sql plan baselines view, it show execution plan in accepted state

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines ;

SQL_HANDLE                PLAN_NAME                           ENA ACC
------------------------- ----------------------------------- --- ---
SQL_e523596056c54c6e      SQL_PLAN_fa8utc1bcam3f38fa3838      YES YES
SQL_e523596056c54c6e      SQL_PLAN_fa8utc1bcam3fd8a279cc      YES YES

10.Test the SQL Queries above used and found index is used. Now both the plan is in accepted state.

select * from scott.emp where ename=’SCOTT’;

Execution Plan
----------------------------------------------------------
Plan hash value: 2092603121
-----------------------------------------------------------------------------
| Id | Operation                         | Name       |Rows|Bytes |Cost(%CPU)
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT                  |            | 1  |   38 |    2 (0)|
|  1 |TABLE ACCESS BY INDEX ROWID BATCHED| EMP        | 1  |   38 |    2 (0)|
|*  2    INDEX RANGE SCAN                |EMP_NAME_IDX| 1  |      |    1 (0)|
-----------------------------------------------------------------------------

11. You can modified it by changing attribute of SQL plan baseline. Suppose you don’t want to use the new plan and switch to old plan.
You can modified the enabled parameter to No. So optimizer not able to use the new plan at all. you can alter attributes with ALTER_SQL_PLAN_BASELINE function.

enabled (YES/NO) : YES : plan is available for the optimizer to use.
fixed (YES/NO) : YES : Fixed plans got more priority than non-fixed plans.

Disable the latest plan as below:

declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => 'SQL_e523596056c54c6e',plan_name => 'SQL_PLAN_fa8utc1bcam3f38fa3838',
attribute_name => 'ENABLED', attribute_value => 'NO');
end;
/

12. Verify again the sql query, its not using index and switch to old plan.

SQL>
SQL>Select * from scott.emp where ename='SCOTT';

Execution Plan
--------------------------
Plan hash value: 3956160932
------------------------------------------------------------------
| Id | Operation         | Name| Rows | Bytes| Cost(%CPU)| Time  |
------------------------------------------------------------------
|  0| SELECT STATEMENT  |     |     1|    38|     3 (0)| 00:00:01|
|* 1|  TABLE ACCESS FULL| EMP |     1|    38|     3 (0)| 00:00:01|
------------------------------------------------------------------ 

Transfer the SQL plan in between two database/backup of sql plan before patch/upgrade.
1. Create a staging table for transfer the stats

BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'scott_Plan_backup',
table_owner => 'SCOTT',
tablespace_name => 'USERS');
END;
/

2. Load the plan baseline to staging table

SET SERVEROUTPUT ON
DECLARE
var_plans_loaded PLS_INTEGER;
BEGIN
var_plans_loaded := DBMS_SPM.pack_stgtab_baseline(
table_name => 'scott_Plan_backup',
table_owner => 'SCOTT');DBMS_OUTPUT.put_line('Plans loaded: ' || var_plans_loaded);
END;
/
Plans loaded: 2

3. Take an export/EXPDP backup of staging table

4. Move to another server

5. Import/IMPDP the staging table

6. Unload the execution plan to database server:

SET SERVEROUTPUT ON
DECLARE
l_plans_unloaded PLS_INTEGER;
BEGIN
l_plans_unloaded := DBMS_SPM.unpack_stgtab_baseline(
table_name => 'scott_Plan_backup',
table_owner => 'SCOTT');DBMS_OUTPUT.put_line('Plans Unloaded: ' || l_plans_unloaded);
END;
/
Plans Unloaded: 2


Drop the SQL plan
Specify only the sql_handle, it will drop all the plan related to SQLhandle.

SET SERVEROUTPUT ON
DECLARE
l_plans_dropped PLS_INTEGER;
BEGIN
l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (sql_handle => 'SQL_e523596056c54c6e');
DBMS_OUTPUT.put_line('Plans Dropped: ' || l_plans_dropped);
END;
/

OR
Drop the specific plan present in SQL handle

SET SERVEROUTPUT ON
DECLARE
l_plans_dropped PLS_INTEGER;
BEGIN
l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
sql_handle => NULL,
plan_name => 'SQL_PLAN_fa8utc1bcam3f38fa3838');
DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/

For displaying the SQL plan baselines plan

SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE( sql_handle=>'SYS_SQL_209d10fabbedc741',format=>'basic'));


For verification purpose
Check whether oracle is using SQL baseline by joining v$SQL view:

Select s.sql_text, b.plan_name, b.origin, b.accepted
From dba_sql_plan_baselines b, v$sql s
Where s.exact_matching_signature = b.signature
And s.SQL_PLAN_BASELINE = b.plan_name;

Reference from
https://docs.oracle.com/cd/E11882_01/server.112/e41573/optplanmgmt.htm#PFGRF95121
https://oracle-base.com/articles/12c/adaptive-sql-plan-management-12cr1
https://oracle-base.com/articles/11g/sql-plan-management-11gr1