良好SQL指令的撰寫-Performance Tuning再探

1. Schema設計

在前面的章節中，著眼在靜態的Schema調整，不當的schema設計，會使得SQL指令變得遲緩而無效率，若您已閱讀過「初探」一文，我們可以假設您已對Schema的設計有了最基本的了解，這裏再將重點摘錄如下：

Schema是SQL指令執行的環境，好的環境才有好的效率
Index可加速SQL指令的查詢速率，但Index的建立必須適得其所
Index的建立必須與執行的SQL相搭配，因此事前完整的SQL蒐集變得極為重要
組合成Index的欄位，必須有好的排除效果；若為複合鍵，次序排列應以排除效果大的為先，與搭配的SQL指令一併考量
考量組合Index的各欄位及Index本身的長度大小，原則上是儘量讓Index頁多放記錄，因此鍵值長度需儘量簡短，如此Index的階層就會較短，搜尋就會較快

本文將會著重在SQL指令動態執行時，對performance所造成的影響，至於硬體及系統與DB效能的關係，會在「Performance三探」中探討。

2. 蒐集完整的SQL指令

首先要作的動作是蒐集完整的SQL指令，即使不是全部的，也要儘量能蒐集得愈完整愈好，有時候為了加快一個SQL指令的速率，反而會造成其他SQL指令變得遲緩，兩者之間要權衡得宜。

這也就是在系統建置之初，DBA與AP須要密集溝通討論，這個時候DBA能夠適時地針對不當的SQL指令進行建議與調整，且在建置之初，還可請AP人員進行改寫SQL指令及程式，DBA也可調整DB Schema來配合；若是系統已上線好一陣子，只有執行檔可供參考，此時只能由DB這邊來進行調整，有些地方就不免施不上力來。

使用DBMaker，可用來蒐集完整SQL指令的工具有三，分別為dmSQL、ODBC_LOG及AUDIT_TRAIT，這三項工具，不僅可用來蒐集SQL，也可用來對AP及環境作監測。

(1) 利用dmsql

dmsql指令是DBMaker用來與Server溝通的一項工具，它本身也屬於一支客戶端應用程式，同時可用來擷取Server上的重要資料，進行維護功能。

DBMaker有一些以「SYS」開頭的虛擬表格，若能適當地去擷取這些資訊，有助於了解伺服器的環境狀況。可在連接資料庫後，使用：「Select * from SYSUSER;」指令，來擷取使用者相關資訊，包括了：登錄的電腦名稱、IP位置、所下的SQL指令，及使用的時間等等資訊，由於我們只對客戶端所下達的SQL指令有興趣，故可用「select SQL_CMD from SYSUSER」，執行這個命令常會發現出現的格式相當紊亂而不易解讀，因此就個人而言，通常會將輸出的結果，先導向到另一個文字檔，整個過程如下：

Set printto C:\SQLCollection1008.txt;

Select SQL_CMD from SYSUSER; (等待數分鐘後)

…

Set printto off;

這樣的作法，筆者常戲稱是「錄製SQL」，使用這個方法來錄製的SQL集，由上述過程便知是有其死角的，因為有些報表程式，可能一個月才執行一次，這個方式就可能漏接了這些SQL指令；再者，在DBMaker 4.0前，可顯示的SQL長度只有512，如此，所擷取的SQL指令會因長度太長而僅捕捉到SQL的前半部，這樣的SQL指令是無法作進一步分析的。

即使如此，使用這個方法來擷取SQL指令，個人覺得十分簡單且足夠了，除非感覺到捉到的實在是不全，個人才會使用下方的另外兩個工具。

(2) 透過ODBC_LOG

DBMaker在3.71後自行提供了ODBC_LOG功能，MicroSoft本身也提供了ODBC的追蹤功能，可是不知何故，筆者甚少一次設定便能成功地開始抓取ODBC Function Code，即使到現在，使用Microsoft的ODBC追蹤功能，「賭運氣」的成份還是很重。

DBMaker的ODBC_LOG與MicroSoft的ODBC追蹤功能十分相似，只是DBMaker的log擷取一定會成功，設定方式十分簡單，只要在「客戶端」上的dmconfig.ini，作如下設定：

[DM_COMMON_OPTION]

