上篇中，我們著重研究了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

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 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

SQL Plan Baseline與Shared Cursor機制研究（三）

相關文章