對Oracle而言，基於成本最佳化器CBO工作的基礎是系統、物件統計量和CPU成本計算公式。而大多數情況下，收集統計量是一個非同步單獨的過程。無論是在9i，還是目前主流11g，發揮CBO作用都需要我們有一個統計量收集或者分析的過程。

在9i時代，CBO和RBO是交替工作的。Oracle是不會“主動”的將物件統計量進行收集（那個時期也成為分析analyze）。系統調優人員也大都是解決統計量缺失、CBO最佳化器缺陷問題，目前能看到的很多hint也大都在9i時期編寫系統。

到了10g之後，兩個情況讓CBO徹底取代RBO。一個是CBO成為Oracle預設的最佳化器使用型別，SQL語句預設情況下就使用CBO進行解析。另一個是自動統計量夜間收集作業的推出，Oracle可以自動找時間進行統計量收集。應該說，大部分的SQL語句從10g之後都是使用CBO進行執行計劃生成。

但是，無論如何，都存在統計量不及時的情況。一個新建立的資料表或者發生大量變化的資料集合，如果沒有手工的資料收集，CBO總是工作在統計量缺失或者陳舊統計量的情況下。

1、從Dynamic Sampling談起

為了應對統計量缺失的情況，Oracle推出了Dynamic Sampling（12c之後稱為Dynamic Statistic）動態取樣收集的技術策略。簡單點說，如果資料表等物件沒有統計量存在，Oracle有需要統計量生成執行計劃，資料庫會進行一次臨時性的資料收集動作。根據不同的採用比例，Oracle實時的採集部分資料塊，應用查詢條件進行小規模試算。最後根據統計規則，將結果集合放大後，作為結果集合統計量和row source納入到CBO體系裡面。

從SQL執行的角度看，Dynamic Sampling給Oracle使用者帶來兩個方面好處：

ü 關聯條件SQL語句精準估算。對於常見的統計量，如選擇率、資料分佈，都是以單列為中心進行計數的。如果SQL語句中對應的是兩個相關聯的where條件，那麼單列統計量計算出來的結果集合往往是偏小。如果採用Dynamic Sampling，採用動作是實時進行計算的，可以消除關聯where條件影響；

ü CBO普遍應用。應該說，沒有統計量，CBO是沒有任何工作的可能的！Dynamic Sampling雖然不能給Oracle提供出最精確的統計量，但是起碼可以讓CBO執行提供基本條件；

從不利的方面看，Dynamic Sampling也帶來一些問題。比如，和駐留在資料字典中的統計量不同，Dynamic Sampling並不會將收集採用值儲存在資料字典裡面，而是一次SQL就進行一次收集動作。取樣比例如果高，就意味著parse前的動態收集動作消耗資源高。如果取樣比例低，效能雖然可以好一些，但是存在著“低效”執行計劃生成的紀律。

這其實是一個矛盾。如果資料表比較小，取樣比例小一些，執行計劃效率低一些其實也沒有過多問題。但是如果資料表很大，SQL語句屬於關鍵SQL，這樣低效的執行計劃其實就意味著風險。

Oracle中，是透過optimizer_dynamic_sampling設定取樣比例。11gR2中，預設為2。

SQL> show parameter dyn

NAME TYPE VALUE

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

optimizer_dynamic_sampling integer 2

為了應對由於動態取樣帶來的問題，在11g裡面針對特殊的資料物件和SQL場景，Oracle CBO採取了自動調節取樣率的最佳化策略。

下面我們進行相應的實驗。

2、環境介紹

我們使用Oracle 11gR2版本進行測試，預設的取樣控制引數是2。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL> show parameter dyn

NAME TYPE VALUE

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

optimizer_dynamic_sampling integer 2

建立一個大資料表，體積為2G。

SQL> select tablespace_name, bytes/1024/1024/1024 from dba_segments where owner='SYS' and segment_name='T';

TABLESPACE_NAME BYTES/1024/1024/1024

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

TESTTBL 2.099609375

Owner列上面建立索引IDX_T_OWNER。

3、自動sampling adjusted

當前採用取值為2，由於資料表比較大，採用並行查詢策略。

SQL> explain plan for select /*+ parallel(t, 2) */count(*) from t;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 3126468333

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

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

| 0 | SELECT STATEMENT | | 1 | 41414 (1)| 00:08:17 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | PX COORDINATOR | | | | |

| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,

| 4 | SORT AGGREGATE | | 1 | | | Q1,

| 5 | PX BLOCK ITERATOR | | 23M| 41414 (1)| 00:08:17 | Q1,

| 6 | TABLE ACCESS FULL| T | 23M| 41414 (1)| 00:08:17 | Q1,

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

Note

-----

- dynamic sampling used for this statement (level=5)

17 rows selected

新建立的資料表，沒有顯示的進行資料收集動作。所以Oracle會去選擇動態取樣Dynamic Sampling策略。在使用parallel和動態取樣的SQL語句中，我們使用explain plan生成了執行計劃，但是在末尾的note中，發現了不確定的部分。

當前dynamic sampling設定引數為2，但是在執行計劃上顯示的是5。取值越高，意味著更高的取樣比例，進而意味著更準確地執行計劃生成。

從其他的場景，比如非並行開啟或者小資料表查詢，我們都沒有看到這樣的現象。說明：這個是針對Oracle特殊SQL場景下的一種最佳化措施。研究CBO行為最好的方式是使用10053等待事件進行跟蹤處理。下面利用這種方法進行檢查。

4、10053事件跟蹤

我們在sqlplus中呼叫跟蹤事件。跟蹤檔名稱如下。

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

VALUE

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

/u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_5602.trc

SQL> show parameter dyn

NAME TYPE VALUE

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

optimizer_dynamic_sampling integer 2

