使用10053事件跟蹤CBO優化器決策（下）

 

上篇中，我們主要介紹了10053事件的作用和跟蹤檔案生成規則。本篇中，我們將使用上篇生成的跟蹤檔案ots_ora_2968.trc，從結構上分析CBO在選擇一個執行計劃的步驟和方法。

 

6、Trace File結果綜析

 

Trace檔案是Oracle預留的用於對系統內部行為進行監控的日誌性質檔案。10053的跟蹤檔案具體可以分為如下組成部分：

 

ü        縮略語資訊說明

 

在Oracle中，有很多內部使用的縮略語。掌握這些習慣性縮寫，可以大大提高我們對Internal理解的能力。在Trace檔案中，很多都是包括這些自解釋的縮略語資訊提示。

 

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

Legend

The following abbreviations are used by optimizer trace.

CBQT - cost-based query transformation

JPPD - join predicate push-down

FPD - filter push-down

PM - predicate move-around

CVM - complex view merging

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

SLAVETHR - average slave I/O throughput

dmeth - distribution method

  1: no partitioning required

  2: value partitioned

  4: right is random (round-robin)

  512: left is random (round-robin)

  8: broadcast right and partition left

  16: broadcast left and partition right

  32: partition left using partitioning of right

  64: partition right using partitioning of left

  128: use hash partitioning dimension

  256: use range partitioning dimension

  2048: use list partitioning dimension

  1024: run the join in serial

  0: invalid distribution method

sel - selectivity

ptn – partition

 

 

這些縮略語都會在跟蹤檔案中出現。

 

ü        系統環境基本資訊

 

如同標籤一樣，在Trace檔案都包括一個trace檔案頭。其中包括了當前系統的基本資訊和環境資訊。如下：

 

 

Dump file c:\tool\oracle\oracle\product\10.2.0\admin\ots\udump\ots_ora_2968.trc

Tue Aug 30 10:29:57 2011

ORACLE V10.2.0.1.0 - Production vsnsta=0

vsnsql=14 vsnxtr=3

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Windows Server 2003 Version V5.2 Service Pack 1

CPU                 : 1 - type 586, 1 Physical Cores

Process Affinity    : 0x00000000

Memory (Avail/Total): Ph:133M/1015M, Ph+PgF:1472M/2453M, VA:1460M/2047M

Instance name: ots

 

Redo thread mounted by this instance: 1

Oracle process number: 22

 

Windows thread id: 2968, image: ORACLE.EXE (SHAD)

 

 

上面的資訊主要表示的資料庫例項的基本資訊，包括記憶體、CPU和一些OS的版本資訊和例項名稱資訊。Oracle DB的行為，與當前例項所在的軟硬體環境是密切相關的，所以在跟蹤檔案的檔案頭，首先列出了例項基本資訊。

 

 

 

ü        Query Block基本資訊

 

在例項基本資訊之後，就是關於進行跟蹤SQL的基本資訊情況。

 

 

*** 2011-08-30 10:29:57.703

*** ACTION NAME:(Command Window - New) 2011-08-30 10:29:57.656

*** MODULE NAME:(PL/SQL Developer) 2011-08-30 10:29:57.656

*** SERVICE NAME:(OTS) 2011-08-30 10:29:57.656

*** SESSION ID:(142.14) 2011-08-30 10:29:57.656

Registered qb: SEL$1 0x796af14 (PARSER)

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

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

 

 

其中的Action Name、Model Name、Service name以及Session ID分別表示使用跟蹤會話SQL的背景資訊。從中我們可以看到該SQL是使用PL/SQL Developer的Command Window功能，連線的Service Name是OTS。當前會話編號是142.14。

 

另一部分是對SQL查詢本身的一個解析，qb（Query Block）對應的SEL$1的解析編號為0x796af14。Objn表示進行查詢操作的資料物件編號。

 

 

ü        SQL改寫Transformation過程

 

對一般成熟的DBMS Query Optimizer而言，都會在parse前試圖對輸入SQL進行改寫（Query Transformation）。這個過程的目的是讓SQL語句更容易生成高效的執行計劃。常見的改寫手段有：謂詞推進（Predicate Move-Around）、檢視合併（View Merging）和子查詢巢狀展開（Subquery Unnesting）等。

 

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

Predicate Move-Around (PM)

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