LG_TRACE=1

LG_PATH=J:\TEST\LOGTEST.LOG

OK，從上面的敘述，就會發現ODBC_LOG的幾個缺點，第一、在dmconfig.ini的設定上，您會發現該一個section是獨立於其他資料庫的(也用中刮號來括示DM_COMMON_OPTION)，所以在這一台客戶端上的任何資料庫連接，都會被記錄，例如：

[DM_COMMON_OPTION]

LG_TRACE=1

LG_PATH=J:\TEST\LOGTEST.LOG

[DB1]

DB_SvAdr = 10.133.1.56

DB_PtNum = 9998

[DB2]

DB_SvAdr = 10.133.1.57

DB_PtNum = 9998

若是這一台客戶端同時連接到DB1及DB2，都會被一併被擷取下來，這對只想單純抓出DB1的問題時，無端增加許多麻煩。

第二、ODBC_LOG設定在「客戶端」，抓取的資料是從這台客戶端往伺服器丟去的ODBC Function Code，但是我們大多是想抓到伺服器所接收到的所有客戶端指令，而不是只有「這台」客戶端丟出來的指令

第三、ODBC Function Code比較難懂，我們固然可以只注意含有SQL指令的片斷，但不可避免的，要在數千、數萬個深澀難懂的ODBC Function中，抓到SQL指令，有些浪費時間。

因此，用ODBC_LOG來抓SQL指令，有些吃力不討好，但有時AP的一些怪異行為，打開ODBC_LOG就十分有用，所以筆者多數是使用這個工具來抓客戶端的AP問題。

(3) 利用AUDIT TRAIT

AUDIT TRAIT是上述兩者的結合體，它是擷取Server端所接收到的信號，並使用DBMaker所特有的記錄方式記錄；但AUDIT TRAIT的DISPLAY功能，在4.0後方提供。

我們可忽略多數對調校沒有什麼意義的function，只蒐集SQL指令，它的設定十分簡單，只要在「伺服器」端的dmconfig.ini設定：

DB_AUDIT=1

便可在[DBMAKER_HOME]/BIN底下，找到[DB_NAME].LOG，之後便可使用DBMaker的工具來檢視這個LOG檔案。

AUDIT_TRAIT抓下來的資料是最完整的，但也是有其缺點，例如：一個月執行一次的報表程式，若在監督期間未執行的話，仍然是抓取不到；還有，AUDIT_TRAIT必須關閉資料庫，再啟動後始生效，但在一些頻繁的線上作業系統，這樣的動作就不太適宜，檢視AUDIT_TRAIT的工具，將在4.0版後擇時推出。

3. SQL指令執行程序

一般的SQL指令丟到Server後，其執行順序如下：

每一個階段都有一定的職責及工作，必須要了解每一階段輸入的參數為何、影響了哪些行為、造成什麼後果的相互關係，才能對每一階段的元素進行調控。

(1)   Parser：一般的SQL指令在執行之初，會先經由Parser作Token的分解，一般的Pattern如下：

Select PROJECTION from TABLE_NAME where PREDICATE

PROJECTION指的是所要擷取的欄位、expression組合，PREDICATE則是搜尋的條件，這兩者的搭配，就會讓optimizer產生不同的行為模式，最簡單的例子

假設有一table t1，有三個column，分別為c1、c2、c3，皆為整數，c1為primary key。

select * from t1 where c1=1 and c2=2 and c3=3;

此例中，「*」為Projection，「c1=1 and c2=2 and c3=3」為Predicate，Optimizer會使用Table-Scan去抓取資料。

select c1 from t1;

在這個例子中，Optimizer就會使用Index-scan，原因在於，c1的資料找索引頁就有了，不須要去抓資料頁；就像在「初探」一文中曾說，對「作者」欄位有索引，那麼要找所有「作者」的資料，沒必要將全部的書拿出來查，只要去找「作者索引」即可。

所以一個SQL指令的好壞，從Predicate與Projection的安裝上，就可能造成差別。

但差異最大的，恐怕還是Predicate中，對於Factor的安排。

(2)   Factor分析