啟用跟蹤過程。

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

會話已更改。

SQL> select /*+ parallel(t, 2) */count(*) from t;

COUNT(*)

----------

19360512

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

會話已更改。

從跟蹤檔案中，我們找到了CBO和Dynamic Sampling工作的片段內容。首先，我們發現了動態取樣動作調整。

Registered qb: SEL$1 0x783a28 (PARSER)

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

QUERY BLOCK SIGNATURE

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

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

fro(0): flg=4 objn=78697 hint_alias="T"@"SEL$1"

SPM: statement not found in SMB

Dynamic sampling level auto-adjusted from 5 to 5

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

Automatic degree of parallelism (ADOP)

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

Automatic degree of parallelism is disabled: Parameter.

在跟蹤檔案中出現了auto-adjusted的調節語句動作。但是還有一個疑惑，就是原有的取樣level是2，如果發生調節也應該是“from 2 to 5”。但是在語句裡面，我們看到的是“from 5 to 5”。這個問題等後面介紹。

不存在統計量的資訊列舉。

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

BASE STATISTICAL INFORMATION

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

Table Stats::

Table: T Alias: T (NOT ANALYZED)

#Rows: 22418465 #Blks: 274467 AvgRowLen: 100.00 ChainCnt: 0.00

Index Stats::

Index: IDX_T_OWNER Col#: 1 (NOT ANALYZED)

LVLS: 1 #LB: 25 #DK: 100 LB/K: 1.00 DB/K: 1.00 CLUF: 800.00

Access path analysis for T

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

SINGLE TABLE ACCESS PATH

Single Table Cardinality Estimation for T[T]

啟動取樣SQL執行。

*** 2014-02-17 10:38:49.540

** Performing dynamic sampling initial checks. **

** Dynamic sampling initial checks returning TRUE (level = 5). –啟用level=5的策略

** Dynamic sampling updated index stats.: IDX_T_OWNER, blocks=45795

** Dynamic sampling updated table stats.: blocks=274467

*** 2014-02-17 10:38:49.555

** Generated dynamic sampling query:

query text :

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, 1 AS C2 FROM "SYS"."T" SAMPLE BLOCK (0.022954 , 1) SEED (1) "T") SAMPLESUB

取樣操作結果如下：

*** 2014-02-17 10:38:49.585

** Executed dynamic sampling query:

level : 5

sample pct. : 0.022954

actual sample size : 5398

filtered sample card. : 5398

orig. card. : 22418465

block cnt. table stat. : 274467

block cnt. for sampling: 274467

max. sample block cnt. : 64

sample block cnt. : 63

min. sel. est. : -1.00000000

** Using dynamic sampling card. : 23517030 –估算計算後的值

補全最後的取樣統計量。

** Dynamic sampling updated table card.

Table: T Alias: T

Card: Original: 23517029.619048 Rounded: 23517030 Computed: 23517029.62 Non Adjusted: 23517029.62

Access Path: TableScan

Cost: 74544.99 Resp: 41413.88 Degree: 0

Cost_io: 74337.00 Cost_cpu: 3527554500

Resp_io: 41298.33 Resp_cpu: 1959752500

Best:: AccessPath: TableScan

Cost: 41413.88 Degree: 2 Resp: 41413.88 Card: 23517029.62 Bytes: 0

最後生成的執行計劃：

============

Plan Table

============

------------------------------------------+-----------------------------------+-------------------------+

------------------------------------------+-----------------------------------+-------------------------+

| 0 | SELECT STATEMENT | | | | 40K | | | | |

| 1 | SORT AGGREGATE | | 1 | | | | | | |

| 2 | PX COORDINATOR | | | | | | | | |

| 3 | PX SEND QC (RANDOM) | :TQ10000| 1 | | | |:Q1000| P->S |QC (RANDOM)|

| 4 | SORT AGGREGATE | | 1 | | | |:Q1000| PCWP | |

| 5 | PX BLOCK ITERATOR | | 22M | | 40K | 00:08:17 |:Q1000| PCWC | |

| 6 | TABLE ACCESS FULL | T | 22M | | 40K | 00:08:17 |:Q1000| PCWP | |

------------------------------------------+-----------------------------------+-------------------------+

Predicate Information:

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

6 - access(:Z>=:Z AND :Z<=:Z)

Content of other_xml column

===========================

db_version : 11.2.0.3

parse_schema : SYS

dynamic_sampling: 5

plan_hash : 3126468333

plan_hash_2 : 1826541471

Outline Data:

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

OPT_PARAM('optimizer_dynamic_sampling' 5)

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

FULL(@"SEL$1" "T"@"SEL$1")

END_OUTLINE_DATA

*/

這個過程，確實證明了Oracle在這個SQL中採用了調節dynamic sample比例的過程，將原來的預設2變為5。從而形成更高的取樣比例。

根據目前的資料：Oracle從11g開始應用一種自動調節的動態收集機制。如果一個SQL對應資料表很大，而且應該用並行策略，同時有沒有統計量。這個時候，Oracle會自己調節取樣比例，到一個比較大的取值。

至於說trace檔案中那個詭異的“from 5 to 5”，在MOS中有對應的記錄，這個是一個未公佈的bug，編號為：452863 ER: USE DYNAMIC SAMPLING FOR KEY CHOICES WHEN ROW ESTIMATES ARE DUBIOUS。但是，調節動作是存在的。

5、結論

透過上面的實驗，我們可以瞭解Oracle在11g之後對CBO動態取樣機制的一種補充最佳化。應該說這樣的策略是合理的。因為在特別的場景下，特別是並行，我們是比較關注的SQL才會選擇應用的。Oracle選擇這樣的激發條件，也是有所依據的。

Oracle 11g中dynamic sampling自動調節（auto-adjusted）機制

相關文章