在上篇（http://space.itpub.net/17203031/viewspace-755198）中，我們介紹了自動捕獲SQL Plan Baseline的初步過程。當存在一個baseline的時候，SQL CBO生成的執行計劃又和Baseline相匹配，在shared pool中會建立父子共享遊標。共享執行計劃中被標註上Plan Baseline。

5、使用10053跟蹤匹配過程

今天我們使用10053這個執行計劃跟蹤過程，看看剛才那個過程是怎麼樣進行決策的。首先需要清理shared pool。

SQL> alter system flush shared_pool;

System altered

SQL> alter system flush buffer_cache;

System altered

--檢視Trace File資訊

SQL> select value from v$diag_info where name='Default Trace File';

VALUE

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

/u01/diag/rdbms/wilson/wilson/trace/wilson_ora_3651.trc

啟動10053監控。

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';

會話已更改。

10053 Trace File資訊關鍵部分如下：

****************

QUERY BLOCK TEXT

****************

select /*+ SPM DEMO*/* from t where wner='SCOTT' –處理SQL，變換之後

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

QUERY BLOCK SIGNATURE

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

signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0

fro(0): flg=0 bjn=77122 hint_alias="T"@"SEL$1"

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

SYSTEM STATISTICS INFORMATION（系統統計量資訊，參與CPU成本計算公式）

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

Using NOWORKLOAD Stats

CPUSPEEDNW: 2722 millions instructions/sec (default is 100)

IOTFRSPEED: 4096 bytes per millisecond (default is 4096)

IOSEEKTIM: 12 milliseconds (default is 10)

MBRC: -1 blocks (default is 8)

***************************************

BASE STATISTICAL INFORMATION（物件統計量，參與CPU成本計算公式）

***********************

Table Stats::

Table: T Alias: T

#Rows: 12 #Blks: 1 AvgRowLen: 78.00

Index Stats::

Index: IDX_T_OWNER Col#: 1

LVLS: 0 #LB: 1 #DK: 1 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00

Access path analysis for T

***************************************

SINGLE TABLE ACCESS PATH

Single Table Cardinality Estimation for T[T]

Table: T Alias: T

Card: Original: 12.000000 Rounded: 12 Computed: 12.00 Non Adjusted: 12.00

Access Path: TableScan

Cost: 2.00 Resp: 2.00 Degree: 0

Cost_io: 2.00 Cost_cpu: 12881

Resp_io: 2.00 Resp_cpu: 12881

Access Path: index (AllEqRange)

Index: IDX_T_OWNER

resc_io: 2.00 resc_cpu: 21803

ix_sel: 1.000000 ix_sel_with_filters: 1.000000

Cost: 2.00 Resp: 2.00 Degree: 1

Best:: AccessPath: TableScan （最終決策，走FTS路徑）

Cost: 2.00 Degree: 1 Resp: 2.00 Card: 12.00 Bytes: 0

***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS

***************************************

GENERAL PLANS

***************************************

Considering cardinality-based initial join order.

Permutations for Starting Table :0

Join order[1]: T[T]#0

***********************

Best so far: Table#: 0 cost: 2.0004 card: 12.0000 bytes: 936

***********************

