2.2　成本

在對SQL語句進行優(yōu)化的過程中，對于成本的理解非常重要。因為Oracle絕大多數(shù)情況下就是使用基于成本的優(yōu)化器對SQL語句制定執(zhí)行計劃的。只有對成本有更深層次的認識，才能理解優(yōu)化器的行為，也更容易找出產(chǎn)生較差執(zhí)行計劃的原因。但對于成本及其計算方法，Oracle公司并沒有開放很多資料，因而只能從一些公開的資料揣摩其工作原理、計算方法等。

下面會對成本的基本概念、計算方法加以簡單說明。后面會結合一個SQL案例，闡述如何計算一個成本。最后，會對一個比較重要的概念——選擇率加以說明。

 

2.2.1　基本概念

成本是指花費在單數(shù)據(jù)塊讀取上的時間，加上花費在多數(shù)據(jù)塊讀取上的時間，再加上所需的CPU處理時間，然后將總和除以單數(shù)據(jù)塊讀取所花費的時間。也就是說，成本是語句的預計執(zhí)行時間的總和，以單數(shù)據(jù)塊讀取時間單元的形式來表示。

成本的概念也是在不斷演化中的，在不同的Oracle版本中是不同的。在Oracle 8i的版本中，成本是考慮了I/O子系統(tǒng)所做的請求數(shù)，并沒有考慮到CPU資源的使用開銷以及多數(shù)據(jù)塊訪問和單數(shù)據(jù)塊訪問的不同。在Oracle 9i中，引入了對CPU成本的計算，此外也加入了對單數(shù)據(jù)塊和多數(shù)據(jù)塊I/O請求的不同的考慮。到了Oracle 10g，又引入了對數(shù)據(jù)分布特征、緩存數(shù)據(jù)塊等因素的考慮。

 

2.2.2　計算公式

成本的具體計算公式如下：

Cost=（#SRDs*sreadtim+#MRDs*mreadtim+#CPUCycles/cpuspeed）/sreadtim

公式說明：

• #SRDs：單數(shù)據(jù)塊讀取的次數(shù)。
• #MRDs：多數(shù)據(jù)塊讀取的次數(shù)。
• #CPUCycles：CPU時鐘頻率。
• sreadtim：隨機讀取單數(shù)據(jù)塊的平均時間，單位為毫秒。
• mreadtim：順序讀取多數(shù)據(jù)塊的平均時間，也就是多數(shù)據(jù)塊平均讀取時間，單位為毫秒。
• cpuspeed：代表有負載CPU速度，CPU速度為每秒鐘CPU周期數(shù)，也就是一個CPU一秒能處理的操作數(shù)，單位是百萬次/秒。

 

2.2.3　計算示例

下面通過一個例子，說明如何通過上述公式計算一條SQL語句的運行成本。在此特別強調一下，成本的計算非常復雜，Oracle官方也沒有公布其具體的算法。在計算中，受影響的因素也比較多。下面的示例，僅僅作為一個參考，簡單描述了計算過程。

下面的示例是在Oracle 10gR2的版本中進行的，此版本的成本計算中既包含了I/O成本，也包含了CPU成本。下面的計算中就包含了兩個部分的計算過程。

（1）準備工作：

create table t1 as select * from dba_objects；
exec dbms_stats.gather_table_stats（ownname=>'HF'，tabname=>'T1'，estimate_percent=>100）；
//創(chuàng)建了一個測試表

（2）優(yōu)化器計算成本：

select * from t1；
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost （%CPU）| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 51054 |  4636K|   200   （1）| 00：00：03 |
|   1 |  TABLE ACCESS FULL| T1   | 51054 |  4636K|   200   （1）| 00：00：03 |
--------------------------------------------------------------------------
//對于上述這條SQL語句，優(yōu)化器采用了全表掃描的執(zhí)行方式，其估算的成本為200

（3）10053 Trace：在開始計算之前，先對上述SQL語句進行一次10053的Trace。通過這個跟蹤事件可以觀察到CBO是如何選擇執(zhí)行計劃的。關于這個跟蹤事件的具體用法，可參見本書后面的講解。在后面的計算過程中，我們可以參看這個跟蹤事件的輸出。

alter session set events '10053 trace name context forever'；
select * from t1；
alter session set events '10053 trace name context off'；

