3. Oracle Optimizer

影響 SQL 執行的速度取決於 SQL 的執行計畫 (Execution plan) ，而 Oracle 則是依照內部的優化器 (Optimizer) 來訂定其執行計畫。 Oracle 優化器大體上可分為基於規則 (RBO,Rule-Based Optimizer) 、 基於成本(CBO,Cost-Based Optimizer) 以及選擇性 (Choose) 三大類。

Oracl e對於 SQL 執行的存取途徑 (access path) 有一套既定的規則，基於規則的優化器會根據這些規則來決定 SQL 最佳的存取途徑；基於成本的優化器則是會考慮到各項因子，例如 CPU、I/O…等，利用這些因子來計算 SQL 依不同的存取途徑所花的成本，並選擇最低成本的途徑做為此 SQL 最佳的執行方式；選擇性 (Choose) 優化器則是會以物件的統計值 (Statistic) 為基礎，若物件有統計值，則選擇基於成本的優化器來執行，反之若物件無統計值，則選擇基於規則的優化器來執行。

在 Oracle 7 之前的版本，主要採用基於規則的優化器， Oracle 8i、9i 的版本主要採用的是 Choose 優化器，而基於成本的優化器在 Oracle 10g 之後便成為主流了。我們可透過設定 optimizer_mode 參數來變更所使用的優化器類別。

Oracle Optimizer 的功能說穿了就是在決定 SQL 語法訪問 Table 的方式而已，只是 RBO 、 CBO 用來決定訪問的方式不同。 Oracle訪問Table的方式可分為全表掃描與ROWID掃描。

全表掃描採用的是一次讀取多個 data block ，依序的訪問 Table 中的每筆資料，而每次讀取 data block 的數量則是由參數 db_file_multiblock_read_count 所決定。

ROWID 指的是資料實際存放在 data block 上的物理位址， Table 中每筆資料都會由 object number + file number + block number + row number 構成一組rowid ，為64進位制，以 A~Z 、 a~z 、 0~9 、 + 號， / 號共64個字符所組成，例如某筆資料的ROWID為 AAAMAkAAEAAAAF/AAA ，第 1~6 位 AAAMAk 表示這筆資料屬於哪個 table 的 object number ；第 7~9 位 AAE 表示此筆資料存放於哪個 data file 上的 file number ；第 10~15 位 AAAAF/ 表示為 block number ，最後 AAA 三位則是為 row number 。 Oracle的索引 (Index) 實現了數據資料與數據物理位址之間的聯繫，也因此我們透過索引定位到數據實體的物理位址，由此很快的就可以得到我們想要的資料了。

• 基於規則的優化器 (RBO)

Oracle 依照訪問 Table 的方式訂定出一套排名，例如 Oracle 認為使用 ROWID 搜尋是最有效率的，因此將 ROWID 搜尋的方法排名在第一位，使用 Full Table Scan 是最沒有效率的，因此將 Full Table Scan 排名在最後一位。在使用基於規則的優化器下， Oracle 在解析 SQL 語句的時候會找出所有可能的執行方式，然後選擇排名 (Rank) 最小來當作最佳的執行方式。

例如一段SQL如下:

select First_name||' '||Last_name from employees where department_id = 20     and  employees_id between 100 and 200 order by First_name;

已知 employees 這個 table 於 department_id 欄位上有索引EMP_DEPARTMENT_IX ，同樣在 employee_id 欄位上也有索引 EMP_EMP_ID_PK ，在使用 RBO 的情況下， Oracle 會選擇何種方式來執行呢 ? 首先評估各種可能的執行方式以及其Rank :

• Full Table Scan 🡪 Rank 15

• Single-column index (使用EMP_DEPARTMENT_IX) 🡪 Rank 9

• Bounded range Search on indexed columns (使用EMP_EMP_ID_PK) 🡪 Rank 10

在基於規則的優化器下， Oracle 會選擇最小的 Rank 做為其執行的方式，因此最終會使用 EMP_DEPARTMENT_IX 這個index來做為執行的方式。

• 基於成本的優化器 (CBO)