PM: Considering predicate move-around in SEL$1 (#0).

PM:   Checking validity of predicate move-around in SEL$1 (#0).

CBQT: Validity checks failed for an041xjjnbr8q.

CVM: Considering view merge in query block SEL$1 (#0)

CBQT: Validity checks failed for an041xjjnbr8q.

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

Subquery Unnest

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

SU: Considering subquery unnesting in query block SEL$1 (#0)

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

Set-Join Conversion (SJC)

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

SJC: Considering set-join conversion in SEL$1 (#0).

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

Predicate Move-Around (PM)

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

PM: Considering predicate move-around in SEL$1 (#0).

PM:   Checking validity of predicate move-around in SEL$1 (#0).

PM:     PM bypassed: Outer query contains no views.

FPD: Considering simple filter push in SEL$1 (#0)

FPD:   Current where clause predicates in SEL$1 (#0) :

         apadrv-start: call(in-use=172, alloc=0), compile(in-use=31672, alloc=0)

kkoqbc-start

: call(in-use=172, alloc=0), compile(in-use=32276, alloc=0)

 

 

可以看出，Query Optimizer使用各種改寫手段對an041xjjnbr8q進行改寫嘗試。注意，這種嘗試對一些簡單SQL沒有過多的意義。

 

此處出現的Transformation過程，也只是優化器對於我們SQL的一種處理流程。

 

ü        優化器引數設定

 

優化器的工作原理是受到系統引數（顯示、隱式）、資料庫物件統計量。其中，系統引數常常起到優化器工作引數和路徑方法選擇的作用。在10053事件的優化跟蹤檔案中，很大的篇幅就是記錄著當前使用的優化器引數。

 

 

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

PARAMETERS USED BY THE OPTIMIZER

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

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

  PARAMETERS WITH ALTERED VALUES

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

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

  PARAMETERS WITH DEFAULT VALUES

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

  optimizer_mode_hinted               = false

  _query_rewrite_fudge                = 90

  optimizer_features_enable           = 10.2.0.1

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

  _force_rewrite_enable               = false

  _optimizer_star_tran_in_with_clause = true

  _optimizer_complex_pred_selectivity = true

  _gby_hash_aggregation_enabled       = true

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

  PARAMETERS IN OPT_PARAM HINT

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

 

 

注意，使用引數是按照預設值和改寫值進行組織的。影響Oracle行為的引數包括顯示引數和隱式引數兩個部分。有時候，會根據系統需要對系統引數進行調節，來控制影響優化器計算成本乃至生成執行計劃的行為。

 

在Trace檔案中，對引數的顯示也是根據預設引數和修改引數進行組織的。

 

 

ü        系統system和資料物件引數

 

我們經常說的Oracle統計量，是分為若干型別的。比較常見的有System Statistics、Schema Statistics、Data Dictionary Statistics和Database Statistics。在CBO生成執行計劃的時候，這些統計量都會作為CBO優化器的參考資訊計入到成本計算公式中。

 

 

 

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

SYSTEM STATISTICS INFORMATION

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

  Using NOWORKLOAD Stats

  CPUSPEED: 485 millions instruction/sec

  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)

  IOSEEKTIM: 10 milliseconds (default is 10)

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

BASE STATISTICAL INFORMATION

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

Table Stats::

  Table:  T  Alias:  T

    #Rows: 53308  #Blks:  735  AvgRowLen:  93.00

Index Stats::

  Index: IDX_T_OBJECT_ID  Col#: 4

LVLS: 1  #LB: 118  #DK: 53308  LB/K: 1.00  DB/K: 1.00  CLUF: 850.00

 

 

在trace檔案中，我們看到了system statistics和schema statistics兩部分的說明。System statistics中的內容表示計算執行計劃時使用的系統狀態引數，是滿負荷下的系統統計量還是空負荷的。之後是CPU、IO相關的時間和速度資訊。

 

Schema統計量中的內容就比較熟悉了，分別是SQL相關的資料表和索引對應的統計量。在上篇中，我們已經看到過這些資料。相當於計算執行計劃的過程是使用這些引數的。

 

 

ü        訪問路徑成本cost計算比較

 

CBO的工作原理，就是一個執行路徑試探性生成和比較的過程。其中，訪問方式table access path是一個重要的成本計算點。從跟蹤檔案中，我們可以看到這種路徑生成的比較。

 

 

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

SINGLE TABLE ACCESS PATH

  Table:  T  Alias: T    

    Card: Original: 53308  Rounded: 53308  Computed: 53308.00  Non Adjusted: 53308.00

  Access Path: TableScan

    Cost:  164.27  Resp: 164.27  Degree: 0

      Cost_io: 162.00  Cost_cpu: 13230458

      Resp_io: 162.00  Resp_cpu: 13230458

  Access Path: index (index (FFS))

    Index: IDX_T_OBJECT_ID

    resc_io: 28.00  resc_cpu: 7237290

    ix_sel: 0.0000e+000  ix_sel_with_filters: 1

  Access Path: index (FFS)

    Cost:  29.24  Resp: 29.24  Degree: 1

      Cost_io: 28.00  Cost_cpu: 7237290

      Resp_io: 28.00  Resp_cpu: 7237290

  Access Path: index (FullScan)

    Index: IDX_T_OBJECT_ID

    resc_io: 119.00  resc_cpu: 11509051

    ix_sel: 1  ix_sel_with_filters: 1

    Cost: 120.98  Resp: 120.98  Degree: 1

  Best:: AccessPath: IndexFFS  Index: IDX_T_OBJECT_ID

         Cost: 29.24  Degree: 1  Resp: 29.24  Card: 53308.00  Bytes: 0

 

 

Oracle CBO的所謂成本，就是將CPU與IO結合的一個指標。在計算訪問路徑中，我們可以看到這種選擇過程。如我們的例項中，如果直接使用Table Scan，計算出的成本約為164。而之後使用的Index Fast Full Scan（FFS）路徑，成本有29.24。對應的Index Full Scan，成本為120。

 

最後，CBO給予了一個BEST路徑選擇，使用Index Fast Full Scan方式，讀取資料索引idx_t_object_id，不進行回表操作。

 

 

ü        生成執行計劃

 

經過比較cost過程，是確定執行計劃的一種過程。之後就是生成執行計劃的結果部分。

 

 

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

OPTIMIZER STATISTICS AND COMPUTATIONS

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

GENERAL PLANS

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

Considering cardinality-based initial join order.

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

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

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

Best so far: Table#: 0  cost: 29.2436  card: 53308.0000  bytes: 0

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

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

Number of join permutations tried: 1

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

Final - All Rows Plan:  Best join order: 1

  Cost: 29.2436  Degree: 1  Card: 53308.0000  Bytes: 0

  Resc: 29.2436  Resc_io: 28.0000  Resc_cpu: 7237290

  Resp: 29.2436  Resp_io: 28.0000  Resc_cpu: 7237290

kkoipt: Query block SEL$1 (#0)

******* UNPARSED QUERY IS *******

SELECT COUNT(*) "COUNT(*)" FROM "SYS"."T" "T"

kkoqbc-end

          : call(in-use=13248, alloc=0), compile(in-use=32844, alloc=0)

apadrv-end: call(in-use=13248, alloc=0), compile(in-use=33392, alloc=0)

 

sql_id=an041xjjnbr8q.

Current SQL statement for this session:

select /*+ 10053 trace */count(*) from t

 

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

Plan Table

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

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

| Id  | Operation              | Name           | Rows  | Bytes | Cost  | Time      |

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

| 0   | SELECT STATEMENT       |                |       |       |    29 |           |

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

| 2   |   INDEX FAST FULL SCAN | IDX_T_OBJECT_ID|   52K |       |    29 |  00:00:01 |

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

Predicate Information:

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

 

Content of other_xml column

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

  db_version     : 10.2.0.1

  parse_schema   : SYS

  plan_hash      : 1572773910

  Outline Data:

  /*+

    BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_ID"))

    END_OUTLINE_DATA

  */

 

 

 

7、結論

 

Oracle Query Optimizer是Oracle中較為複雜的邏輯元件，也是Oracle DBMS的核心技術。作為行業領軍的資料庫產品，Oracle Query Optimizer屬於關鍵技術。CBO如何選擇執行計劃，是每個希望深入核心的研究者追尋的目標。

 

使用10053事件，我們可以較為深入的接近CBO的判斷軌跡和方式。在出現一些執行計劃問題的時候，我們也可以看到CBO為什麼沒有按照希望的方式執行。

 

10053事件跟蹤檔案包括很多重要的資訊和內容。由於篇幅的原因，只尋找到一個簡單的SQL進行trace。

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17203031/viewspace-706625/，如需轉載，請註明出處，否則將追究法律責任。