2.2　成本

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

下面會(huì)對(duì)成本的基本概念、計(jì)算方法加以簡單說明。后面會(huì)結(jié)合一個(gè)SQL案例，闡述如何計(jì)算一個(gè)成本。

2.2.1　基本概念

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

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

2.2.2　計(jì)算公式

成本的具體計(jì)算公式如下：

---

Cost = (#SRDs * sreadtim +#MRDs * mreadtim +#CPUCycles /cpuspeed) / sreadtim

---

公式說明：

·#SRDs：單數(shù)據(jù)塊讀取的次數(shù)。

·#MRDs：多數(shù)據(jù)塊讀取的次數(shù)。

·#CPUCycles：CPU時(shí)鐘頻率。

·sreadtim：隨機(jī)讀取單數(shù)據(jù)塊的平均時(shí)間，單位為毫秒。

·mreadtim：順序讀取多數(shù)據(jù)塊的平均時(shí)間，也就是多數(shù)據(jù)塊平均讀取時(shí)間，單位為毫秒。

·cpuspeed：代表有負(fù)載CPU速度，CPU速度為每秒鐘CPU周期數(shù)，也就是一個(gè)CPU一秒能處理的操作數(shù)，單位是百萬次/秒。

2.2.3　計(jì)算示例

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

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

1）準(zhǔn)備工作：

---

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

---

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

---

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 |
--------------------------------------------------------------------------
//對(duì)于上述這條SQL語句，優(yōu)化器采用了全表掃描的執(zhí)行方式，其估算的成本為200

---

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

---

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

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

---

*****************************
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í)行時(shí)是使用NOWORKLOAD的狀態(tài)，即此時(shí)沒有收集系統(tǒng)的統(tǒng)計(jì)信息。CPUSEED已經(jīng)給出，此外還給出另外兩個(gè)統(tǒng)計(jì)參數(shù)IOTFRSPEED、IOSEEKTIM。我們所需要的指標(biāo)可以通過如下關(guān)系進(jìn)行折算。在計(jì)算中，還涉及另外兩個(gè)系統(tǒng)參數(shù)：一個(gè)是塊大小，由db_block_size參數(shù)設(shè)定，當(dāng)前系統(tǒng)為8K；另外一個(gè)是一次多數(shù)據(jù)塊讀取的塊數(shù)，由db_file_multiblock_read_count參數(shù)設(shè)定，當(dāng)前系統(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）對(duì)象統(tǒng)計(jì)信息：在優(yōu)化器計(jì)算成本時(shí)，還需要參考對(duì)象級(jí)的統(tǒng)計(jì)信息。我們可以通過數(shù)據(jù)字典查看，也可以在10053的Trace文件中找到。在此跟蹤輸出中，相關(guān)部分如下。

---

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

---

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

---

Cost = (
        #SRDs * sreadtim +
        #MRDs * mreadtim +
        #CPUCycles /cpuspeed
        ) / sreadtim

---

簡單變換一下：

---

Cost = (
        #SRDs +
        #MRDs * mreadtim/sreadtim +
        #CPUCycles/(cpuspeed * sreadtim)
        )

---

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

---

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

---

7）計(jì)算CPU成本：

---

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

---

8）驗(yàn)證成本：下面解讀一下10053的成本計(jì)算，可與上面我們手工計(jì)算的部分進(jìn)行對(duì)比。

---

***************************************
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)化器計(jì)算的I/O成本為198.00（對(duì)應(yīng)于Cost_io）。這一點(diǎn)和計(jì)算得到的197.17非常接近。考慮到系統(tǒng)中有隱含參數(shù)，計(jì)算成本時(shí)一般向上取整。可以認(rèn)為兩者就是一致的。對(duì)于CPU成本計(jì)算，Cost_cpu: 25059861就是前邊引用的CPUCycles。整體CPU成本為總成本減去I/O成本，即199.67–198＝1.67。這和我們前面計(jì)算的完全一致。