上篇中，我们着重研究了SQL实际执行情况和Baseline不匹配情况下，shared pool变化情况。那么，从优化器执行的角度看，这个决策过程是怎么样的呢？我们从获得的10053跟踪文件谈起。
7、10053诊断事件诊断不匹配过程
我们检查10053事件生成的诊断文件，发现CBO在计算过程中的演化。
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T Alias: T
#Rows: 72744 #Blks: 1033 AvgRowLen: 97.00
Index Stats::
Index: IDX_T_OWNER Col#: 1
LVLS: 1 #LB: 293 #DK: 33 LB/K: 8.00 DB/K: 58.00 CLUF: 1915.00
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Column (#1):
NewDensity:0.000090, OldDensity:0.000007 BktCnt:5570, PopBktCnt:5565, PopValCnt:20, NDV:33
Table: T Alias: T
Card: Original: 72744.000000 Rounded: 13 Computed: 13.06 Non Adjusted: 13.06
Access Path: TableScan
Cost: 265.60 Resp: 265.60 Degree: 0
Cost_io: 265.00 Cost_cpu: 21908888
Resp_io: 265.00 Resp_cpu: 21908888
Access Path: index (AllEqRange)
Index: IDX_T_OWNER
resc_io: 2.00 resc_cpu: 23913
ix_sel: 0.000180 ix_sel_with_filters: 0.000180
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange
Index: IDX_T_OWNER
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 13.06 Bytes: 0
首先CBO站在纯Cost的基础上，做出了Index Range Scan的判断。但是，之后CBO发现了执行计划与Baseline不匹配的情况。
--首先给已经存在的SQL Plan Baseline中添加一个执行计划；
SPM: setup to add new plan to existing plan baseline, sig = 7398055453175447404, planId = 2139464760
SPM: planId's of plan baseline are: 2498539100
SPM: using qksan to reproduce, cost and select accepted plan, sig = 7398055453175447404
SPM: plan reproducibility round 0 (plan outline + session OFE)
--使用强制的执行计划，重新生成执行计划；
SPM: using qksan to reproduce accepted plan, planId = 2498539100
Registered qb: SEL$1 0x56a9c0 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=4 bjn=77122 hint_alias="T"@"SEL$1"
发现最优执行计划和现有的Baseline不匹配，SPM将最优的计划作为一个非Accepted的保存在数据字典里面。之后要求按照planid=2498539100（也就是FTS路径）重新评估。
下面，也的确是进行重新评估，最后出现FTS执行计划。
******************************************
----- Current SQL Statement for this session (sql_id=b3j6y3af0jahw) -----
/* SQL Analyze(39,0) */ select /*+ SPM DEMO*/* from t where wner='SCOTT'
*******************************************
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T Alias: T
#Rows: 72744 #Blks: 1033 AvgRowLen: 97.00
Index Stats::
Index: IDX_T_OWNER Col#: 1
LVLS: 1 #LB: 293 #DK: 33 LB/K: 8.00 DB/K: 58.00 CLUF: 1915.00
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Column (#1):
NewDensity:0.000090, OldDensity:0.000007 BktCnt:5570, PopBktCnt:5565, PopValCnt:20, NDV:33
Table: T Alias: T
Card: Original: 72744.000000 Rounded: 13 Computed: 13.06 Non Adjusted: 13.06
Access Path: TableScan
Cost: 265.60 Resp: 265.60 Degree: 0
Cost_io: 265.00 Cost_cpu: 21908888
Resp_io: 265.00 Resp_cpu: 21908888
Best:: AccessPath: TableScan
Cost: 265.60 Degree: 1 Resp: 265.60 Card: 13.06 Bytes: 0
***************************************
注意，Oracle是利用新旧统计量的不同，来强制影响执行计划的生成。
8、执行计划进化
现在我们有两个Baseline执行计划，一个是Accepted，另一个是非Accepted。我们尝试进行Baseline的进化过程。
SQL> set long 10000;
SQL> select dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_66ab2c3cf329ff6c',plan_name => null) from dual;
DBMS_SPM.EVOLVE_SQL_PLAN_BASEL
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_66ab2c3cf329ff6c
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SQL_PLAN_6datc7mtkmzvc7f85a438
------------------------------------
Plan was verified: Time used .05 seconds.
Plan passed performance criterion: 259.6 times better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
Rows Processed: 12 12
Elapsed Time(ms): 3.913 .031 126.23
CPU Time(ms): 3.888 0
Buffer Gets: 1038 4 259.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
所谓的进化过程，就是非Accepted状态执行计划和已经有的Baseline计划相比较，从CPU、IO多方面进行比较，判断结果。
此时，在baseline中包括多个Accepted状态执行计划。
SQL> select sql_handle, plan_name, ENABLED, ACCEPTED, FIXED, AUTOPURGE,executions from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED AUTOPURGE EXECUTIONS
------------------------------ ------------------------------ ------- -------- ----- --------- ----------
SYS_SQL_66ab2c3cf329ff6c SQL_PLAN_6datc7mtkmzvc7f85a438 YES YES NO YES 0
SYS_SQL_66ab2c3cf329ff6c SQL_PLAN_6datc7mtkmzvc94ecae5c YES YES NO YES 0
作为一个被接受的执行计划，是可以生效的。
SQL> explain plan for select /*+ SPM DEMO*/* from t where wner='SCOTT';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1516787156
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 1261 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 13 | 1261 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 13 | | 1 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
Note
-----
- SQL plan baseline "SQL_PLAN_6datc7mtkmzvc7f85a438" used for this statement
18 rows selected
在真正执行的时候，我们查看一下Oracle怎么处理。
SQL> alter session set events '10053 trace name context forever, level 12';
会话已更改。
SQL> select /*+ SPM DEMO*/* from t where wner='SCOTT';
SQL> alter session set events '10053 trace name context off';
会话已更改。
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
----------------------------------------------------------------------------
/u01/diag/rdbms/wilson/wilson/trace/wilson_ora_4496.trc
Trace文件中的关键细节。
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T Alias: T
#Rows: 72744 #Blks: 1033 AvgRowLen: 97.00
Index Stats::
Index: IDX_T_OWNER Col#: 1
LVLS: 1 #LB: 293 #DK: 33 LB/K: 8.00 DB/K: 58.00 CLUF: 1915.00
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Column (#1):
NewDensity:0.000090, OldDensity:0.000007 BktCnt:5570, PopBktCnt:5565, PopValCnt:20, NDV:33
Table: T Alias: T
Card: Original: 72744.000000 Rounded: 13 Computed: 13.06 Non Adjusted: 13.06
Access Path: TableScan
Cost: 265.60 Resp: 265.60 Degree: 0
Cost_io: 265.00 Cost_cpu: 21908888
Resp_io: 265.00 Resp_cpu: 21908888
Access Path: index (AllEqRange)
Index: IDX_T_OWNER
resc_io: 2.00 resc_cpu: 23913
ix_sel: 0.000180 ix_sel_with_filters: 0.000180
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange
Index: IDX_T_OWNER
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 13.06 Bytes: 0
***************************************
--结果匹配；
SPM: cost-based plan found in the plan baseline, planId = 2139464760
SPM: cost-based plan was successfully matched, planId = 2139464760
Starting SQL statement dump
user_id=0 user_name=SYS module=sqlplus.exe action=
sql_id=3kn82wdfqh9j3 plan_hash_value=1516787156 problem_type=3
----- Current SQL Statement for this session (sql_id=3kn82wdfqh9j3) -----
select /*+ SPM DEMO*/* from t where wner='SCOTT'
sql_text_length=50
sql=select /*+ SPM DEMO*/* from t where wner='SCOTT'
----- Explain Plan Dump -----
----- Plan Table -----
此时，shared pool情况如下：
SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+ SPM DEMO*/* from t%';
SQL_ID EXECUTIONS VERSION_COUNT
------------- ---------- -------------
3kn82wdfqh9j3 1 1
SQL> select sql_id, child_number, sql_plan_baseline,executions from v$sql where sql_text like 'select /*+ SPM DEMO*/* from t%';
SQL_ID CHILD_NUMBER SQL_PLAN_BASELINE EXECUTIONS
------------- ------------ ------------------------------ ----------
3kn82wdfqh9j3 0 SQL_PLAN_6datc7mtkmzvc7f85a438 1