（4）系統(tǒng)統(tǒng)計信息：先來查看一下計算公式，在公式中指標Sreadtim、Mreadtim、cpuspeed跟具體的物理硬件有關。在Oracle數(shù)據(jù)庫中，可通過收集系統(tǒng)級的統(tǒng)計信息得到相關的數(shù)據(jù)（關于系統(tǒng)的統(tǒng)計信息，可參看后面的統(tǒng)計信息部分）。如果數(shù)據(jù)庫沒有收集相應的信息，則此時處于NOWORKLOAD狀態(tài)，這種情況下可通過幾個新的統(tǒng)計參數(shù)折算得到我們需要的指標。

在10053的跟蹤事件中，我們可以找到相關的部分：

*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats          
  CPUSPEED： 1251 millions instruction/sec
  IOTFRSPEED： 4096 bytes per millisecond （default is 4096）
  IOSEEKTIM： 10 milliseconds （default is 10）

從上面輸出中可見，這條語句執(zhí)行時是使用NOWORKLOAD的狀態(tài)，即此時沒有收集系統(tǒng)的統(tǒng)計信息。CPUSEED已經(jīng)給出，此外還給出另外兩個統(tǒng)計參數(shù)IOTFRSPEED、IOSEEKTIM。我們所需要的指標可以通過如下關系進行折算。在計算中，還涉及另外兩個系統(tǒng)參數(shù)：一個是塊大小，由db_block_size參數(shù)設定，當前系統(tǒng)為8K；另外一個是一次多數(shù)據(jù)塊讀取的塊數(shù)，由db_file_multiblock_read_count參數(shù)設定，當前系統(tǒng)為8。

Sreadtim = ioseektim + db_block_size/iotrfrspeed
     = 10 + 8192/4096 = 12
Mreadtim = ioseektim + db_file_multiblock_read_count * db_block_size/iotfrspeed
     = 10 + 8*8192/4096 = 26

（5）對象統(tǒng)計信息：在優(yōu)化器計算成本時，還需要參考對象級的統(tǒng)計信息。我們可以通過數(shù)據(jù)字典查看，也可以在10053的Trace文件中找到。在此跟蹤輸出中，相關部分如下。

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats：：
  Table： T1  Alias： T1
    #Rows： 51054  #Blks：  723  AvgRowLen：  93.00
//從上面的輸出中可見，表T1的塊數(shù)為723。對應于全表掃描而言，需要讀取723個8K的數(shù)據(jù)塊。

（6）計算I/O成本：前面提到過，成本的計算分為兩個部分，分別為I/O和CPU。下面簡單看一下，I/O的計算過程。前面提到的計算公式如下。

Cost = （
        #SRDs * sreadtim +
        #MRDs * mreadtim +
        #CPUCycles /cpuspeed
        ） / sreadtim

簡單變換一下：

Cost = （
        #SRDs +
        #MRDs * mreadtim/sreadtim +
        #CPUCycles/（cpuspeed * sreadtim）
        ）

其中前兩行為I/O成本，暫不考慮最后一行，因為這條語句為全表掃描，使用的是多數(shù)據(jù)塊讀取的方式，因此，I/O成本計算值考慮到第二行即可。

IO_Cost = #MRDs * mreadtim/sreadtim
       = ceil（723/8） * 26 / 12
          = 197.17
//系統(tǒng)總共需要讀取723個數(shù)據(jù)塊，每次讀取8個塊，共需要ceil（723/8）=91次

（7）計算CPU成本：

CPU_Cost = #CPUCycles/（cpuspeed * sreadtim）
       = 25059861/（1251*12000）
           = 1.67
//總的CPU處理次數(shù)是從10053中得到的，后面會說明。整體CPU成本為1.67

（8）驗證成本：下面解讀一下10053的成本計算，可和上面我們手工計算的部分進行對比。

***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Table： T1  Alias： T1
    Card： Original： 51054  Rounded： 51054  Computed： 51054.00  Non Adjusted： 51054.00
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path： TableScan
    Cost：  199.67  Resp： 199.67  Degree： 0     //CPU成本為199.67 - 198 = 1.67
Cost_io： 198.00  Cost_cpu： 25059861          //IO成本為198
Resp_io： 198.00  Resp_cpu： 25059861
  Best：：AccessPath： TableScan
       Cost： 199.67  Degree： 1  Resp： 199.67  Card： 51054.00  Bytes： 0

從10053可見，優(yōu)化器計算的I/O成本為198.00（對應于Cost_io）。這一點和計算得到的197.17非常接近。考慮到系統(tǒng)中有隱含參數(shù)，計算成本時一般向上取整。可以認為兩者就是一致的。對于CPU成本計算，Cost_cpu：25059861就是前邊引用的CPUCycles。整體CPU成本為總成本減去I/O成本，即199.67–198＝1.67。這和我們前面計算的完全一致。