所謂的「Factor」，就是Predicate中，邏輯上可獨立出來判別的因子，聽起來很玄不?事實上相當簡單，例如：c1=1 and c2=2，就是有兩個Factor，分別是「c1=1」及「c2=2」；為什麼要這樣把SQL指令拆開呢? 假設這個table共有100萬筆資料，c1=1的記錄只有10筆，而c2=2卻有10000筆，那麼先從c1=1去判斷較好，意即若c1!=1的話，根本就不用去判斷c2=2這個因素是否滿足；所以「Factor」即是「邏輯上可獨立出來判別的因子」。

相對地，若是Predicate是c1=1 or c2=2，那麼這則只有一個Factor，因為即使是c1!=1，你還是得要對c2=2加以判斷，同樣地若c2!=2，還是要對c1=1加以判斷，這兩個式子，並無法因前一個式子滿足或不滿足，就可終止整個式子的判斷。

更進一步想，假若曾對c1加以索引，那麼在搜尋上，因為可先利用索引頁作判別，再實際抓取資料，因此在速度上會比較快。

同樣地，對c2也曾加以索引的話，也可能比較快，但要用誰比較好呢?以上面的例子來看，若c1=1有10筆，自然用c1的索引比較好，若使用c2索引，因為c2有10000筆資料，可利用索引頁將這10000筆資料所在位置找到，然後再抓取資料頁，再從資料頁去判斷c1=1是否滿足，相形之下，會比較慢。但這還是會比抓取100萬筆資料的資料頁(Table-Scan)，要快得多。

(3)   Optimizer

OK，上述以概念上來看，都還算有道理，在DBMaker的實作上，則會有須要考量的地方。我們以上述例子繼續說明，假設c1、c2都分別作了索引，那麼Optimizer會使用那個索引來搜尋呢?

因此最好我們能提醒Optimizer一個事實，就是c1=1的有10筆，c2=2的卻有10000筆；Optimizer就可根據我們所提供的「情報」、「資訊」，來作出實際搜尋時所採行的行為方法。

因此在手冊上，會提醒使用者，有時須要不定期的update statistics，所謂的「statistics」就是類似上頭所說的「10筆」、「10000筆」的資訊，系統會將一些數據記錄起來，但是由於每insert、delete、update資料庫的資料，更新相對的記錄將會花去許多系統資源及時間，因此DBMaker只會更新必要的資訊，隨著時間的增加，許多沒被更動的系統記錄值也許已經不合時宜，此時就需要使用這個命令來更新所有的記錄值。

一旦update statistics後，所有的系統記錄值被更新完畢，理論上Optimizer會使用最聰明的方法來搜尋方式。但畢竟程式不可能所有的狀況都會考量進去，以上例來講，若變成「c1=100 and c2=200」，也許「c1=100」的筆數有1000個，而「c2=200」的反而只有10個，沿用c1索引反而變慢了。

在這種情形下，一般會考慮所謂的「排除效果」(filtered effect)，就是那一個欄位會讓factor所產生出來的結果最小，排除不符合的記錄筆數最大，一旦決定後，就將其納入建立index的考量，以上例來講，若發現c1=?的排除效果較強的話，筆者個人會只建c1的索引而不建c2索引，避免讓Optimizer有機會去走到不必要的路。

4. 研究Execution Plan

接下來，就是了解SQL指令執行時，Optimizer所實際作的程序吧。一個SQL指令的執行，不見得只有一個方法，正所謂「條條大路通羅馬」，Optimizer必須根據所獲得的資料，及可執行的幾個方法，作出正確的決定；而對同一個SQL指令，可得到相同結果的執行流程稱之為「Execution Plan」；當DBMaker將SQL指令正確parse完畢，Optimizer再根據系統記錄值，套入可執行的Execution Plan，計算出那一條路是「最少成本」的(對DBMaker而言，就是花最少時間的)，最後付諸實行。

要顯示Optimizer所計算出的最佳Execution Plan，在dmsql中，有三種命令可以使用：

dmsql> set dump plan on; (將optimizer的execution plan顯示出來)

dmsql> set dump plan off; (關閉此項功能)

dmsql> set dump plan only; (只顯示execution plan，但不抓取資料)

(1)   Table Scan及Index Scan

Execution Plan在處理Query時，會去決定是否要用Index來搜尋，或是Table 來搜尋，所謂的Table Scan，就是將整個Table讀取至記憶體；而Index Scan則是會先將索引頁抓取至記憶體，透過索引頁，再去抓取資料頁。

