【测试】模拟一个全表扫描的sql，对其进行优化走索引，并且将执行计划稳定到baseLine。

①创建表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