Oracle基於成本的優化器主要是使用統計資料 (Statistic) 來估算各個執行計畫所需的成本，並選擇成本最小做為最佳的執行計畫。這些統計資料存放在資料庫的資料字典裡 (Data Dictionary) ，例如資料庫在估算一個table資料量的大小時，不是使用 "select count(*)" 這個指令去查詢，而是去尋找 USER_TABLES 裡面的 NUM_ROWS 這個欄位所記錄的比數，若 USER_TABLES 所記錄的筆數與實際上使用 count 所估算出來的筆數差距甚遠時，便有可能導致 Oracle 錯估執行計畫的成本而選擇了一個較差的執行計畫。我們可以使用 DBMS_STATS 這個系統 package 來蒐集統計信息，而在 Oracle 9i之前的版本也可以使用 analyze 這個指令來蒐集統計信息。

在設定基於成本的優化器時，總共有 ALL_Rows 、 First_Rows_N ， First_Rows 三種模式可供選擇。 ALL_Rows 表示優化器會選擇能夠在最短的時間內返回所有資料行的執行計畫； First_Rows_N 其中的N可以是 1、10、100、1000 或任何整數，這告訴優化器數據總量的哪些部分應該最先被獲取，優化器將會選擇能最快返回 N 筆資料的執行計畫； First_Rows 則是優化器會選擇能最快返回第一筆資料的執行計畫。

最簡單推算執行計畫 Cost 的方式就是去計算截取資料所需讀取 Data Block 的次數。若使用全表掃描 (Full Table Scan) ，其 Cost = (Table 總 block 數量 /  db_file_multiblock_read_count)；若使用 ROWID 掃描 (Index Scan)，其 Cost = (查詢資料筆數占總資料量的比例  *  Cluster_Factor)， Cluster_Factor 指的是使用索引完整掃描資料所需讀取的 block 數量。例如一個查詢如下 :

SELECT * FROM employees WHERE department_id = 50;

已知 department_id 欄位上有索引 EMP_DEPARTMENT_IX ，我們可由USER_TABLES 得知 employees 的 NUM_ROWS = 107，Blocks = 5 :

由 USER_INDEXES 得知 EMP_DEPARTMENT_IX 的 Clustering_Factor = 9 :

由參數檔可知 db_file_multiblock_read_count = 8 :

department_id = 50的資料筆數為 45 筆:

我們來計算看看最佳的執行計畫為何:

• Full Table Scan : Cost = 5/8 = 0.625。

• 使用EMP_DEPARTMENT_IX : Cost = (45/107 * 9) = 3.785。

由上述分析可知使用 Full Table Scan 所需的 Cost 最小，因此這個查詢的執行計畫，會使用 Full Table Scan 。

然而這種簡易的計算方式使用的是 IO cost-based ，上述範例只是用來幫助我們了解 CBO 的機制而已，實際上 Oracle 為了能夠更精準找到最佳的執行計畫， CBO 計算上考量的數據非常多，主要使用的是 CPU Model 來計算，公式如下 :