create table SYSADM.t1 (

t1id SERIAL(1000),

pdate DATE default cur_date() )

in DEFTABLESPACE lock mode page fillfactor 100 ;

alter table SYSADM.t1 primary key ( t1id) ;

dmSQL> set dump plan on;

dmSQL> select * from t1;

----- begin dump plan -----

{ON Block 0}

ON Type     : SCAN

[PL Block 0]

Method     : Scan

Table Name : t1

Type : Table Scan

Order : <none>

Factors    : <none>

…….

----- end dump plan -----

   t1id   pdate

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

5    NULL

4    NULL

3   NULL

2    NULL

1    NULL

5 rows selected

此處，在Scan Type中，顯示這個Query(select * from t1)使用了Table Scan來搜尋，最後找到五筆資料。

dmSQL> select t1id from t1;

----- begin dump plan -----

{ON Block 0}

ON Type     : SCAN

[PL Block 0]

Method     : Scan

Table Name : t1

Type : Table Scan

Order : <none>

Factors    : <none>

….

----- end dump plan -----

   t1id

===========

5

4

3

2

1

5 rows selected

在此處，Query已經從「select * from t1」變成「select t1id from t1」，由於t1id是Primary Key，故有索引(Foreign Key則沒有)，理論上，使用Index Scan是比較合理的!!原因是因為Projection中只有t1id，所以沒必要再到資料頁去抓資料。但現在顯示的是仍然使用Table Scan。

這就是我們之前所說，Optimizer所賴以判斷的系統記錄值有問題，我們可以先update statistics，更新這個表格的系統記錄值，也許Optimizer還不知道有Index可用呢。

dmSQL> update statistics t1; (更新t1的系統統計值)

dmSQL> select t1id from t1;

----- begin dump plan -----

{ON Block 0}

ON Type     : SCAN

[PL Block 0]

Method     : Scan

Table Name : t1

Scan Type : Index Scan on PrimaryKey(t1id)

Order : ASC

Index EQFA#: 0

Index FA# : 0

Index FACOL: Index Cost : 2

….

----- end dump plan -----

   t1id

===========

1

2

3

4

5

首先，在Scan Type中變成了Index Scan，再來，在select出來的結果中，排序的方式是由小到大，相較之前Table Scan時，產生的結果是由大到小，事實上Table Scan是資料怎麼放，就怎麼拿出，例中的由大到小是因為資料在資料庫中便是用這種方式由大到小儲存。而INDEX因為事先排序過，所以產生的資料會依欄位的次序一一輸出。

(2)   Merged Join及Nested Join

在Query 的Execution Plan中，Join是最令人頭痛的，兩個表格的「結合」(Join)，通常會伴隨著所謂的「結合因子」(Join Factor)，例如：

select * from A,B where A.C1=B.C1;

A.C1=B.C1便是Join Factor，那麼實際「結合」時，DBMaker有兩種方式來實作「結合」的動作。

以實例說明較為清楚，假設現在有兩個Table，內容如下：

A表格

C1 C2

1 A

2 B

3 C

B表格

C1 C2

3 E

2 F

1 G

Select * from A, B where A.C1=B.C1;

A.C1   A.C2   B.C1   B.C2

1 A     1 G

2 B 2 F

3 C     3 E

在join的過程中，分別為Nested Join及Merged Join，分述如下：

Nested Join(巢狀結合)：將A表格的資料，一筆一筆抽出與B表格配對，結合成另一個大Table，如下表：

A表格		B表格
C1	C2	C1	C2
1	A	3	E
1	A	2	F
1	A	1	G
2	B	3	E
2	B	2	F
2	B	1	G
3	C	3	E
3	C	2	F
3	C	1	G

這時再進一步去判斷Join Factor是否滿足，產生最後的結果。

Nested Join由於需要先行對兩表格作配對結合，產生的暫時表格可能會很大(若A表格有m筆資料，B表格有n筆資料，產生的暫存表格會有m X n筆資料)，若是有第三、第四個表格要一併join在一起，產生的暫存表格可能會有 m X n X o X p(若分別有o筆與p筆)。