(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000

*********************************

Number of join permutations tried: 1

*********************************

Trying or-Expansion on query block SEL$1 (#0)

Transfer Optimizer annotations for query block SEL$1 (#0)

id=0 frofand predicate="T"."OWNER"='SCOTT'

Final cost for query block SEL$1 (#0) - All Rows Plan:

Best join order: 1

Cost: 2.0004 Degree: 1 Card: 12.0000 Bytes: 936

Resc: 2.0004 Resc_io: 2.0000 Resc_cpu: 12881

Resp: 2.0004 Resp_io: 2.0000 Resc_cpu: 12881

kkoqbc-subheap (delete addr=0x8566b0, in-use=11048, alloc=14176)

kkoqbc-end:

:

call(in-use=16448, alloc=32736), compile(in-use=55872, alloc=59324), execution(in-use=1468, alloc=4060)

kkoqbc: finish optimizing query block SEL$1 (#0)

apadrv-end

:

call(in-use=16448, alloc=32736), compile(in-use=56468, alloc=59324), execution(in-use=1468, alloc=4060)

--Oracle發現竟然有SPM Baseline，並且相匹配

SPM: cost-based plan found in the plan baseline, planId = 2498539100

SPM: cost-based plan was successfully matched, planId = 2498539100

Starting SQL statement dump

user_id=0 user_name=SYS module=sqlplus.exe action=

sql_id=3kn82wdfqh9j3 plan_hash_value=1601196873 problem_type=3

從10053事件診斷過程中，我們可以發現Oracle在出現Baseline的時候，並不是武斷的去按照Baseline的方法去做。Oracle首先使用CBO生成執行計劃，之後拿到SPM baseline中進行匹配。如果匹配上了，也就是CBO生成的執行計劃和baseline相匹配，Oracle直接就使用這個執行計劃了。並且這個過程中會在shared pool中生成shared cursor資訊。

如果沒有匹配上，也就是說baseline不是最優執行計劃，那麼怎麼辦？

6、資料修改策略

我們可以修改SQL底層的資料，讓FTS並不是最優執行計劃。

SQL> insert into t select * from dba_objects where owner<>'SCOTT';

72732 rows inserted

SQL> commit;

Commit complete

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

在原有的12條記錄基礎上，新增了7萬多條記錄。那麼SQL應該是Index Range Scan是最好執行計劃。

SQL> explain plan for select * 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')

14 rows selected

執行SQL，同時生成對應的10053 Trace File。生成的Trace File我們之後分析。

--啟動10053事件

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_3781.trc

注意，此時監控記憶體中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

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

我們知道，如果是從CBO單純的過程看，FTS（也就是baseline規定的執行計劃）不是最優的執行計劃，而是Index Range Scan。但是，SQL執行了，沒有在shared pool中形成任何共享遊標。這是不正常的，在之後的實驗中，我們證明是由於10053造成的執行計劃不在shared pool中出現。

此時，SPM Baseline狀態如下：

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 NO NO YES 0

SYS_SQL_66ab2c3cf329ff6c SQL_PLAN_6datc7mtkmzvc94ecae5c YES YES NO YES 0

我們發現在SPM中增加了一個baseline plan，但是和之前的區別是Accepted狀態為NO。

我們首先檢視一下，剛剛的那個SQL執行計劃是什麼。

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: 1601196873

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

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

| 0 | SELECT STATEMENT | | 13 | 1261 | 266 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T | 13 | 1261 | 266 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - filter("OWNER"='SCOTT')

Note

-----

- SQL plan baseline "SQL_PLAN_6datc7mtkmzvc94ecae5c" used for this statement

17 rows selected

SQL還是按照了Accepted的Baseline計劃來執行的SQL，此時SQL Plan編號是：SQL_PLAN_6datc7mtkmzvc94ecae5c。那麼，生成的那個非Accepted的執行計劃基線是什麼呢？

SQL> set pagesize 10000;

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SYS_SQL_66ab2c3cf329ff6c',plan_name => 'SQL_PLAN_6datc7mtkmzvc7f85a438',format => 'advanced'));

PLAN_TABLE_OUTPUT

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

SQL handle: SYS_SQL_66ab2c3cf329ff6c

SQL text: select /*+ SPM DEMO*/* from t where wner='SCOTT'

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

Plan name: SQL_PLAN_6datc7mtkmzvc7f85a438 Plan id: 2139464760

Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE

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

Outline Data from SMB:

/*+

BEGIN_OUTLINE_DATA

INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))

OUTLINE_LEAF(@"SEL$1")

ALL_ROWS

DB_VERSION('11.2.0.1')

OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

IGNORE_OPTIM_EMBEDDED_HINTS

END_OUTLINE_DATA

*/

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

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)|

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1 / T@SEL$1

2 - SEL$1 / T@SEL$1

（篇幅原因，省略部分……）

72 rows selected

在Baseline執行規則上看，我們才到了這樣的過程：一個SQL出現之後，Oracle CBO的確會重新進行評估，成本公式告訴Oracle說Index Range Scan是最優的執行計劃。但是之後，發現SPM中有一個強制的Baseline，裡面只有一個Accepted的執行計劃。根據SPM的工作原理，新生成的執行計劃如果成本值低於Baseline中Accepted的執行計劃，這個執行計劃也不會在這次操作中執行。而是新增為一個非Accepted的Plan。這次的執行還是會按照Baseline中Accepted的計劃去執行。

同時，此時Shared Pool中沒有對應的共享遊標資訊。下面，我們透過簡單的實驗，來證明其本質。

注意：由於是在其他環境上進行相同實驗，sql_id和plan name可能有所不同。

此時資料庫中包括FTS的baseline。

SQL> select sql_handle, plan_name, accepted from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME ACCEPTED

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

SYS_SQL_f82ffff93a977037 SQL_PLAN_ghbzzz4x9fw1r94ecae5c YES

SQL> alter system flush shared_pool;

System altered

SQL> alter system flush buffer_cache;

System altered

首先我們使用10053監控。

SQL> alter session set events '10053 trace name context forever, level 12';

會話已更改。

SQL> select /*+ DEMP */* from t where wner='SCOTT';

（篇幅原因，有省略.....）

SQL> alter session set events '10053 trace name context off';

會話已更改。

資料shared pool中環境：

SQL> select sql_id, child_number, plan_hash_value, SQL_PLAN_BASELINE, executions from v$sql where sql_text like 'select /*+ DEMP */* from t%';

SQL_ID CHILD_NUMBER PLAN_HASH_VALUE SQL_PLAN_BASELINE EXECUTIONS

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

在非跟蹤模式下：

SQL> select /*+ DEMP */* from t where wner='SCOTT';

（結果集合省略......）

SQL> select sql_id, child_number, plan_hash_value, SQL_PLAN_BASELINE, executions from v$sql where sql_text like 'select /*+ DEMP */* from t%';

SQL_ID CHILD_NUMBER PLAN_HASH_VALUE SQL_PLAN_BASELINE EXECUTIONS

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

cz2dt520pdh97 1 1601196873 SQL_PLAN_ghbzzz4x9fw1r94ecae5c 1

在正常情況下，我們可以看到不匹配的過程：如果執行過程中開啟10053事件跟蹤，shared cursor可能就不會生成。標準的方式是：如果一個新的執行計劃被加入為非Accept型別Baseline Plan，真正執行的時候選擇的依然是確認的Baseline，生成的是確認的Baseline執行計劃。

那麼，我們看到的是SPM在不匹配的時候的工作結果，那麼在Oracle解析過程中，發生了什麼呢？我們可以從剛剛生成的那個Trace檔案中看到結果。我們在下篇中繼續分析：

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

相關文章