Manually Evolve and Accept SQL PLAN BASELINES in Oracle 12c
Manually Evolve and Accept SQL PLAN BASELINES in Oracle 12c
1. Choose the sql plan baselines you want to evolve.
In this case, there are 3 sql plan baselines which are NOT accepted. They will be analyzed by SPM EVOLVE ADVISOR.
SQL> select sql_handle, plan_name, accepted, enabled, fixed from dba_sql_plan_baselines where sql_text like 'select empno%';
SQL_HANDLE PLAN_NAME ACC ENA FIX
------------------------ ---------------------------------------- --- --- ---
SQL_58d940ad7b3ac047 SQL_PLAN_5jqa0ppxmph272685333e YES YES NO
SQL_58d940ad7b3ac047 SQL_PLAN_5jqa0ppxmph273bea498c NO YES NO
SQL_58d940ad7b3ac047 SQL_PLAN_5jqa0ppxmph272f593575 NO YES NO
SQL_58d940ad7b3ac047 SQL_PLAN_5jqa0ppxmph2796fd8705 NO YES NO
2. Create a SPM EVOLVE TASK:
SQL> var task_name varchar2(50)
SQL> exec :task_name := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle=>'SQL_58d940ad7b3ac047', plan_name=> NULL);
SQL> print task_name
TASK_NAME
------------------
TASK_226
3. Execute the SPM EVOLVE TASK:
SQL> var exec_name varchar2(50)
SQL> exec :exec_name := DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name => :task_name);
SQL> print exec_name
EXEC_NAME
------------------
EXEC_216
4. Generate a report for the SPM EVOLVE TASK:
SQL> set long 100000000
SQL> var evol_out clob
SQL> exec :evol_out := DBMS_SPM.REPORT_EVOLVE_TASK(task_name=>:task_name, type=>'TEXT', execution_name=>:exec_name);
SQL> print evol_out
5. SPM EVOLVE TASK REPORT:
In this example, the recommendation is created only for one unaccepted sql plan baseline.
No recommendation is created for the other two unaccepted sql plan baselines because there is no benefit to them.
EVOL_OUT
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------
Task Information:
---------------------------------------------
Task Name : TASK_226
Task Owner : SCOTT
Execution Name : EXEC_216
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 10/28/2014 17:16:06
Finished : 10/28/2014 17:16:06
Last Updated : 10/28/2014 17:16:06
Global Time Limit : 2147483646
Per-Plan Time Limit : UNUSED
Number of Errors : 0
---------------------------------------------------------------------------------------------
SUMMARY SECTION
---------------------------------------------------------------------------------------------
Number of plans processed : 3
Number of findings : 3
Number of recommendations : 1
Number of errors : 0
---------------------------------------------------------------------------------------------
DETAILS SECTION
---------------------------------------------------------------------------------------------
Object ID : 2
Test Plan Name : SQL_PLAN_5jqa0ppxmph273bea498c
Base Plan Name : SQL_PLAN_5jqa0ppxmph272685333e
SQL Handle : SQL_58d940ad7b3ac047
Parsing Schema : SCOTT
Test Plan Creator : SCOTT
SQL Text : select empno, dname from emp e, dept d where e.deptno=d.deptno and empno=7788
Execution Statistics:
-----------------------------
Base Plan Test Plan
---------------------------- ----------------------------
Elapsed Time (s): .000002 0
CPU Time (s) : 0 0
Buffer Gets : 0 0
Optimizer Cost : 4 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.01000 seconds. It passed the benefit criterion
because its verified performance was 2.25000 times better than that of the baseline plan.
Recommendation:
-----------------------------
Consider accepting the plan. Execute
dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_226', object_id => 2, task_owner => 'SCOTT');
EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------
Baseline Plan
-----------------------------
Plan Id : 107
Plan Hash Value : 646263614
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 4 | 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 20 | 4 | 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 20 | 4 | 00:00:01 |
| * 3 | TABLE ACCESS FULL | EMP | 1 | 7 | 3 | 00:00:01 |
| * 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 | 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 1 | 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter("EMPNO"=7788)
* 4 - access("E"."DEPTNO"="D"."DEPTNO")
Test Plan
-----------------------------
Plan Id : 108
Plan Hash Value : 1005209996
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 | 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 20 | 2 | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 7 | 1 | 00:00:01 |
| * 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 | 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 1 | 00:00:01 |
| * 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 | 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("EMPNO"=7788)
* 5 - access("E"."DEPTNO"="D"."DEPTNO")
---------------------------------------------------------------------------------------------
Object ID : 3
Test Plan Name : SQL_PLAN_5jqa0ppxmph272f593575
Base Plan Name : SQL_PLAN_5jqa0ppxmph272685333e
SQL Handle : SQL_58d940ad7b3ac047
Parsing Schema : SCOTT
Test Plan Creator : SCOTT
SQL Text : select empno, dname from emp e, dept d where e.deptno=d.deptno and empno=7788
Execution Statistics:
-----------------------------
Base Plan Test Plan
---------------------------- ----------------------------
Elapsed Time (s): .000002 .000002
CPU Time (s) : 0 .000011
Buffer Gets : 0 0
Optimizer Cost : 4 4
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.00000 seconds. It failed the benefit criterion
because its verified performance was 0.00901 times worse than that of the baseline plan.
EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------
Baseline Plan
-----------------------------
Plan Id : 109
Plan Hash Value : 646263614
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 4 | 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 20 | 4 | 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 20 | 4 | 00:00:01 |
| * 3 | TABLE ACCESS FULL | EMP | 1 | 7 | 3 | 00:00:01 |
| * 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 | 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 1 | 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter("EMPNO"=7788)
* 4 - access("E"."DEPTNO"="D"."DEPTNO")
Test Plan
-----------------------------
Plan Id : 110
Plan Hash Value : 794375541
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 4 | 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 20 | 4 | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 7 | 1 | 00:00:01 |
| * 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 | 00:00:01 |
| * 4 | TABLE ACCESS FULL | DEPT | 1 | 13 | 3 | 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("EMPNO"=7788)
* 4 - filter("E"."DEPTNO"="D"."DEPTNO")
---------------------------------------------------------------------------------------------
Object ID : 4
Test Plan Name : SQL_PLAN_5jqa0ppxmph2796fd8705
Base Plan Name : SQL_PLAN_5jqa0ppxmph272685333e
SQL Handle : SQL_58d940ad7b3ac047
Parsing Schema : SCOTT
Test Plan Creator : SCOTT
SQL Text : select empno, dname from emp e, dept d where e.deptno=d.deptno and empno=7788
Execution Statistics:
-----------------------------
Base Plan Test Plan
---------------------------- ----------------------------
Elapsed Time (s): .000001 .000008
CPU Time (s) : 0 .000011
Buffer Gets : 0 1
Optimizer Cost : 4 6
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.01000 seconds. It failed the benefit criterion
because its verified performance was 0.00901 times worse than that of the baseline plan.
EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------
Baseline Plan
-----------------------------
Plan Id : 111
Plan Hash Value : 646263614
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 4 | 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 20 | 4 | 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 20 | 4 | 00:00:01 |
| * 3 | TABLE ACCESS FULL | EMP | 1 | 7 | 3 | 00:00:01 |
| * 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 | 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 1 | 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter("EMPNO"=7788)
* 4 - access("E"."DEPTNO"="D"."DEPTNO")
Test Plan
-----------------------------
Plan Id : 112
Plan Hash Value : 2533197573
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 6 | 00:00:01 |
| * 1 | HASH JOIN | | 1 | 20 | 6 | 00:00:01 |
| * 2 | TABLE ACCESS FULL | EMP | 1 | 7 | 3 | 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 | 52 | 3 | 00:00:01 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("E"."DEPTNO"="D"."DEPTNO")
* 2 - filter("EMPNO"=7788)
---------------------------------------------------------------------------------------------
6. Manual execution of SPM EVOLVE ADVISOR does NOT accept any sql plan baseline:
SQL> select sql_handle, plan_name, accepted, enabled, fixed from dba_sql_plan_baselines where sql_text like 'select empno%';
SQL_HANDLE PLAN_NAME ACC ENA FIX
------------------------ ---------------------------------------- --- --- ---
SQL_58d940ad7b3ac047 SQL_PLAN_5jqa0ppxmph272685333e YES YES NO
SQL_58d940ad7b3ac047 SQL_PLAN_5jqa0ppxmph273bea498c NO YES NO
SQL_58d940ad7b3ac047 SQL_PLAN_5jqa0ppxmph272f593575 NO YES NO
SQL_58d940ad7b3ac047 SQL_PLAN_5jqa0ppxmph2796fd8705 NO YES NO
7. Accept the plan baseline recommended by SPM EVOLVE ADVISOR:
SQL> exec dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_226', object_id => 2, task_owner => 'SCOTT');
SQL> select sql_handle, plan_name, accepted, enabled, fixed from dba_sql_plan_baselines where sql_text like 'select empno%';
SQL_HANDLE PLAN_NAME ACC ENA FIX
------------------------ ---------------------------------------- --- --- ---
SQL_58d940ad7b3ac047 SQL_PLAN_5jqa0ppxmph272685333e YES YES NO
SQL_58d940ad7b3ac047 SQL_PLAN_5jqa0ppxmph273bea498c YES YES NO
SQL_58d940ad7b3ac047 SQL_PLAN_5jqa0ppxmph272f593575 NO YES NO
SQL_58d940ad7b3ac047 SQL_PLAN_5jqa0ppxmph2796fd8705 NO YES NO
8. If you try to accept the plan baseline which is NOT recommended by SPM EVOLVE ADVISOR, it will fail with ORA-38149.
SQL> exec dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_226', object_id => 3, task_owner => 'SCOTT');
*
ERROR at line 1:
ORA-38149: There is no recommended action for object 3 in task TASK_226.
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 3005
ORA-06512: at "SYS.DBMS_SPM", line 3387
ORA-06512: at line 1
9. You can accept the plan baseline which is NOT recommended by SPM EVOLVE ADVISOR, by setting force to TRUE.
SQL> exec dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_226', object_id => 3, task_owner => 'SCOTT', force => TRUE);
SQL> select sql_handle, plan_name, accepted, enabled, fixed from dba_sql_plan_baselines where sql_text like 'select empno%';
SQL_HANDLE PLAN_NAME ACC ENA FIX
------------------------ ---------------------------------------- --- --- ---
SQL_58d940ad7b3ac047 SQL_PLAN_5jqa0ppxmph272685333e YES YES NO
SQL_58d940ad7b3ac047 SQL_PLAN_5jqa0ppxmph273bea498c YES YES NO
SQL_58d940ad7b3ac047 SQL_PLAN_5jqa0ppxmph272f593575 YES YES NO
SQL_58d940ad7b3ac047 SQL_PLAN_5jqa0ppxmph2796fd8705 NO YES NO
Ref: How to Manually Evolve and Accept SQL PLAN BASELINES in Oracle 12c (Doc ID 1939828.1)
Comments
Post a Comment