所以有時一個query感覺上，只是對幾個表格作join，卻可能要花上很多的時間，便是因為若A有10萬筆，B、C、D就算只有100筆，產生的暫存表格卻有1G的資料量(10萬X100X100X100)。

Merged Join(合併結合)：與Nested Join不同，Merged Join在進行join之前，必須先對各個表格先作Sort的動作：

A表格

C1 C2

1 A

2 B

3 C

B表格(先行排序過)

C1 C2

1 G

2 F

3 E

作完Sort的動作後，再由A表格循序往下讀，與B表格的內容一一比對，是否符合A.C1=B.C1的條件。

若符合，則將結果挑出，若不符合，若B.C1較大，則A表格再往下讀一筆資料，否則則是繼續讀B的下一筆資料。

Merged Join的壞處是它必須先對每一個表格作「排序」的動作，這個動作十分耗用電腦資源，若是一個Table有百萬筆資料，Merged Join等於要讓這個Table先行Sort，產生一個Sort完後的暫存表格(故也是百萬筆)，再來進行Join Factor檢查，但在這種Case上，用Nested Join恐怕也不是太高明。

那麼是不是Merged Join一定比Nested Join來得好呢??這倒未必，如果一個table很小(10筆)，另一個很大(百萬筆)，Nested Join只會產生一個千萬筆的表格，以後便直接掃描這個暫存表格。若是Merged Join的話，要先對百萬筆的表格作Sort，這個動作將會耗費極大的系統資源及時間，在這個例子中，使用Merged Join的話，反而不是明智之舉。

當然，一個SQL指令的完成，可能要經過好幾道的步驟後，組合相關的子集合，方才產生最終的「結果集」(result set)，我們以實例作說明：

dmSQL> select * from A , B where A.c1=B.c1;

----- begin dump plan -----

{ON Block 0}

ON Type     : JOIN

[PL Block 0]

Method     : Join

Type : Merge Join

Factors    : (1) A.c1 = B.c1

MJ Factors : (1) A.c1 = B.c1

I/O Cost   : 4.7

….

Sub Block 1: [PL Block 1]

Sub Block 2: [PL Block 2]

[PL Block 1]

Method     : Sort

I/O Cost   : 2.3

…..

SUB Block : [PL Block 3]

[PL Block 3]

Method     : Scan

Table Name : A

Type : Table Scan

Order : <none>

Factors    : <none>

….

Result Rows: 1000.0

[PL Block 2]

Method     : Sort

….

Result Rows: 1000.0

SUB Block : [PL Block 4]

[PL Block 4]

Method     : Scan

Table Name : B

Type : Table Scan

Order : <none>

Factors    : <none>

….

Result Rows: 1000.0

----- end dump plan -----

    c1 c2     c1 c2

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

1 A    1 G

2 B    2 F

3 C    3 E

3 rows selected

似乎很難看懂，我們將上面的流程，畫圖表示：

在Block 0中，我們決定要作「Merged Join」，因此需再對A、B對c1作Sort的動作，在Block 3中，決定了使用Table Scan，對A掃描完後，產生一暫存表格(Block 1的Sort動作)，同樣地，在Block 2及Block 4也是作同樣的動作。

但在Block 3、Block 4中的Sort動作，事實上是很可怕的，首先，Table Scan就已經花上不少時間，再來，要對掃描完的表格作Sort(使用暫存表格)，又要花上不少時間，因此，在作Join的時候，這Table Scan及Sort的動作是亟須消弭的地方。

5. 調校SQL指令

(1)   避免惡名昭彰的「Table Scan」

Table Scan是所有DBA最欲除之而後快的惡夢，因為大表格的Table Scan，要花費許多I/O動作，還有「清洗」記憶體效果，同時還會佔用其他系統資源，但一般的是沒什麼必要一定要將整個表格的資料拿在手上的。想想，若是一個400M的資料表格，200M的記憶體，進行Table Scan，那麼就等於將近一片光碟要讀取放在記憶體中(大家可以想見，這個速度有多慢)，將記憶體清洗了兩次(其他user只能等你作完這個動作，將記憶體釋放出來)。

光只是想想就令人毛骨悚然了，這個動作實在是太駭人了。但是程式設計師在撰寫程式時，其實是很難想到這一層的，因為該建的Index都建了，自然不會想到是否正確地使用了環境提供的Index。

