東吳大學商學院
資訊管理學系碩士論文
指 導 教 授 ：鄭為民 博士
TOP SQL 系統效能管理介面改善研究
Improving the System Performance Management Interface of TOP SQL
研 究 生：林姿君 撰
中 華 民 國 一 ○ 二 年 十 一 月
TOP SQL 系統效能管理介面改善研究
Improving the System Performance Management Interface of TOP SQL
研究生：林姿君 Student：Tzu-Chun Lin 指導教授：鄭為民 Advisor：Wei-Min Jeng
東吳大學商學院 資訊管理學系
碩士論文
A Thesis
Submitted to Department of Computer Science and Information Management School of Business
Soochow University
Dec 2013
Taipei, Taiwan, Republic of China
中華民國一○二年十一月
i
誌 謝
當我還在考慮要報考哪一所研究所時，政倫學長就力薦東吳資管及鄭為民教授，並 在我確定考上時，陪我到學校找教授會談。因此，能完成研究所的學業，要感謝政倫學 長的介紹，讓我能在鄭教授悉心的指導下完成論文，取得學位。更要感謝鄭教授在我研 究過程遇到瓶頸時多次的提點，讓資質駑鈍的我也能完成論文，以及口試委員余銘忠教 授及鄭武德教授惠賜論文建議。
在工作及家庭都要兼顧的情形下，還能夠上課修得學分都要歸功我的婆婆及先生，
幫我照顧小孩的生活及功課，讓我能夠有多一些時間於課業上。也要感謝鼓勵及激勵我 的同事們，由於你們時常的關心，讓我不敢忘記學業尚未完成，論文仍須努力。
還要感謝一起修課的同學怡錚、啟豪及耿銘，能在課業上一同努力，相互扶持，這 段求學時光讓我感覺非常開心。尤其是怡錚，是我分組報告的好伙伴。能重新踏入校園，
能認識這些好同學、好老師是我的福份，也要感謝老天爺賜給我這一切。
林姿君 謹誌 2 0 1 3 . 1 1 . 2 8
ii
摘 要
在資訊科技高度利用的時代，掌握即時正確的資訊才能擁有先機，而龐大的資料收 錄在資料庫中，須做有效的管理與運用，才能突顯資訊的價值。每個現代的資訊系統都 建立資料庫來儲存及管理所需的資料，資料庫可謂為資訊系統的重要核心，在龐大的資 料中如何快速的存取資料，一直是使用者關心的議題，基於對於系統的回應時間越加要 求，資料庫的效能必須時時維持在良好的情況，才能保持系統整體工作效率。
基於改善效能的目標，各家資料庫系統的廠商紛紛提出監控效能的管理工具以及效 能調校工具，以方便資料庫管理者進行系統調校作業，例如：Microsoft SQL Server、Oracle。
也有第三方(Third-Party)廠商提出方便的工具，例如：Quest Software Toad、Embarcadero 等等。在效能出現瓶頸時多半只能即時監控及記錄資訊，但缺乏主動提供建議之機能，
一旦錯失發生的時機，便無法發揮有效監督的作用。
本研究利用資料庫內的系統紀錄發展出一套簡單、實用、低成本的 TOP SQL 使用 者介面，以耗費系統資源 CPU Time、磁碟 I/O 指標來分析，並根據選定指標進而從中 找出哪個 SQL 語句最需要調優，以達到有效監控進而改善整體系統運作效能。
關鍵詞：TOP SQL、資料庫效能
iii
Abstract
Upon the heavy use of information technology today, getting hold of the real-time accurate information is the only mean to any possible success. In order to maximize the add-on value of information, the huge amount of data has to be effectively managed with the current data base system. Nearly every application system nowadays depends on a reliable data base sys- tem to provide the critical information it needs. The way regarding how to efficiently access the information remains the issue that most users are concerned. Due to the need of an satis- factory respond time from a data base system, the performance of a good database system has to be maintain in its best operating condition to sustain the running of the overall application.
Because of the goal in improving the performance of the system, most of the database system vendors offer monitoring tools or utilities to fine tune the underlying system. Well-known examples such as Quest Software Toad and Embarcadero are suggested by Microsoft SQL Server、Oracle、or even the third-party manufacturers for the same purpose. However, most of the products only passively provide the monitoring statistics without actively providing the helpful advice to the management people. Therefore, the tools are limited in effectively help the system management at the critical time.
This study aims to design an easy and effective tool and the corresponding user interface for the system management people. Practical and lost-cost solution is offered by pinpointing the TOP SQL statements for the users. Top performance indicators such as CPU Time, Disk I/O, etc. are used to monitor the performance of the targeted problematic statements in order to efficiently monitor and improve the system performance.
Keyword：TOP SQL、Database Performance
iv
目 錄
誌 謝 ... i
摘 要 ... ii
Abstract ... iii
目 錄 ... iv
圖目錄 ... vi
1. 緒論 ... 1
1.1. 研究動機 ... 1
1.2. 研究目的 ... 3
2. 資料庫效能 ... 5
3. TOP SQL 效能管理工具 ... 8
3.1. Oracle SQL Performance Analyzer ... 8
3.2. Quest Foglight Performance Analysis ... 9
3.3. Embarcadero DB Optimizer ... 10
4. 研究方法 ... 11
4.1. 系統架構 ... 11
4.2. 系統實作 ... 13
5. 個案討論 ... 16
5.1. 個案環境 ... 16
5.2. 個案問題分析 ... 18
v
6. 結論與建議 ... 19
6.1. 研究結論 ... 19
6.2. 未來發展方向 ... 19
參考文獻 ... 21
附 錄 ... 22
vi
圖目錄
圖 1 造成非計劃停機是何種性能問題 ... 3
圖 2 Oracle 效能管理工具介面 ... 8
圖 3 Quest Foglight TOP SQL 介面 ... 9
圖 4 Embarcadero DB Optimizer 介面 ... 10
圖 5 系統架構圖 ... 12
圖 6. TOP SQL 追蹤分析方法 ... 15
圖 7 實驗環境架構圖 ... 17
1
1. 緒論
1.1. 研究動機
在資訊科技的時代，掌握資訊才能擁有先機，而龐大的資料收錄在資料庫中，
要做有效的管理與運用，才能突顯資訊的價值。因此，每個資訊系統都建立資料 庫來儲存及管理所需的資料，資料庫可謂為資訊系統的重要核心，在龐大的資料 中如何快速的提取資料，一直是使用者關心的議題，因為科技的不斷進步，使得 大家對於系統的回應時間越加要求，因此資料庫的效能必須時時維持在良好的情 況，才能保持系統整體工作效率。
一套應用系統開發完成上線使用了一段時間，在工作負載未有大幅增加及環境 設定不變的情形下，通常可以良好運行一段時間，不須進行效能的調校。直到使 用者增加或是資料量成長到某種程度時，系統效能就可能出現瓶頸。不過以現在 資料庫的技術而言，資料量在 500GB 以下是不會有太大問題的。因此，資料庫在 系統運行穩定後，是不須要經常調整的。
當系統發生反應速度變慢時，有些經驗不足的系統管理者在找不出問題的根源 時，直覺的認為提升主機等級或增加記憶體便可以改善效能，但當硬體提升後問 題仍存在，這才是大問題，就如同很多人所說「花錢能解決的事情都是小事情，
花錢卻解決不了，那才是大事」。因此系統效能變差時，所有環節都要進行檢測，
若排除硬體及網路相關的問題，最後便是資料庫與應用程式的問題了。某些企業 會有專職的資料庫管理員(DBA)來管理資料庫，而應用程式由程式設計師進行系統 開發或維護，表面上來看兩方各司其職，可以發揮彼此的專長，然而當問題的癥
2
結落在資料庫或應用程式，這時 DBA 跟程式設計師可能因各自立場不同而互踢皮 球，無法合力解決問題，那麼問題就不能早一點被解決。
根據市場研究機構 Unisphere 在 2011 年對 Oracle 產品與技術用戶的調查 (《Managing the Rapid Rise in Database Growth: 2011 IOUG Survey on Database Manageability》[1]指出，因為效能問題導致停機的狀況中，60%為 SQL（Structured Query Language）語法的調優(圖 1)。可見，SQL 影響效能的程度不能小覷。對於 一些程式設計師而言，著重於資料存取及處理結果的正確，忽略資料存取的效率，
這點往往是埋下日後系統出現瓶頸的地雷。因為系統開發時，沒有大量資料可以 測試，程式設計師僅用少許的幾筆資料來測試程式邏輯，無法測出潛在的問題。
日後當系統運行一段時間後，資料量到達一定的數量，問題就可能顯現出來了。
現行常見的資料庫多數有自家的效能監控工具和 SQL 優化工具，例如：Oracle 、 SQL Server、DB2 UDB，且市面上也有不少資料庫管理工具，例如：Quest Toad、
Embarcadero。使用這些工具對於資料庫管理確實有很大的幫助，但其取得的成本 高。對於一些小企業，在經費的考量下購置的資料庫也許就沒有包含效能監控工 具可以使用。
3
圖 1 造成非計劃停機是何種性能問題
資料來源：2011 IOUG Survey on Database Manageability
1.2. 研究目的
在系統效能監控工具中強調 SQL 語句在某段時間區間佔用了多少系統資源，
並根據 CPU Time 排序 TOP SQL，以幫助資料庫管理者針對 TOP SQL 做調校。資 料庫管理者分析這個 SQL 語句、改寫、加上必要的索引、測試，以新的 SQL 語句 取代了原來有問題的 SQL 語句執行。但為什麼系統還是出現效能不佳的情形？原 因出在單從系統資源的觀點來解決問題，而忽略了其他需要考慮的重要因素，而
4
出現的盲點。例如，一個查詢消耗了很多系統資源，可是他一天才執行一次而且 都是在凌晨 2~4 點完成，那麼針對這個查詢我們去做調校，對整體系統效能不會 有太大的提昇。另一個情況，如果從系統資源消耗的程度來看，這個查詢並不會 消耗太多系統資源，可是頻繁的執行這個查詢，也會使得系統整體效能受到影響。
因此，除了系統資源的數據可以參酌，跟效能最相關的莫過於查詢 SQL 語句 的寫法及相關索引是否建立，此時可能還需要查詢計畫成本來了解是否還有更好 的方法來改善效能。
不同的資料庫系統所提出的介面是各式各樣的，除了系統資源的資訊外，會 有約略的不同，那麼資料庫管理者還需要哪些重要的訊息，能供其判斷哪個 SQL 語句有改善的必要。若能一併將判斷問題查詢語句的資訊整合，以系統資源的消 耗為排序，就是本研究的目的。因此本研究將發展出一套簡單、實用、低成本的 TOP SQL 使用者介面，藉由記錄下來的 SQL Statement 運行狀況，從中剖析效能瓶 頸的問題，找出可能的問題 SQL，必要時從 TOP SQL 中選擇進行長時間的觀察。
效能數據化的呈現可作為資料庫管理者與程式設計師共同解決問題的輔助工具。
5
2. 資料庫效能
資料庫效能問題已經有很多研究，效能問題可分為資源的使用、資料存放設 計及 SQL 語法設計。系統的資源是有限的，當資源用盡時，系統便無法運作，因 此提升資源使用效率也是提升資料庫的效能。透過演算法改良資料於記憶體的命 中率是方法之一，不過演算法的研究會花費太多成本，而不一定有很大的成效。
DBMS 將資料從輔助儲存體(SS)經由記憶體層次結構(memory hierarchy) 分別包括 I/O、 主記憶體、 L2 cache 、L1 cache，送到處理器執行， DB 效能研究大部份 著重在 SS 跟 L2 cache 之間, 稱為「Smart data storage techniques」[5]。近年來記憶 體取得的代價越來越低，因此乾脆把所有的資料存放在記憶體上，不用再到磁碟 搜尋資料，In-Memory Database 油然而生[10]。因為將所有的資料載入到記憶體上，
資料查詢速度快為其主要優點，但現今的資料量動輒好幾 TB，遇到如此龐大的資 料量時，In-Memory Database 並不適用。另一種與資源問題，是發生死結(deadlock) 兩程序互搶佔資源的情形，不過現在的資料庫系統針對死結的情形都有其解決的 演算法。
在資料存放設計方面，現行還是以關連式資料庫為普遍使用的型式，其資料 存取的架構以表格(Table)、索引(index)、視圖(view)…等。資料表格的設計需要經 過正規化[6]，然後加入適當的索引。資料正規化、資料關連及索引選擇問題(Index Selection problem-ISP)是攸關資料庫效能的因素之一。其中索引選擇問題有較多的 討論，其包含的因素有工作負載、資料庫中的屬性、最大允許的磁碟空間、所需 的目標效能。一個表格建立太多的索引並非好事，會增加磁碟空間及異動的時間，
甚至多個索引同時存在會產生索引交互作用，因此索引需要最適選擇，許多研究 針對索引的選擇與調校，提出許多理論與作法。[3]提出因為多個索引同時存在, 導
6
致效能改變的狀況, 稱之索引交互作用，為了考量索引交互作用,也將會造成最佳 解的搜尋空間非常巨大。
分割表格(partition table)技術概念，是把一個大的表格依某欄位值切割成 n 個 小表格，這樣查詢的時候可以很快定位到某一小表格上。另一方面不同的小表格 儲存在不同一個硬碟或是 RAID，同時去讀寫不同的硬碟，也可以提高讀取效率。
資料庫為維持資料的一致性，以鎖(lock)來進行並發控制(Concurrency control)，因 此當資料存取的方式及流程錯誤，嚴重者可能發生阻塞(block)，被阻塞的程序，前 端使用者苦等不到回應，系統如同當機。因此，查詢的相互作用也是影響效能的 重要因素[4]。在過去的幾年已經出現一些從 SQL 負載訊息的分析發展，這些發展 能理解使用者及應用程序的行為[8]。
SQL 語法設計中不同寫法的 SQL 語句可以產生相同的結果，但不同的 SQL 語句執行所產生的成本卻不相同。等效的搜尋參數可參考下列幾項：(1)between 轉 換為 >= and <= 子句(2)如果為 like “sm%”可轉換成>=”sm” and <”sn”，但如果第一 個字為通用符號like “%x”，則無法使用索引(3)in(values_list)轉換為 or 查詢，例如 int_c in(1,2,3)變為 int_c =1 or int_c =2 or int_c =3。
在[11]所表示工作負荷分析儀由兩部分組成：SQL 分析器分析 SQL 語句和目 錄(catalog)資訊。目錄資訊在 SQL 語句的分析有其重要性。因為資料庫系統在進 行最佳化時會使用查詢中指定的表、索引等相關統計訊息來評估查詢成本，如果 統計訊息不精準時，成本估計就可能會不準確了。有些統計訊息(例如 page 數或某 個表的資料筆數)在查詢處理過程中會被更新。其他的統計訊息只有在進行 update statistics 或建立索引時才會被更新。因此，統計訊息是否定期更新也關係著查詢效 能。
7
在查詢處理器(query processor)中 SQL 語句處理主要分為四個階段[7]：分析 (parsing)，優化(optimization)，產生代碼(code generation)，和執行(execution)。每個 SQL 語句發送到解析器檢查語法並將 SQL 語句轉變成”查詢樹”的內部表示方式。
之後以查詢最佳化器(query optimizer)找到相關統計資料和訪問路徑(access path)找 出可能存在的操作組合，依據最小查詢成本選擇訪問路徑產生一個執行計畫 (execution plan)，最後資料庫系統依照此計畫執行。執行計畫的選擇關係著查詢效 能，在某些情況下，查詢處理器不選擇最好的計畫，只選擇次計畫，是需要權衡 成本與效能的。
因此經由系統效能分析工具擷取資料庫目錄資訊、工作負載記錄及 SQL 並分 析訊息，將資訊整合利用排序，TOP SQL 透過使用者介面呈現出來，即可讓資料 庫管理者了解 SQL 語句在系統的狀況。效能不好的 SQL 語句再透過 Tuning 工具 找出解決方案。如此一來，以加速系統調校的時間。
8
3. TOP SQL 效能管理工具
3.1. Oracle SQL Performance Analyzer
圖 2 Oracle 效能管理工具介面
在 Oracle 的 SQL Performance Analyzer(SPA)架構裡稱為 SQL Tuning Set [9]，
SQL Tuning Set 是一個資料庫物件(Object)，可以捕捉或儲存使用者或應用程式所 執行的 SQL 語句內容、解析可以編譯的 SQL 語句、實際載入數值用於執行 SQL 語句、以及其執行計劃和執行統計數據，如被執行的 SQL 語句的次數。系統效能 分析方面，在預設情況下，SPA 使用所經過的時間(elapsed time)作為度量進行比較。
還可以選擇從各種 SQL 運行時統計資訊，包括 CPU 時間、I/O 時間、緩衝區獲取、
9
磁碟讀取、磁碟寫入或任意組合（例如，CPU 時間 + 10 *緩衝區獲取）。該模組 還比較在兩次的 SQL 執行計畫的結構變化。這些測量資料透過 SPA 報告呈現給 使用者。負值表示效能退化，正值顯示性能良好。SPA 使用 SQL 執行頻率權衡 SQL 語句在工作負荷中的重要性。這能讓使用者能夠正確地確認對長時間運行的 SQL 語句執行是僅有幾次或哪些執行經過時間非常快，但重複執行的語句。
3.2. Quest Foglight Performance Analysis
圖 3 Quest Foglight TOP SQL 介面
Quest Foglight Performance Analysis 是一個具有即時監控與歷史資料分析的 資料庫效能分析工具，其獨有的 StealthCollect 資料收集技術，只佔用資料庫主機 少量的資源，將收集到資料庫效能資料存放於另一台機器，在 Client 端將資料庫 目前狀況以圖形介面即時呈現，也可分析資料庫效能歷史資料並排程產出報表以
10
email 方式通知 DBA。 目前已支援的資料庫有 Oracle、MS SQL Server 與 IBM DB2 三種。
3.3. Embarcadero DB Optimizer
圖 4 Embarcadero DB Optimizer 介面
Embarcadero DB Optimizer 具有多個功能，簡化 SQL 優化和部署，可以提高工作效 率，並減少錯誤。包含語句優化、資料來源剖析、代碼完成、即時錯誤檢查等，
主要的三個介面元件如下：
SQL 剖析器：顯示最常見的 SQL、事件和會話活動。
SQL 優化器：提供一種簡單、最佳的方法來發現高效的路徑查詢。通過優化器 可以檢測和修改資料檢索中使用的執行路徑來優化性能不佳的 SQL 指令。
SQL 編輯器：通過利用提高工作效率並減少錯誤的功能來簡化 SQL 開發過程。
11
4. 研究方法
4.1. 系統架構
本研究利用 sybase 在 ASE 12.5.0.3 版本之後推出的監測數據訪問表 Monitoring Data Access Table[12]，簡稱 MDA Table 或 Monitor Table，作為資料來源之一，其 特點是能提供資料庫目前的運行狀況及有關效能調校的參考數據，利用標準的 SQL select 指令存取資訊。MDA Table 為一系列的表格，方便執行查詢指令。以環 形緩衝區(ring buffers)的方式儲存，不會佔用太多資料庫儲存空間。但也是因為資 料的儲存並非持久的，若非即時查看則會錯失檢測效能的良機，因此需要將資料 定時的截取出來，另外存放數據資料以便日後觀察使用。因為頻繁的截取資料，
利用預儲程序(store procedure)執行效率高，便於外部呼叫的優點，來處理資料庫內 部資料。並撰寫 shell script 及利用作業系統的排程(crontab)，每 5 分鐘截取資料 1 次，為避免存放數據資料的 Table 過大，也要設定排程定期清除部分歷史資料。而 前端介面以 JavaScript、PHP 來呈現，系統架構如圖 5 所示。
「預儲程序」[2]是一種在資料庫中儲存複雜程式，以便外部程式呼叫的一種 資料庫物件，它可以視為資料庫中的一種函式或副程式。其優點為預儲程序可以提 昇執行效率、模組化設計、降低網路負載、封裝並隱藏複雜的商業邏輯、可以回傳 值及接受參數。缺點為預儲程序往往客製化於特定的資料庫上，因為支援的程式語 言不同。當切換到其他廠商的資料庫系統時，需要重寫原有的預儲程序。預儲程序 的效能調校與撰寫，受限於各種資料庫系統。
12
「shell script」類似早期 DOS 作業系統的批次檔(.bat)，使用純文字檔將一些 shell 的語法與指令(含外部指令)寫在裡面，搭配正規表示法、管線命令與資料流重 導向等功能，以達到我們想要的處理目的。
圖 5 系統架構圖
13
4.2. 系統實作
MDA Table 並未在 sybase ASE 安裝時內建在裡面，因此在使用前必須先行安裝 並設定參數，其安裝步驟為(1)確認 ASE 為 12.5.0.3 以上版本。(2)系統設定。(3)安 裝 MDA Table。(4) 設定使用者權限。(5)啟動監控及相關參數設定。相關安裝系統 設定及指令請參考附錄。
安裝好 MDA Table 並啟動監控後，監控資料即可從 MDA Table 中查詢得到，
但為了分析效能需要一段時間的監控資料，因此利用作業系統的排程程式及在資料 庫中建立一預儲程序紀錄監控資訊。主要紀錄的資訊從下列 3 個表格中截取：
monProcessStatement：提供有關當前執行的語句，相關的欄位有 SPID、CPUTime、
WaitTime、PhysicalReads、LogicalReads。
monProcessSQLText：提供當前正在執行的 SQL 內容，相關的欄位有 SPID、
SequenceInLine、SQLText。
sysprocesses：提供 Adaptive Server 進程的訊息，由使用者查詢時動態構建，相 關的欄位有 spid、status、suid、cmd、blocked、dbid、ipaddr。
系統管理者若未能從系統開始運作起即定期紀錄系統資源的使用狀態，是很難 區分系統資源使用至多少百分比時視為異常狀況。因此本研究運用標準差來設定門 檻值，若超過門檻值則視此 SQL 效能不彰，列入 TOP SQL 中。
「標準差」是一種表示離散程度的統計觀念，可以當作不確定性的一種測量。
標準差是一組數值自平均數分散開來程度的一種測量觀念，一個較大的標準差代表 大部分的數值和其平均值之間差異較大，較小的標準差則代表這些數值皆接近平均 值。例如：兩組數的集合{1,3,5,7,9}和{5,5,5,5,5}其平均值皆為 5，但第二組具有較 小的標準差。
14
假設有一組數值X1,…,Xn 其平均值為公式(1)，標準差為公式(2)：，
啟動監控後先保留數日資料，針對其中一日的紀錄避開夜間批次作業時段以免 產生太多誤差，計算指標 CPU Time 及 PhysicalReads 的標準差，設定門檻值，之後 監控資料即以此門檻值為準則。系統若調校後，其門檻值應適時重新產生，才能更 準確。
當前端使用者介面程式呼叫後端資料時產生圖形報表提供使用者分析，使用者 可於前端設定門檻值，然後指定欲觀察的時段及以何種指標排序，此時段區間的選 擇最好也排除系統批次作業的時間，例如：完整備份(Full Backup)、更新統計資料 (Update statistics)，這些動作是需要耗費相當多 I/O 的，如果含蓋在分析資料中，會 影響分析結果。
使用者介面先以曲線圖得知資源耗用趨勢，如圖 6 (a)以 CPU Time 為排序方式 得知此一時間區間有 2 次高峰，並產生 TOP SQL 列表如圖 6 (b)，為了解 SQL Text 歷次執行的狀態，擇一追蹤。若此 SQL Text 在這段時間執行多次且每次皆耗用大量 的 CPU Time 則應對此 SQL 進行更進一步的分析，可從成本(cost)及執行計劃 (showplan)著手。但如果是如圖 6 (c)同樣的查詢語法，可是耗用的 CPU Time 相差超 過一個標準差，這時應試著找出其他影響的因素，持續追蹤觀察。
15
(a)CPU Time 曲線圖
(b)TOP SQL 以 CPU Time 排序最高 2 筆
(c)追蹤指定之 SQL Text 效能
圖 6. TOP SQL 追蹤分析方法
16
5. 個案討論
5.1. 個案環境
以某政府機關對外服務系統之資料庫為例，其資料庫供應兩主要系統之使用，
其一為便民之線上申辦作業系統，主要提供近 10 項申辦業務讓民眾透過網路進行 申請，當中有 1 項申請是需要經過人工審核且親至櫃台領取，其餘項目為系統審核 並核發，核可者可自行下載列印。另一為提供其他公務機關查詢資料使用。系統架 構如圖 7 所示。
＊網頁伺服器 IBM x365：Microsoft Windows 2000 Server、Web server：Microsoft Windows IIS 5.0、AP server: JBoss 3.4。
＊應用程式伺服器 IBM x365：AIX 5L 5.1、Websphere application server 5。
＊中介平台伺服器 IBM x365：Microsoft Windows 2000 Server、Microsoft SQL Server 2000、AP server: Apache Tomcat 4.1
＊資料庫伺服器：IBM p560(CPU*8、8GB)、作業系統 AIX 5L 5.2、資料庫：Sybase 12.5.1。
17
圖 7 實驗環境架構圖
18
5.2. 個案問題分析
系統上線 3 年後，線上申辦之後台審核人員反應系統回應速度遲緩，每次查 詢回應時間皆超過 2 分鐘，甚至超過等待時間，以致於無法負荷每日申請數量，
個案系統管理人員及維護廠商從網路及主機效能工具無所發現，因此將焦點放在 資料庫的效能不佳。一來認為主機等級不敷使用，需要升級資料庫主機；二來緊 急的措施是減少此作業的 Table 資料量，理由是系統初期資料量少時並無問題。
於是，一方面將此作業的歷史資料放置另一 Table，另一方面將資料庫主機升級，
但沒想到經過半年的時間，同樣的問題又出現了，其實這都是因為個案系統管理 人員經驗不足，不夠了解系統而採取治標不治本的處理方式。
此作業所使用的表格因處理流程分為 3 個 Table 存放，最大筆數約為 160 萬 筆，查詢時會 join 3 個 Table 的資料。以大型資料庫來看，此一資料量並不算太 大，3 個 Table 的每筆資料長度為 175byte、103byte 及 80byte。因此，利用本研 究方法進行一段時間的記錄，分別以 CPU Time 及 Physical Reads 指標觀察。從 Physical Reads 指標發現 2 點問題：(1)以身分證字號等可識別個人的資料不應以
「Like %ID%」為查詢條件且應建立索引。(2)Table_1 為申請人線上填寫的資料 與 Table_2 審核人員審核紀錄 為 1 對 1 的關連，實不必拆成 2 個 Table。
因變動 Table 的設計較花時間變更，因此先變更查詢語法，去除 Like 的查詢 語法並建立相關索引後，使用者即明顯感受到系統回應時間，從超過 2 分鐘變成 小於 30 秒。
19
6. 結論與建議
6.1. 研究結論
資料庫為系統的資料核心，存取資料的便利是其存在的重要價值，資料庫效能 深深影響著系統的反應時間，因此日常的檢查及監控機制是必須建立的。本研究利 用資料庫內建的效能監控數據資料，運用自動的方式將資料另存起來，以 CPU Time 或 PhysicalReads 做為消耗資源的指標，利用指標的標準差先檢驗 SQL Statement 執 行效能的優劣，再將執行效能不佳的 SQL Statement 進行排序，列出最消耗資源的 TOP SQL，可幫助使用者找出問題 SQL。為避免觀察的取樣值過少，或是同一 SQL Statement 消耗的資源有較大的標準差，可能會導致誤判，因此針對該 SQL Statement 應持續追踪，較能客觀判斷。將本研究應用在個案中，幫助找出效能不彰的 SQL Statement，調校後可提昇資料庫效能，不僅可節省硬體升級的費用，更可徹底的解 決查詢回應時間變慢的問題。
本研究以 Sybase ASE 為資料庫，是一般系統使用上較為少見的資料庫，相對的 效能監控工具也較少，因此運用資料庫本身提供的效能監控相關資訊，再利用圖形 介面呈現及資料的整理分析，也可以讓使用者以低成本又簡易的方式監控資料庫。
6.2. 未來發展方向
本研究所使用的 Sybase 資料庫為 12.5.1 版本，此版本的資料庫針對動態產生的 SQL (Dynamic SQL)無法呈現 SQL Statement，因此無法進行分析。但在 sybase ASE 15.0 之後的版本即可記錄動態產生的 SQL Statement。另外，還有一個稱為查詢處理 (QP)指標，QP 指標能確認並比較查詢執行過程中的實驗計量值。當執行一個查詢時，
20
查詢會與一組定義的指標關聯，這組指標是以 QP 指標進行比較的基礎。內容包括：
CPU 運行時間、經過時間、邏輯 IO、實體 IO、計數…等。QP 指標和 MDA Table 的資訊是可以相互對照的，兩者的不同在於 QP 指標可以蒐集關於查詢的歷史數據 並儲存於系統目錄中，而 MDA Table 中的訊息是暫存的。QP 指標所產生的訊息可 以用來了解可能引發問題查詢的訊息，例如：查詢的性能衰退、一批正在運行的查 詢中花費成本最大的查詢、最常運行的查詢。有了這些訊息，便可以對這些查詢進 行調校以提高效率，因此未來可使用 Sybase ASE 15 版本的資料庫進行設定，將會 有更完整的效能監控訊息。
21
參考文獻
[1] Joseph McKendrick, R.A., Managing the Rapid Rise in Database Growth: 2011 IOUG Survey on Database Manageability. 2011.
[2] 維基百科. 預儲程序. 2013; Available from:
http://zh.wikipedia.org/wiki/%E5%AD%98%E5%82%A8%E7%A8%8B%E5%B A%8F.
[3] 黃建智, PostgreSQL 資料庫伺服器上的索引調校工具. 2009.
[4] Ahmad, M., A. Aboulnaga, and S. Babu. Query interactions in database workloads. in Proceedings of the Second International Workshop on Testing Database Systems. 2009. ACM.
[5] Baykan, E., Recent Research on Database System Performance. Jun, 2005. 28: p.
1-9.
[6] Beeri, C., P.A. Bernstein, and N. Goodman. A sophisticate's introduction to database normalization theory. in Proceedings of the fourth international conference on Very Large Data Bases-Volume 4. 1978. VLDB Endowment.
[7] D.D., C., et al. Access Path Selection in a Relational Database Management System. in SIGMOD '79 Proceedings of the 1979 ACM SIGMOD international conference on Management of data. 1979.
[8] Elnaffar, S., A Methodology for Auto-Recognizing DBMS Workloads, in Proceedings of CASCON'022002.
[9] K. Yagoub, P.B., B. Dageville, K. Dias, S. Joshi, and H. YuPete, Oracle’s SQL Performance Analyzer, in DEB2008.
[10] Lindström, J., et al., IBM solidDB: In-Memory Database Optimized for Extreme Speed and Availability. Data Engineering, 2013: p. 14.
[11] PS. Yu, M.C., HU Heiss and Sukho Lee, On Workload Characterization of Relation Database Environments, in Software Engineering,IEEE Transactions on1992. p. 347-355.
[12] sybase Sybooks Online:Monitoring Remote Servers Using MDA Tables. 2006.
22
附 錄
安裝 MDA Table 的系統設定
增加 loopback server 至 $SYBASE/interfaces 進入 isql 執行下列指令
sp_configure ‘enable cis’,1 go
sp_addserver loopback,null,@@servername go
set cis_rpc_handling on go
安裝 MDA Table 指令
cd $SYBASE/$SYBASE_ASE/scripts
isql –Usa –Ppassword –Sserver_name –iinstallmontables
設定使用者權限
grant role mon_role to sa
go
啟動監控及相關參數設定
sp_configure "enable monitoring", 1 (設 0 為關閉監控)
23
sp_configure "sql text pipe active", 1 (是否收集 SQL text)
sp_configure "sql text pipe max messages", 2000 (指定儲存 SQL text 訊息的數量)
sp_configure "statement pipe active", 1 (是否收集 statement-level 的統計)
sp_configure "statement pipe max messages", 5000 (指定儲存 statement 統計訊息的數量)
sp_configure "SQL batch capture", 1
(和 max SQL text monitored 一起使用可以收集每個 user 任務的每個 批次 SQL text)
sp_configure "max SQL text monitored", 4096 (指定記憶體大小給 monitor)
