=============================================
SQL> SHOW parameter baselines
NAME TYPE VALUE
------------------------------------ ----------- -------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL>
=============================================
Connect as SPM user and create a table as follows.
--------------------------------------------------
SQL> CREATE TABLE EMPLOYEE (code number,dept char(100),address char(1000));
Table created.
SQL>
=============================================
Populate some rows in the table
===================================
declare
c1 number;
begin
for c1 IN 1..10000 loop
insert into EMPLOYEE values(c1,'HR','BANGALORE');
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL>
=============================================
SQL> ALTER SESSION SET optimizer_use_sql_plan_baselines=TRUE;
Session altered.
SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines=TRUE;
Session altered.
SQL>
=============================================
SQL> SHOW parameter baselines
NAME TYPE VALUE
------------------------------------ ----------- -------
optimizer_capture_sql_plan_baselines boolean TRUE
optimizer_use_sql_plan_baselines boolean TRUE
SQL>
=============================================
SQL> SELECT * FROM EMPLOYEE WHERE code=999 ORDER BY code;
CODE DEPT ADDRESS
----- ---- --------
999 HR BANGALORE
SQL>
=============================================
No Plans/Baselines will be generated FIRST time when the query is executed.
------------------------------------------------------------------
SQL> SELECT sql_handle,plan_name,sql_text,origin,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'SELECT * FROM EMPLOYEE %';
no rows selected
SQL>
=============================================
Re-execute the same query once again.
--------------------------------------
SQL> SELECT * FROM EMPLOYEE WHERE code=999 ORDER BY code;
CODE DEPT ADDRESS
----- ---- --------
999 HR BANGALORE
SQL>
=============================================
1 Baseline created
-------------------
SQL> SELECT sql_handle,plan_name,sql_text,origin,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'SELECT * FROM EMPLOYEE%';
SQL_HANDLE PLAN_NAME SQL_TEXT ORIGIN ENA ACC FIX
-------------------- ------------------------------ ------------------------------------------------------------ -------------- --- --- ---
SQL_c795ea29ffc0c016 SQL_PLAN_cg5ga57zw1h0q78a4f908 SELECT * FROM EMPLOYEE WHERE code=999 ORDER BY code AUTO-CAPTURE YES YES NO
SQL>
=============================================
Check the plan the query is using
----------------------------------
SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT * FROM EMPLOYEE WHERE code=999 ORDER BY code;
CODE DEPT ADDRESS
----- ---- --------
999 HR BANGALORE
Execution Plan
----------------------------------------------------------
Plan hash value: 2119105728
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 87 | 97179 | 477 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEE | 87 | 97179 | 477 (0)| 00:00:01 | ===========> SHOWS FULL TABLE SCAN
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CODE"=999)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL plan baseline "SQL_PLAN_cg5ga57zw1h0q78a4f908" used for this statement
SQL>
SQL> SET AUTOTRACE OFF
=============================================
SQL> CREATE INDEX indx10 ON EMPLOYEE (code);
Index created.
=============================================
SQL> ANALYZE TABLE EMPLOYEE compute statistics;
Table analyzed.
=============================================
Execute the query again:
-------------------------
SQL> SELECT * FROM EMPLOYEE WHERE code=999 ORDER BY code;
CODE DEPT ADDRESS
----- ---- --------
999 HR BANGALORE
=============================================
New Plan Generated, because table was altered by adding INDEX.
-----------------------------------------------------------------
SQL> SELECT sql_handle,plan_name,sql_text,origin,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'SELECT * FROM EMPLOYEE%';
SQL_HANDLE PLAN_NAME SQL_TEXT ORIGIN ENA ACC FIX
-------------------- ------------------------------ ------------------------------------------------------- -------------- --- --- ---
SQL_c795ea29ffc0c016 SQL_PLAN_cg5ga57zw1h0q78a4f908 SELECT * FROM EMPLOYEE WHERE code=999 ORDER BY code AUTO-CAPTURE YES YES NO
SQL_c795ea29ffc0c016 SQL_PLAN_cg5ga57zw1h0qb3c7f87e SELECT * FROM EMPLOYEE WHERE code=999 ORDER BY code AUTO-CAPTURE YES NO NO
SQL>
=============================================
Check the Query Plan now:
----------------------------
SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT * FROM EMPLOYEE WHERE code=999 ORDER BY code;
CODE DEPT ADDRESS
----- ---- --------
999 HR BANGALORE
Execution Plan
----------------------------------------------------------
Plan hash value: 2119105728
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1103 | 477 (0)| 00:00:01 | ======> ROWS,BYTES Reduced but COST remains SAME
|* 1 | TABLE ACCESS FULL| EMPLOYEE | 1 | 1103 | 477 (0)| 00:00:01 | ======> Still Full Table Scan
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CODE"=999)
Note
-----
- SQL plan baseline "SQL_PLAN_cg5ga57zw1h0q78a4f908" used for this statement ====> Using Old Plan
SQL>
=============================================
Evolve the SQL HANDLE:
======================
SQL> variable report clob
SQL> exec :report:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (sql_handle=>'SQL_c795ea29ffc0c016');
PL/SQL procedure successfully completed.
SQL> Print report
REPORT
-------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------
Task Information:
---------------------------------------------
Task Name : TASK_7010
Task Owner : SPM
Execution Name : EXEC_7568
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 03/29/2017 00:11:00
Finished : 03/29/2017 00:11:00
Last Updated : 03/29/2017 00:11:00
Global Time Limit : 2147483646
Per-Plan Time Limit : UNUSED
Number of Errors : 0
-------------------------------------------------
SUMMARY SECTION
-------------------------------------------------
Number of plans processed : 1
Number of findings : 2
Number of recommendations : 1
Number of errors : 0
-------------------------------------------------
DETAILS SECTION
---------------------------------------------------
Object ID : 2
Test Plan Name : SQL_PLAN_cg5ga57zw1h0qb3c7f87e
Base Plan Name : SQL_PLAN_cg5ga57zw1h0q78a4f908
SQL Handle : SQL_c795ea29ffc0c016
Parsing Schema : SPM
Test Plan Creator : SPM
SQL Text : SELECT * FROM EMPLOYEE WHERE code=999 ORDER BY code
Execution Statistics:
-----------------------------
Base Plan Test Plan
---------------------------- --------------
Elapsed Time (s): .000132 .000004
CPU Time (s): .000133 0
Buffer Gets: 170 0
Optimizer Cost: 477 2
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 0 0
Executions: 10 10
FINDINGS SECTION
----------------------------------------------------------------
Findings (2):
-----------------------------
1. The plan was verified in 0.06000 seconds. It passed the benefit criterion
because its verified performance was 568.04443 times better than that of
the baseline plan.
2. The plan was automatically accepted.
Recommendation:
-----------------------------
Consider accepting the plan.
EXPLAIN PLANS SECTION
---------------------------------------------------------------------------
Baseline Plan
-----------------------------
Plan Id : 9390
Plan Hash Value : 2024077576
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1103 | 477 | 00:00:01 |
| * 1 | TABLE ACCESS FULL | EMPLOYEE | 1 | 1103 | 477 | 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("CODE"=999)
Note
-----
- dynamic sampling used for this statement
Test Plan
-----------------------------
Plan Id : 9391
Plan Hash Value : 3016226942
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1103 | 2 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | EMPLOYEE | 1 | 1103 | 2 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | INDX10 | 1 | | 1 | 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE"=999)
Note
-----
- dynamic sampling used for this statement
SQL>
SQL> SET AUTOTRACE OFF
=============================================
Now you can notice that for the plan "SQL_PLAN_cg5ga57zw1h0qb3c7f87e" "ACCEPTED" column shows "YES"
------------------------------------------------------------------------------------------------------
SQL> SELECT sql_handle,plan_name,sql_text,origin,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'SELECT * FROM EMPLOYEE %';
SQL_HANDLE PLAN_NAME SQL_TEXT ORIGIN ENA ACC FIX
-------------------- ------------------------------ ------------------------------------------------------------ -------------- --- --- ---
SQL_c795ea29ffc0c016 SQL_PLAN_cg5ga57zw1h0q78a4f908 SELECT * FROM EMPLOYEE WHERE code=999 ORDER BY code AUTO-CAPTURE YES YES NO
SQL_c795ea29ffc0c016 SQL_PLAN_cg5ga57zw1h0qb3c7f87e SELECT * FROM EMPLOYEE WHERE code=999 ORDER BY code AUTO-CAPTURE YES YES NO
SQL>
=============================================
Now Enable the trace & check the plan the query is using
----------------------------------------------------------
SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT * FROM EMPLOYEE WHERE code=999 ORDER BY code;
CODE DEPT ADDRESS
----- ---- --------
999 HR BANGALORE
Execution Plan
----------------------------------------------------------
Plan hash value: 1183808122
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1103 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEE | 1 | 1103 | 2 (0)| 00:00:01 | ==> Cost is Reduced
|* 2 | INDEX RANGE SCAN | INDX10 | 1 | | 1 (0)| 00:00:01 | ==> Index Scan Used
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CODE"=999)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL plan baseline "SQL_PLAN_cg5ga57zw1h0qb3c7f87e" used for this statement ===> Best Plan Used
- 1 Sql Plan Directive used for this statement
SQL>
SQL> SET AUTOTRACE OFF
=============================================
Comments
Post a Comment