Cost = ( #SRds * sreadtim + #MRds * mredtim + #CPUCycles / cpuspeed) / sreadtim

#SRDs : 單 Block 讀取數量 (number of single block reads)

#MRDs : 多 Block 讀取數量 (number of multi block reads)

#CPUCycles : CPU 速度 (number of CPU Cycles)

sreadtim  : 單 Block 的讀取時間 (single block read time)

mreadtim  : 多 Block 的讀取時間 (multi block read time)

cpuspeed  : CPU 每秒速率 (CPU cycles per second)

這些數據可以由 sys.aux_stats$ 獲得，並且可以使用 dbms_stats.gather_system_stats 來蒐集這些數據。實際 CBO 的 Cost = IO Cost + CPU Cost ，計算上頗為複雜，這邊就不再做深入的探討。

了解優化器的運作方式及原理對於我們做 SQL Tuning 是有幫助的。

2. SQL Query Process

首先我們先來了解當執行一段 SQL 的時候， Oracle 內部是如何將這段 SQL 的結果回應出來。當 Oracle 資料庫接收到一段 SQL 的時候，會經過 Parse 、 Bind 、 Execute 與 Fetch 等階段，這些階段我們稱為 " SQL的生命週期 " 。

• Parse 階段

SQL 在 Parse 階段會進行兩個動作，其一是 SQL 語法檢查，用來確認 SQL 語句的撰寫是否正確；其二是 SQL 語意分析，用來確認 SQL 所描述的物件 (如 table 、 function …等) 是否存在，以及使用者是否有權限來存取這些物件。在檢查完 SQL 語句之後， Oracle 便會依照優化器 (Optimizer) 來決定此 SQL 最佳的執行計畫。

通常 Parse 又可分為 Soft Parse 與 Hard Parse 。 Soft Parse 指的是 Oracle 可以從內存中 (在此為 SGA 中的 Shared_Pool ) 找到相符合的 SQL 語法，無需再對此 SQL 做解析的動作，相對的，若無法從內存中找到相符合的 SQL 語法，就必須對 SQL 重新解析，這個情況我們就稱做為 Hard Parse ， SQL 在做完 Hard Parse 之後便會把此語句放入 Shared_Pool 之中。

由於 Soft Parse 無需對 SQL 語句做解析，因此會比 Hard Parse 來得有效率。

• Bind 階段

若 SQL 語句中含有 Bind Variable ，便會在此階段賦予該變數的值。

• Execute 階段

此階段會根據 Parse 階段所訂定的執行計畫來執行 SQL ，大部分的 SQL 緩慢都是慢在此 Execute 階段，也是我們常常需要進行 Tuning 的地方。

• Fetch 階段

最後 Fetch 階段則會根據 SQL 執行的結果於資料庫中擷取與返回數據，每次截取資料的多寡則是以 array 為單位，可於 SQL*PLUS 中設定 arraysize 參數進行更改，預設 arraysize = 50。

當一句 SQL 執行緩慢，我們必須分析出此 SQL 慢在哪一個階段，針對此階段的效能瓶頸做調整。

1. SQL Tuning 簡介

SQL Tuning 一向是資料庫優化的重要課題，一般來說，資料庫效能問題約有 80% 甚或至 90% 是由於 SQL 語句執行緩慢所引起的，對於 Oracle 資料庫來說，效能的調整都會基於 Time Model 來進行，所謂的 Time Model 就是一個公式 :

DB time = DB CPU time + DB wait time

資料庫運行所花費的時間 (DB Time) 等於資料庫消耗 CPU 的時間 (DB CPU Time) 加上資料庫內部等待的時間 (DB Wait Time) ，一個良好的資料庫系統，希望 DB 本身能夠完整的使用到系統的資源，也就是 DB Time 大多為 DB CPU Time 較好，消耗在資料庫本身的等待時間 (DB Wait Time) 越少越好，如果 DB Wait Time 占了絕大多數，那麼就必須檢視 Top 的等待事件，進行 Instance 層級的調整；如果 DB 大部分的時間都已經是使用到系統的資源， DB CPU Time 占了絕大多數，可是資料庫還是有緩慢的情況，這個時候就必須找出 Top SQL 來優化它，進行 SQL Tuning 不僅可以加速執行的速度，也可以減少系統 CPU 的消耗，這也就是本次所要討論的主題。

我們可以遵循下列步驟來進行 SQL Tuning :

• 確定當前系統的瓶頸

首先須先確認資料庫的瓶頸在於 DB CPU Time 或是 DB Wait Time ，如果在於 DB CPU Time ，此時就可以找出消耗資源最多的 TOP SQL 作為 Tuning 的標的。

• 測量並記錄當前性能

進行性能的測量是一項重要的工作，所有的 SQL Tuning 必須要有 Base Line 為基準，否則我們不知道 Tuning 的目標在哪裡，例如一句 SQL 跑了 5 分鐘，我們可以說跑 5 分鐘是快、還是慢嗎 ? 答案肯定是不確定的，但如果說有一句 SQL 昨天跑了 1 分鐘，但是今天卻跑了 5 分鐘，那麼當然就可以很肯定地說，它變慢了 ! 因此測量性能是一個很重要的步驟。

• 設立合理的性能目標

有了 Base Line ，那麼就可以設定 SQL Tuning 的目標，承如上例， SQL 昨天跑了 1 分鐘今天跑 5 分鐘，那麼我們就知道要把這句 SQL 調整回 1 分鐘，為什麼要設定 "合理" 的性能目標 ? 例如一句百行的 SQL 語法，中間使用了多個子查詢，其中不乏還有許多大表的串接，然後要求 DBA 必須調整到 1 秒內回應出結果，這樣子從直覺上就覺得不可能是吧 ? 因此有了 Base Line 我們才能有一個合理的目標。

• 實施 SQL Tuning

在找出了 SQL 標的以及設定完目標之後，就可以開始來調整 SQL ，對於 Oracle 來說調整 SQL 有四個大方向可以進行調整，檢視統計資訊 (Statistics) 、 調整 Index 、 建立 SQL Profile ，如果上述三招都沒用的話，那麼最後就只能修改 SQL 語句的結構，也就是改寫法。

• 測量並記錄調整結果，然後停止 Tuning

SQL Tuning 並不是一項無止盡的任務，當 SQL Tuning 的結果達到所設定的目標時，就可以停止 Tuning ，有些長官會不斷的要求 DBA 進行 Tuning ，問題是到底要 Tuning 甚麼 ? 因此設定目標以及完成目標是一項很重要的前置工作。

從上述 Tuning 的流程我們可以發現 Base Line 是一個很關鍵的東西，那麼如何定義這個 Base Line ，我們可以從過去的資訊來當作 Base Line ，首先我們可以使用系統穩定運行的一段區間為主，例如昨日早上 09:00 ~ 12:00 是系統穩定正常運行的區間，那麼就可以使用昨日的 09:00 ~ 12:00 當作 Base Line 區間；或者是同樣時段的穩定區間，例如今日週三 14:00 發生效能問題，而上週三 14:00 效能是正常的，那麼就可以使用上週三 14:00 的區間當作 Base Line 。在找出 Base Line 的區間後，接下就可以利用這區間的數據來做 Base Line ，數據來源可以是 AWR Report 、 撈取 dba_hist_sqlstat 的資訊，或者是使用官方所提供的 SQLHC 這項工具都可以找到足以作為 Tuning 的 Base Line 。

在了解 SQL Tuning 的本質與概念後，接下來就可以真正來做 SQL Tuning 了。

11. Golden Gate 簡易除錯

當 OGG 的 Group 發生問題造成 Abend 的狀態時，首先必須要檢視此 Group 相對應的 log ，這邊講的 log 就是 OGG 的 report file ，預設位在 OGG Home 底下的 dirrpt 目錄，雖然 OGG Home 底下的 ggserr.log 也會有相關資訊，但不及於 dirrpt 底下的 rpt file 來的詳細，建議有問題的時候都直接檢視 rpt file 。

OGG 有幾個常見的問題整理如下 :

• ggsci 啟動問題

在 OGG軟體安裝完成後，要進入 ggsci 時產生錯誤如下 :

./ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory

這個訊息表示 ggsci 找不到相對應的 library ，大部分都是 LD_LIBRIARY_PATH 的環境變數沒有設定，只要設定環境變數 LD_LIBRIARY_PATH 為 $ORACLE_HOME/lib 就可以了，在 AIX 的環境下則是設定 LIBPATH ， OGG 19c 之後的版本在安裝完之後 OGG Home 目錄下就會有必要的 library ，所以要再多設定 GG_HOME 這個環境變數為 OGG 的軟體安裝目錄。

• Extract 無法啟動問題

設定完一個新的 Extract Group 但是無法啟動，檢視 rpt file 發現錯誤如下 :

log 顯示 Group 的名稱與參數檔裡面的設定不相同，更正後就可以啟動。

• Extract Abend 問題之一 :

Extract 發生 Abend ，檢視 rpt file 錯誤如下 :

log 顯示 Extract 因為權限問題無法存取 Redo / Archive Log ，這個問題常常發生在 OGG 的作業系統使用者與 Oracle 軟體的使用者不同，由於 archive log 產生出來的權限為 -rw-r----- ，這個是無法更改的，若是 OGG 使用者與 Oracle 不同，那麼就會產生此問題，這個時候必須要使用 Integrated Extract 才可以避免此問題， Classic Extract 使用的是 api 解析 Redo / Archive ，無法解決權限存取上的問題。

• Extract Abend 問題之二 :

ERROR OGG-00446  Could not find archived log for sequence 301894 thread 1 under default destinations

因為缺少 archive log 所以 Extract Abend 了，這個問題容易發生在資料庫備份的時候，當 archive log 備份完成後就予以刪除，此時刪除到 Extract 還沒讀到的 archive log 就會產生 Abend ，解決方法是改用 Integrated Extract 就可以完全避免此問題，在 Integrated Extract 的狀態下，只要是 archive log 還沒有被讀到，除非使用 force 指令否則 archive log 是不能刪除的。

• Pump Abend 問題之一 :

OGG-01223  Oracle GoldenGate Capture for Oracle, dp1.prm:  TCP/IP error 111 (Connection refused)

Pump 產生了 TCP Error 大部分都是防火牆的問題，必須檢查防火牆有無開通，以及 MGR 的 Dynamic Port 設定是否正確。

• Pump Abend 問題之二 :

OGG-01201  Error reported by MGR : Access denied

這個是 OGG 12.2 之後新的安全性設定，只要 Trail File 不是放在預設路徑 ./dirdat 底下，就必須於 MGR 設定 access rule ，例如 :

ACCESSRULE, PROG *, IPADDR 192.168.49.20, ALLOW

• Replicate ORA-01403 Error :

ORA-01403: no data found 算是 Replicate 很常出現的一個錯誤，這個訊息表示要同步的資料在 Target 找不到，經常發生在 Delete 或 Update 的語法上，看到這個訊息首先檢查 Discard File (dirrpt 底下的 .dsc 檔案) :

.dsc 要看的是 Trail File 裡面是否缺少 Key Column 的數值，上述例子就是缺少 CUST_CODE 這個 Key Column 的數值，如果是這個情況表示 Source 端沒有為這個 Table 添加 supplemental log ，這個時候要回 Source 端 add trandata ，然後重新 exp 、 imp 同步這個 Table 。如果 .dsc 顯示的 Key Column 沒有缺少數值，那麼就可能不是 trandata 的問題，這個時候就是 Table 本身資料面或資料錯誤的問題，就必須要再進一步比對資料才能找出問題所在。

• Replicate 同步效能問題

Replicate 在同步的過程中 apply 的速度很慢，造成 Trail 產生的速度大於 apply 的速度，怎麼都追不完。

這個問題常常發生在 Table 沒有 Primary Key 或是 Unique Index 的情況，此時 OGG 會使用 Table 的全部欄位當 Key 來同步，如果此時 Table 沒有 Index ，那麼 Full Table Scan 的情況下， Replicate Apply 的速度自然就慢了，由於 Replicate 使用的是 row by row 逐筆的 apply ，也就是 Source 使用了一個 update 語法一次 update 100 筆資料，而 Target 在 row by row 的情況下必須做 100 次，在 Table 沒有 Index 的情況下自然就會產生 Apply 很慢的情況。

解決方法就是幫 Target 端的 Table 建立 Index 來加快 apply 的速度，最理想的情況下就是建立全部欄位的 Index ，但有時候 Table 欄位過多無法這樣建，那麼就挑 Distinct Value 最大的那幾個欄位來建 Index 吧。

OGG 除錯只要掌握一個原則，就是檢視 Report File 的錯誤訊息，針對這個錯誤訊息找出相對應的解決方案。

10. Logdump

Logdump 是一個用來檢視 Trail File 內容的工具，在 OGG Home 目錄下直接執行 logdump 就可以使用。進入 logdump 之後就可以使用 open 指令來打開想檢視的 Trail File ，例如要檢視 R1000008 這個 Trail File 的內容 :

$ ./logdump Logdump > ghdr on Logdump > detail on Logdump > open /opt/app/ogg/dirdat/R1000008 Logdump > n

ghdr on : 顯示 header 資訊。

detail on : 顯示欄位資訊。

open : 開啟 R1000008 這個 Trail File。

n (next) : 顯示下一筆資料。

開啟 Trail File 之後，一筆資料就可以看到它的表頭 (header) 與這筆資料的資訊 :

從這筆資料來看可以知道是一個 Insert 到 HR.TTT4 的操作，而且是 After Image 的資訊，左邊區塊是這筆資料的 Hex 數值，右邊是可顯示的 ASCII 格式。表頭部分有揭露此筆資料的所有屬性，說明如下 :

Hdr-Ind : 該值總是 E ，表示此 Trail File 是由 Extract 創建。

UndoFlag : 只有在 NonStop 系統上才會使用此數值，正常為 0 ，若為 1 則表示 Extract 需要撤銷已經抽取到的數據。

RecLength : Record Buffer 的大小，單位是 bytes 。

IOType : 表示此筆資料的操作類型，較常出現的有 3 – Delete 、 5 – Insert 、 10 – Update 。

TransInd : 表示此筆資料是屬於整個 Transaction 的哪一部份，總共會有四種狀態， x00 – 起始交易，這筆資料是屬於 Transaction 的第一筆紀錄 、 x01 – 這筆資料是屬於整個 Transaction 當中的某一筆紀錄 、 x02 – 結束交易，這筆資料是整個 Transaction 的最後一筆紀錄 、 x03 – 整個 Transaction 只有一筆資料時以 x03 表示。

SyskeyLen : 只有在 NonStop 系統上才會使用此數值，表示 NonStop 系統上 System Key 的長度，若沒有 System Key ，則此數值為 0 。

AuditRBA : 標示 Transaction commit 時，在交易日誌中所記錄的位址。

Continued : 只有 N 與 Y 兩個數值，如果為 Y 表示為大數據，需要多個數據區塊來組成，像是 BLOB 類型的數據，其餘則標示為 N。

Partition : 為 OGG 內部的一個標示，針對 Bulk Load 操作時，用來記錄來源端完成 Bulk Load 操作的數量，如果非 Bulk Load 操作，這個數值都會標記為 0 或 4 。

BeforeAfter : 用來標示此筆資料是屬於 Before Image 或是 After Image 。

IO Time : 用來標示交易 commit 時的時間。

OrigNode : 只有在 NonStop 系統上才會使用此數值，表示 Extract 所抽取資料的節點編號，其餘的情況則標示為 0 或 255 。

FormatType : 表示此資料是直接從資料庫中獲得 (F ， Fetch From Database) 或是從交易日誌中獲得 (R ， Readable in Transaction Log) 。

Incomplete : 此屬性從 OGG 10.4 的版本就已經不使用，保留下來是為了兼容舊版本。

AuditPos : 表示 Extract 是從 Transaction Log 中的哪一個位址把資料抽取出來。

RecCount : 表示處理 LOB 資料所使用的 Chunk 數量。

Logdump 有助於我們分析 Trail File 的內容，最簡易的方法就是直接使用 count 指令，透過 count 可以馬上知道這個 Trail File 裡面總共有多少筆資料的交易資訊 :

此外也可以透過 Filter 指令來搜尋某一個 Table 在這個 Trail File 裡面所有的交易資訊，例如搜尋 HR.TTT4 這個 Table 的所有交易 :

$ ./logdump Logdump > ghdr on Logdump > detail on Logdump > open /opt/app/ogg/dirdat/R1000008 Logdump > filter include filename HR.TTT4 Logdump > filter match all Logdump > n

使用 n (next) 就可以逐筆檢視與 HR.TTT4 相關的資料 :

透過 Logdump 檢視 Trail File 也可以用來讓 OGG 跳過這筆交易，例如從 report file 裡面顯示 Replicate Abend 在 R1000006 這個 Trail 的 rba 1415 ，如果要跳過這筆資料，那麼就可以利用 Logdump 打開 R1000006 並且定位到 1415 這個位址，然後使用 n 來檢視下一筆資料 :

$ ./logdump Logdump > ghdr on Logdump > detail on Logdump > open /opt/app/ogg/dirdat/R1000006 Logdump > pos 1415 Logdump > n

可以看到 1415 下一個 rba 號碼為 1474 ，那麼我們就可以使用 alter 指令讓 Replicate 跳過 1415 ，從 1474 這筆資料再繼續同步下去 :

GGSCI> stop replicat r_hr1 GGSCI> alter replicat r_hr1, extseqno 6, extrba 1474 GGSCI> start replicat r_hr1

OGG 在一般情況下不太會需要來檢視 Trail File 的內容，不過在處理一些棘手的問題上，使用 Logdump 還是相當有幫助的。