SQL效能之改善研究-以Oracle資料庫為例

資料庫系統上線初期，通常不會有效能上的問題，但是當系統運行了一陣子後，可能因為資料量增加或等其他因素，造成SQL的效能下降，進而造成公司業務執行遲緩或無法運作的問題。因此，如何藉由改善SQL效能，以提高資料庫服務水準，便成了值得探討的問題。本研究以台灣知名企業(A公司、P公司)之Oracle資料庫系統為研究對象，進行多個個案分析研究，研究期間為2017年7月至2017年12月。本研究案例於實際正式環境進行測試，並進行比較其SQL執行時間，調校後的SQL執行時間大幅度減少，調校效果顯著。本論文的貢獻在於透過效能調校步驟，進行SQL效能調校，並彙整SQL效能不佳的原因及建議。期望藉由實際案例，提供給系統設計者及資料庫管理者當為未來設計、開發程式及維護資料庫的案例參考。

關鍵字

SQL ；效能調校；索引

並列摘要

In the management and maintenance of the database system, the performance issue has always been the focus of attention of everyone. When the system is initially launched, it is usually not a problem of effective performance. However, when the system is running for a while, it may be due to the increase in the amount of data or Other factors, such as the decline in the effectiveness of SQL, and the resulting slow or inoperable business operations of the company. Therefore, how to improve the database service level by improving the efficiency of SQL has become a question worth exploring. This study takes the Oracle database system of a well-known Taiwanese company (A company, P company) as the research object, and the research period is from July 2017 to December 2017. The study case was tested in the actual formal environment, and the SQL execution time was compared. After the adjustment, the execution time of SQL was greatly reduced, and the adjustment effect was quite obvious. The contribution of this dissertation is to use performance tuning steps to perform SQL performance tuning and to gather the reasons and suggestions for poor SQL performance. Expectations are provided to system designers and database managers as case references for designing, developing programs, and maintaining databases in the future.