若是一個SQL指令本身使用了Table Scan，這倒還好，因為有許多工具可以輔助我們，但是若像上面「join」裏的例子，Execution Plan裏的Block 3、Block 4「偷偷地」使用了Table Scan，又不太好找，對performance又傷害頗大，才是我們的最大敵人。

(2)   由Index開始改善

可以避免Table Scan的作法，首先，就是建好Index，讓Optimizer可以找到更好的一條路來執行，這當中，須權衡Projection與Predicate的映射狀況，排除效果是否良好，對其他Insert、Delete、Update指令的影響是否重大，都一併計入考量，重點是要避免掉Table Scan；建立Index所須作的考量，請見「初探」一文及本文前節所提。

以前面的例子來說，Block 3、Block 4都在作Table Scan，然後才進行Join，事實上，以Join Factor(A.c1=B.c1)來說，若是對A、B的c1欄位事先索引，那麼在作Sort的步驟時，只要各抓取A、B的索引頁，因為索引頁已經先行排序且可直接進行比對，因此速度上會比原來的方式快上許多，如下：

dmSQL> select * from A , B where A.c1=B.c1;

----- begin dump plan -----

{ON Block 0}

ON Type     : JOIN

[PL Block 0]

Method     : Join

Type : Nested Join

Factors    : <none>

…

Sub Block 1: [PL Block 1]

Sub Block 2: [PL Block 2]

[PL Block 1]

Method     : Scan

Table Name : A

Scan Type : Index Scan on PrimaryKey(c1)

Order : ASC

…

[PL Block 2]

Method     : Scan

Table Name : B

Scan Type : Index Scan on PrimaryKey(c1)

….

Factors    : (1) A.c1 = B.c1

I/O Cost   : 2.0

CPU Cost   : 0.3

Sub Cost   : 0.0

Result Rows: 1.0

----- end dump plan -----

    c1 c2     c1 c2

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

1 A    1 G

2 B   2 F

3 C    3 E

3 rows selected

在這個例子中，首先，原來的merged join變成了nested join了，而Block 1是用Index 去Scan Table A，這樣的作法，掃描出來的資料已經經過排序了，所以原來的Sort動作也不見了；同樣地，在Block 2也對Table B作Index Scan，但是加了一個Factor。也就是因為如此，要Sort、比對join factor及移動cursor的merged join，在這裏反而不如nested join來得簡單，所以Optimizer就使用nested join來處理join的動作，有了這樣的index建置，絕對比原來的Query指令來得快。

(3)   改寫AP的SQL指令

有些程式所丟出來的SQL，即使在Server端調整，還是無法將Performance調整好，這時就必須從傳來的SQL指令中改寫，程式設計師與DBA在schema的設計過程中，常要不斷溝通與互動，便是要將這些Performance的因素儘量予以消弭。

6. 實戰演練

一般的SQL指令，是很難一眼讓你看出他有什麼問題的，請看下面的SQL指令：

Select D.RoutNo as RoutNo,Routing,AirlineID,sum(Fare) as Fare,Sum(SaleAmount) as SaleAmount,count(D.IDNo) as Count

from tmsSaleTicket M ,tmsSaleTicketDet D outer Program.tmsRouting R

where M.YearMonth='200109' and M.Dep='1' and M.SaleNo=D.SaleNo and D.RoutNo=R.RoutNo

Group by D.RoutNo,Routing,AirlineID order by AirlineID ;

表格名稱資料筆數可用索引

tmsSaleTicket 10395 unique index Index1 on tmsSaleTicket ( SaleNo asc )

tmsSaleTicketDet 33571 unique index Index1 on tmsSaleTicketDet ( SaleNo asc , IDNo asc )

index Index2 on tmsSaleTicketDet ( PK asc )

Program.tmsRouting 1240 primary key ( RoutNo) ;

這三個表格要先經過一個join的動作，而後再經過predicate刪選後，產生的資料要經過group by及order by處理，最後產生結果集。

現在來看這個Query產生的Execution Plan:

----- begin dump plan -----

{ON Block 0}

ON Type : SORT

Sort : 1

Distinct : 0

Sub Block : {ON Block 1}

