①创建表t3：
SQL> create table t3 (id int);
Table created.
SQL> insert into t3 select level from dual connect by level<=100000;
100000 rows created.
②开启自动捕获并修改时间格式：
SQL> alter system set optimizer_capture_sql_plan_baselines=true;
System altered.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
③查询sql
SQL> select count(*) from t1 where id=1;
COUNT(*)
----------
2
SQL> select count(*) from t1 where id=1;
COUNT(*)
----------
2
SQL> select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like '%select count(*) from t1 where id=1%';
SQL_HANDLE
------------------------------
SQL_TEXT
------------------------------------------------------------------------------
PLAN_NAME ORIGIN
------------------------------ --------------
VERSION
----------------------------------------------------------------
CREATED
---------------------------------------------------------------------------
LAST_MODIFIED
---------------------------------------------------------------------------
LAST_EXECUTED
---------------------------------------------------------------------------
LAST_VERIFIED
---------------------------------------------------------------------------
ENA ACC FIX
--- --- ---
SQL_c0dca3d9bf76dcbd
select count(*) from t1 where id=1
SQL_PLAN_c1r53v6zrdr5x616acf47 AUTO-CAPTURE
11.2.0.4.0
17-OCT-16 02.56.20.000000 PM
17-OCT-16 02.56.20.000000 PM
17-OCT-16 02.56.20.000000 PM
YES YES NO
④创建索引：
SQL> create index idx_t1 on t1(id);
Index created.
⑤再次执行相同的sql语句：
SQL> select count(*) from t1 where id=1;
COUNT(*)
----------
2
SQL> select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like '%select count(*) from t1 where id=1%';
SQL_HANDLE
------------------------------
SQL_TEXT
------------------------------------------------------------------------------
PLAN_NAME ORIGIN
------------------------------ --------------
VERSION
----------------------------------------------------------------
CREATED
---------------------------------------------------------------------------
LAST_MODIFIED
---------------------------------------------------------------------------
LAST_EXECUTED
---------------------------------------------------------------------------
LAST_VERIFIED
---------------------------------------------------------------------------
ENA ACC FIX
--- --- ---
SQL_c0dca3d9bf76dcbd
select count(*) from t1 where id=1
SQL_PLAN_c1r53v6zrdr5x616acf47 AUTO-CAPTURE
11.2.0.4.0
17-OCT-16 02.56.20.000000 PM
17-OCT-16 02.56.20.000000 PM
17-OCT-16 02.56.20.000000 PM
YES YES NO
SQL_c0dca3d9bf76dcbd
select count(*) from t1 where id=1
SQL_PLAN_c1r53v6zrdr5xa9a6a0a8 AUTO-CAPTURE
11.2.0.4.0
17-OCT-16 02.59.07.000000 PM
17-OCT-16 02.59.07.000000 PM
YES NO NO
⑥演进执行计划：
SQL> SET SERVEROUTPUT ON
SQL> SET LONG 10000
SQL> declare
2 report clob;
3 begin
4 report :=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
5 sql_handle => 'SQL_c0dca3d9bf76dcbd');
6 DBMS_OUTPUT.PUT_LINE(report);
7 END;
8 /
-----------------------------------------------------------------------------
--
Evolve SQL Plan Baseline
Report
-----------------------------------------------------------------------
--------
Inputs:
-------
SQL_HANDLE = SQL_c0dca3d9bf76dcbd
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan:
SQL_PLAN_c1r53v6zrdr5xa9a6a0a8
------------------------------------
Plan was
verified: Time used .09 seconds.
Plan passed performance criterion: 153.86
times better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status:
COMPLETE COMPLETE
Rows Processed: 1
1
Elapsed Time(ms): 4.149 .046 90.2
CPU Time(ms): 4.221 .111 38.03
Buffer Gets: 309 2 154.5
Physical Read Requests: 0 0
Physical Write
Requests: 0 0
Physical Read Bytes:
0 0
Physical Write Bytes: 0 0
Executions: 1
1
---------------------------------------------------------------------------
----
Report
Summary
----------------------------------------------------------------------
---------
Number of plans verified: 1
Number of plans accepted: 1
PL/SQL procedure successfully completed.
⑦再次查看：
SQL> select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like '%select count(*) from t1 where id=1';
SQL_HANDLE
------------------------------
SQL_TEXT
------------------------------------------------------------------------------
PLAN_NAME ORIGIN
------------------------------ --------------
VERSION
----------------------------------------------------------------
CREATED
---------------------------------------------------------------------------
LAST_MODIFIED
---------------------------------------------------------------------------
LAST_EXECUTED
---------------------------------------------------------------------------
LAST_VERIFIED
---------------------------------------------------------------------------
ENA ACC FIX
--- --- ---
SQL_c0dca3d9bf76dcbd
select count(*) from t1 where id=1
SQL_PLAN_c1r53v6zrdr5x616acf47 AUTO-CAPTURE
11.2.0.4.0
17-OCT-16 02.56.20.000000 PM
17-OCT-16 02.56.20.000000 PM
17-OCT-16 02.56.20.000000 PM
YES YES NO
SQL_c0dca3d9bf76dcbd
select count(*) from t1 where id=1
SQL_PLAN_c1r53v6zrdr5xa9a6a0a8 AUTO-CAPTURE
11.2.0.4.0
17-OCT-16 02.59.07.000000 PM
17-OCT-16 03.02.17.000000 PM
17-OCT-16 03.02.17.000000 PM
YES YES NO
⑧查看现在查询所用的执行计划：
SQL> select count(*) from t1 where id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1970818898
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX RANGE SCAN| IDX_T1 | 2 | 26 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_c1r53v6zrdr5xa9a6a0a8" used for this statemen
t
Statistics
----------------------------------------------------------
29 recursive calls
15 db block gets
94 consistent gets
0 physical reads
3000 redo size
526 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed