NO.30 | 2018.01.31
banner
title資訊百科 友善列印>>
Oracle PL/SQL Introduction & Performance Tuning Example_應用服務組 吳宗儒 副工程師



PL/SQL(全名:Procedural Language/SQL)是一種Oracle獨有的程式語言,主要是由Oracle資料庫對SQL語言的擴展,不僅允許嵌入SQL語言,還可以定義變量和常量,允許使用條件語句和循環語句,允許使用例外處理各種錯誤。

PL/SQL特點如下:
1.可以提高應用程式的運作性能
2.減少網路傳輸量(與其他程式語言介接都需要網路流量)
3.提高安全性(其他程式語言登入都需要帳號密碼,PL/SQL則可以不需要帳號密碼即可執行)
4.移植性不好(換了DB就無法使用)

為什麼會接觸到PL/SQL呢?主要是因為2G執照到期的任務,此任務是為了將原獲核配業者為2G的門號修改為同集團4G業者並給予未攜碼的狀態,並依照業者需求做資料恢復的日期調整,預估主要資料表要調整筆數約有700多萬筆資料以及新增相同筆數到紀錄檔中。

稍微說明一下整個程式撰寫的心路歷程,一開始主管只要求能夠在5小時內完成。但因執行效率並沒有明確需求,所以只能以限制5小時內可以完成的Java程式為主要開發工具,然而當下對於Stored Procedure並沒有任何的認知。這支程式在需求確認時,一開始是安排交由廠商執行開發作業,但是廠商對於程式上的執著,就拿著之前並不符合需求的程式作修改,一改就是兩個星期且無法提供任何有效率(執行效率大概每秒一筆)及完整執行完成的程式(執行約22萬筆資料即產生錯誤而停止)。
由於廠商效率稍差,只好抽空了兩天與同事及主管說明,除了寫了一支Java程式之外,再花一點時間學習Stored Procedure。Java程式執行時間約40分鐘,在執行效率上已有明顯進步,但是對於效能上要求的執著,開始著重在找尋可以減少一些網路連線速度、記憶體讀取寫入等影響,就借重PL/SQL的長處,再寫了一支Stored Procedure。

說明一下這支Stored Procedure的誕生,活動圖如下:
 


圖一
 
一開始是與Java相同邏輯去寫了一支,執行效率也是近40分鐘。但慢慢的會去將各個時間點拉出來看,並看看執行瓶頸有哪些。例如原本的邏輯是要將主要Table用Update的方式將資料作調整,用一個區間範圍的Update會比用For Loop Update還快,所以有加入Sequence來當作區間,而Merge方式又會比Update的速度更快。另外還可以用其他方式作到像是Update的功用,如先將資料刪除後再新增修改後的資料進去等。當然除了Update是瓶頸外,在慢慢調整的過程中會發現有許許多多可以調整效率的小地方,例如SELECT可以加入Hint以增加執行效率、或是有些資料需要依筆數作調整時可以加入一個新Table並用Trigger的方式處理可以加快速度並準確完成工作。這支程式在經過幾次調整之後,活動圖如下:
圖二
速度最快可以達到15分鐘內,且每個動作約有近千萬筆資料總計約有快3000萬筆大致上可分為幾個:
1.SQL撈出相關資料。
2.將SQL撈出的資料新增至一個TMP Table並以Trigger做資料新增前的修改。
3.將主要Table的部份資料刪除。
4.將TMP Table的資料寫入主要Table中。
5.將TMP Table的資料寫入歷史資料Table中。
 
寫這項程式很有趣,從一開始主管只要求在時間內完成工作,進步到每一個項次是不是能再加快執行速度,所學到的不僅僅是Stored Procedure,更多的是SQL以及程式的最佳化處理。但其實繼續往下看,同樣有些瓶頸可以作調整,例如刪除資料的時間過長可以再修改為CTAS的方式,或是寫入資料的時候可以再加入部份HINT或是以APPEND的方式新增等等,以有限的資源做最大的效益。追求執行速度不僅僅是寫程式的樂趣,更是一種挑戰。
這支程式最終於2018年1月20日執行完成,修改筆數約730萬筆資料,實際實行時間約19分鐘。