[PL Block 0]

Method : Temp

….

Result Rows: 0.0

{ON Block 1}

ON Type : TEMP

Sub Block : {ON Block 2}

[PL Block 1]

Method : Scan

Table Name :

Type : <unknown>

{ON Block 2}

ON Type : CELL

Sub Block : {ON Block 3}

[PL Block 2]

Method : Temp

I/O Cost : 0.0

…

{ON Block 3}

ON Type : SORT

…

Sub Block : {ON Block 4}

[PL Block 3]

Method : Temp

…

{ON Block 4}

ON Type : JOIN

[PL Block 4]

Method : Join

Type : Nested Join

Factors : (3) tmsSaleTicket.SaleNo = tmsSaleTicketDet.SaleNo

….

Sub Block 1: [PL Block 5]

Sub Block 2: [PL Block 6]

[PL Block 5]

Method : Scan

Table Name : tmsSaleTicket

Scan Type : Index Scan on Index1(SaleNo)

Order : ASC

Index EQFA#: 0

Index FA# : 0

Index FACOL: Index Cost : 61

Factors : (1) tmsSaleTicket.YearMonth = '200109'

: (2) tmsSaleTicket.Dep = '1'

….

[PL Block 6]

Method : Outer Join

Type : Nested Join

Factors : <none>

….

Sub Block 1: [PL Block 7]

Sub Block 2: [PL Block 8]

[PL Block 7]

Method : Scan

Table Name : tmsSaleTicketDet

Scan Type : Index Scan on Index1(SaleNo, IDNo)

Order : ASC

Index EQFA#: 0

Index FA# : 0

Index FACOL: Index Cost : 297

Factors : <none>

I/O Cost : 3136.0

CPU Cost : 834.0

Sub Cost : 0.0

Result Rows: 33347.0

[PL Block 8]

Method : Scan

Table Name : tmsRouting

Scan Type : Index Scan on Index1(RoutNo)

Order : ASC

Index EQFA#: 1

Index FA# : 1

Index FACOL: 4

Index Cost : 2

Factors : (4) tmsSaleTicketDet.RoutNo = tmsRouting.RoutNo

….

----- end dump plan -----

這個Query乍看之初，覺得應該還好，但仔細看，其實是大有文章的。

首先在Block 6時，欲進行outer join，使用了nested join來完成，這個動作本身會產生 33571 X 1240 = 41628040筆資料，每筆資料的長度就算以50個bytes計算，這個nested join都還是要花上約2G的儲存空間來存放。實際上，DBMaker在作JOIN時，會先使用記憶體來儲放，當不夠使用時，會再去使用TMP File，這個動作看似平凡無奇，事實上，卻吃光了記憶體(因為這個nested join)，同時因為要記錄在TMP File，有了很大量的I/O動作，使得這個Query要使用20分鐘以上來執行。

其次，在Block 7中，是對RoutNo欄位索引來作Index Scan，但是Factor卻是對別的欄位作判斷( tmsSaleTicket.YearMonth = '200109'，tmsSaleTicket.Dep = '1')，所以走錯索引了。但是這個錯誤的索引搜尋，並不是整個Performance的關鍵，而是在方才的Block 6的nested join。事實上，當筆者建立了一個RoutNo index，只不過少了幾個I/O Page的動作。但若還是使用一樣的Execution Plan，產生出來的Temp就是需要這麼大的儲存空間，Index在此反而變成是末節。

不僅於此，產生的結果，還要再經過Block 4的再一次nested join，由於產生的結果集已經夠驚人，且沒有任何factor可先排除不必要的資料，只好將這整個結果才去nested join一次，這樣performance自然好不起來。

至此，DB此端能夠著力的部份已經告終，筆者只好看看是否能修改Query，使這個Query不要耗用這麼大的系統資源及時間，後來發現，在predicate的部份，其實一開始就設有條件能先行加以判斷，若能因此排除不必要的資料，那麼即使在後來join的部份，也無需花用太多資源。

怎麼說呢?筆者先用了以下的Query條件查詢：

Select count(*) from tmsSaleTicket M where M.YearMonth='200109' and M.Dep='1' ;

count(*)

===========

184

1 rows selected

