【11g新特性】SPM SQL PLAN MANAGEMENT执行计划管理流程图

SQL> create table mac_spm tablespace users as select * from dba_objects;

Table created.

SQL> analyze table mac_spm compute statistics;

Table analyzed.

SQL>    alter session set optimizer_capture_sql_plan_baselines=true;

Session altered.

SQL>
SQL>    select sum(object_id) from mac_spm;

SUM(OBJECT_ID)
--------------
    2911455720

SQL>    select sum(object_id) from mac_spm;

SUM(OBJECT_ID)
--------------
    2911455720

	alter session set optimizer_capture_sql_plan_baselines=false;

	SQL> alter system flush shared_pool;

System altered.

SQL> set autotrace on;
SQL>  select sum(object_id) from mac_spm;

SUM(OBJECT_ID)
--------------
    2911455720

Execution Plan
----------------------------------------------------------
Plan hash value: 874020942

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     4 |   301   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |         |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| MAC_SPM | 75535 |   295K|   301   (1)| 00:00:04 |
------------------------------------------------------------------------------

Note
-----
   - SQL plan baseline "SQL_PLAN_cjd95ftv8ct90eca706bd" used for this statement

Statistics
----------------------------------------------------------
        240  recursive calls
          0  db block gets
       1289  consistent gets
          0  physical reads
          0  redo size
        536  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         25  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> create index ind_objd on mac_spm(object_id);

Index created.

oradebug setmypid
oradebug tracefile_name

SQL> alter system flush shared_pool;

System altered.

alter system set events 'trace[SQL_Plan_Management] disk highest';

select sum(object_id) from mac_spm;

*** 2013-04-11 09:28:49.628
SPM: statement found in SMB
SPM: planId's of plan baseline are: 3970369213
SPM: using qksan to reproduce, cost and select accepted plan, sig = 14462506969095103776
SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 3970369213
SPM: planId in plan baseline = 3970369213, planId of reproduced plan = 3970369213
SPM: best cost so far = 301.13, current accepted plan cost = 301.13
SPM: re-parse to use selected accepted plan, planId = 3970369213
SPM: statement found in SMB
SPM: re-parsing to generate selected accepted plan,  planId = 3970369213
SPM: kkopmCheckSmbUpdate (enter) xscP=0x7f8e30491298, pmExCtx=0xc7463800, ciP=0xc99f7e78, dtCtx=0xbaf7c20

SQL> select  /*+ index( MAC_SPM ind_objd) */ sum(object_id) from mac_spm;

SUM(OBJECT_ID)
--------------
    2911455720

Execution Plan
----------------------------------------------------------
Plan hash value: 45369511

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     4 |   169   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE  |          |     1 |     4 |            |          |
|   2 |   INDEX FULL SCAN| IND_OBJD | 75535 |   295K|   169   (1)| 00:00:03 |
-----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        168  consistent gets
        168  physical reads
          0  redo size
        536  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
SQL> insert into mac_spm select * from mac_spm;

75535 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'MAC_SPM');

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.

SQL> oradebug setmypid
Statement processed.
SQL> select sum(object_id) from mac_spm;

SUM(OBJECT_ID)
--------------
    5822911440

SQL> oradebug tracefile_name
/s01/diag/rdbms/prodb/PRODB/trace/PRODB_ora_11126.trc

*** 2013-04-11 09:45:24.248
SPM: statement found in SMB
SPM: planId's of plan baseline are: 3970369213
SPM: using qksan to reproduce, cost and select accepted plan, sig = 14462506969095103776
SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 3970369213
SPM: planId in plan baseline = 3970369213, planId of reproduced plan = 3970369213
SPM: best cost so far = 623.28, current accepted plan cost = 623.28
SPM: re-parse to use selected accepted plan, planId = 3970369213
SPM: statement found in SMB
SPM: re-parsing to generate selected accepted plan,  planId = 3970369213
SPM: kkopmCheckSmbUpdate (enter) xscP=0x7f6066c949f8, pmExCtx=0xc8876e48, ciP=0xc86784c0, dtCtx=0xbaf7c20

        qksan是负责SQL ANALYZE 的函数，如果看到 类似如下的信息 则说明 存在过 SQL ANALYZE sql=/* SQL Analyze(168,0) */ select sum(object_id) from mac_spm End parsing of cur#=4 sqlid=72ph25kpkkqhs Semantic Analysis cur#=4 sqlid=72ph25kpkkqhs

本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277758