在一開始若是強迫資料庫，只用這剩下的184筆資料，無論要作nested join或是sort，都不會那麼吃重(原來有1萬筆)，再說，接下來的join factor中，有一項是：M.SaleNo=D.SaleNo，其中的D就是有33571的資料，若是能將前面剩下來的184筆資料，與這三萬多筆資料，再進行結合判斷，無論是用merged join或是nested join，都會比較輕鬆。

由於DBMaker預設會將「from A , B outer C」，視作是「from A, (B outer C)」，才會造成Query Plan卡在後面要產生2G temp資料，筆者試驗將SQL改寫成「from (A,B) outer C」，產生的新Query Plan如下：

dmSQL> Select D.RoutNo as RoutNo,Routing,AirlineID,sum(Fare) as Fare,Sum(SaleAmount) as SaleAmount,count(D.IDNo) as Count

from (tmsSaleTicket M ,tmsSaleTicketDet D) outer Program.tmsRouting R

where M.YearMonth='200109' and M.Dep='1' and M.SaleNo=D.SaleNo and

D.RoutNo=R.RoutNo Group by D.RoutNo,Routing,AirlineID order by AirlineID ;

----- begin dump plan -----

{ON Block 0}

ON Type : SORT

Sub Block : {ON Block 1}

[PL Block 0]

Method : Temp

{ON Block 1}

ON Type : TEMP

Sub Block : {ON Block 2}

[PL Block 1]

Method : Scan

Table Name :

Type : <unknown>

{ON Block 2}

ON Type : CELL

Sub Block : {ON Block 3}

[PL Block 2]

Method : Temp

{ON Block 3}

ON Type : SORT

Sub Block : {ON Block 4}

[PL Block 3]

Method : Temp

{ON Block 4}

ON Type : JOIN

[PL Block 4]

Method : Outer Join

Type : Nested Join

Factors : <none>

Sub Block 1: [PL Block 5]

Sub Block 2: [PL Block 6]

[PL Block 5]

Method : Join

Type : Nested Join

Factors : <none>

Sub Block 1: [PL Block 7]

Sub Block 2: [PL Block 8]

[PL Block 7]

Method : Scan

Table Name : tmsSaleTicket

Scan Type : Index Scan on Index1(SaleNo)

Factors : (1) tmsSaleTicket.YearMonth = '200109'

: (2) tmsSaleTicket.Dep = '1'

I/O Cost : 240.0

CPU Cost : 260.1

Sub Cost : 0.0

Result Rows: 1.0

[PL Block 8]

Method : Scan

Table Name : tmsSaleTicketDet

Scan Type : Index Scan on Index1(SaleNo, IDNo)

Order : ASC

Factors : (3) tmsSaleTicket.SaleNo = tmsSaleTicketDet.SaleNo

I/O Cost : 4.0

CPU Cost : 0.3

Sub Cost : 0.0

Result Rows: 3.0

[PL Block 6]

Method : Scan

Table Name : tmsRouting

Scan Type : Index Scan on Index1(RoutNo)

Order : ASC

Index EQFA#: 1

Index FA# : 1

Index FACOL: 4

Index Cost : 2

Factors : (4) tmsSaleTicketDet.RoutNo = tmsRouting.RoutNo

I/O Cost : 3.0

CPU Cost : 0.3

Sub Cost : 0.0

Result Rows: 1.0

在Block 5中還是使用Nested Join，但是Block 7已經將原來有1萬筆的資料，排除到只剩184筆了，即使是再用Nested Join，還是少用了許多系統資源。(原來的outer join另一個table是1240筆)。產生的結果再往上去outer join，也是剩下的184筆去與另外的表格 nested join，與原來的Query不可同日而語。

這個指令的performance十分令人滿意，從原來的二十分鐘縮短到30秒，但為了怕與原始設計的邏輯不符，所以再與程式人員討論，經確認此邏輯並未違背原始設計概念，便請程式人員修改其Query。

此例只是Query 調整的一小部份，實戰中的例子十分繁雜，無法一一例舉，只要掌握住基本觀念，對於工具等能熟練，應該就能針對可能的問題，提出自己的想法與解答。對於DBA來說，這不單是一項繁重的工作，也是一項極大的挑戰，若能處理得當，調整得宜，對個人不啻也是另一項收獲及成就。