百倍效能的PL/SQL優化案例(r11筆記第13天)

我相信你是被百倍效能的字樣吸引了，不過我所想側重的是優化的思路，這個比優化技巧更重要，而結果嘛，其實我不希望說成是百倍提升，“”自黑“”一下。

    有一個真實想法和大家討論一下，就是一個SQL語句如果原本執行20秒，優化到了1秒，效能提升該說是20倍還是提高了95%。當然還見過一種說法，一條SQL語句每次執行20秒，每天執行100次，優化後每次執行1秒，執行還是100次，那麼效能提升是說成優化累計時間為100*20-100=1990秒？

好了，我們來看看PL/SQL的優化，前期自己分析了一些資訊，可以參考閃回區報警引發的效能問題分析(r11筆記第11天)

總體來說就是資料庫層面的閃回區暴增，很快就接近報警閾值。

發現其中的一個重要因素就是一個update操作時間極長，大概是4個小時，而且資源消耗巨大。

SQL_FULLTEXT
----------------------------------------------------------------------------------------------------
UPDATE CARDINFO A SET A.MAX_LEVEL = NVL((SELECT USER_CLASS FROM ROLE_CLASS_INFO B WHERE A.GROUPID =
B.GROUP_ID AND B.CN_GUID = A.ROLE_GUID), A.MAX_LEVEL) WHERE DRAWED = 'Y'而經過資料分析發現這是一個規律性的變化，是在每週二會觸發一次。經過確認這是一個scheduler JOB執行導致。而其中的關鍵就是呼叫的儲存過程。

好了，重點就是儲存過程，當然裡面的邏輯還是有一些複雜。我簡化一下。

簡單解釋一下，資料庫a的表card_new中會儲存一些禮包卡的資料，使用者啟用卡資訊之後就會插入一條記錄。而資料庫b則是一個統計資料庫，會從資料庫a中基於規則表tasklist抽取這些資料，然後在統計端基於業務需求做資訊的變更校準，資訊都在cardinfo這個表裡。規則表tasklist簡單補充一下，就好像我們的手機卡號，比如152xxxx001-152xxxx999是一個號段，裡面定義的就是這些資訊，從源庫按照這個規則抽取。

SQL> select count(*)from card_new where cardid between 'j23450010000' and 'j23500009999';  
  COUNT(*)
----------  
      5000

儲存過程的資訊大體如下

CREATE  or replace PROCEDURE        "PROC_UPDATE_CARDINFO"
AS
BEGIN
  for cur in (select * from tasklist where is_droped = 'N') loop
    MERGE INTO cardinfo a
    USING (SELECT *
             FROM card_new@tmp_link t
            WHERE t.cardid >= cur.t_start
              AND t.cardid <= cur.t_end
              ) b
    ON (a.cardid = b.cardid)
    WHEN MATCHED THEN
      UPDATE
         SET a.groupid   = b.GROUPID,
             a.role_guid = b.role_guid,
             a.drawed    = b.drawed,
             a.max_level = b.max_level
    WHEN NOT MATCHED THEN
      insert
        (cardid, groupid, role_guid, drawed, max_level)
      values
        (b.cardid, b.groupid, b.role_guid, b.drawed, b.max_level);
    COMMIT;
  end loop;

  /** 做欄位1的對映變更*/
  UPDATE cardinfo a
     SET a.used_jewel = (SELECT jewel_total
                           FROM role_costs_info b
                          WHERE b.GROUP_ID = a.groupid
                            AND b.cn_guid = a.role_guid)
   WHERE drawed = 'Y' and cardid in(select cardid from tmp_cardinfo);
  COMMIT;

  /**  做欄位2的對映變更**/
  UPDATE cardinfo a
     SET a.max_level = nvl((SELECT user_class
                             FROM role_class_info b
                            WHERE a.groupid = b.GROUP_ID
                              AND b.cn_guid = a.role_guid),
                           a.max_level)
   WHERE drawed = 'Y' and cardid in(select cardid from tmp_cardinfo);
  COMMIT;
END;
/

上面的表，除了規則表tasklist是不到1萬條資料庫（類似號段的資料），其它的資料量都在億級，所以優化空間很大，優化難度不小。

    和開發同學簡單瞭解了需求之後，我的初步結論是update的部分有待提高，因為update的部分變更都是全表更新，這個影響面較大，沒法確定增量的資料，基本上按照1周的頻率來說，增量資料應該會在百萬以內。而檢視後面幾個update的部分，發現變更的資料量都在千萬級別，效能極差。

不過在優化的過程中，感覺我似乎偏離了方向，因為目標端按照現有的條件和補充條件發現始終變更的資料量太大，都是千萬級別，和預期相去甚遠，簡單來說，按照目前的條件得到的資料不是增量資料，所以我的注意力就關注在了源頭的資料抽取上。

因為源庫的配置較好，使用了PCIE-SSD，查詢億級大表也蠻給力，我在備庫查詢了一下資料的情況。

SQL> SELECT count(t.cardid)
  2  FROM card_new t ,tasklist cur
  3  WHERE t.cardid >= cur.t_start
  4  AND t.cardid <= cur.t_end;
COUNT(T.CARDID)
---------------
      599845975

一看結果有5億多條資料，當然大家仔細看，其實語句本身也是有問題的。

其實按照邏輯抽取的資料有2億，也就是源庫表中所有的資料。

如此一來，下游的資料變更都會直接影響，導致了現在的狀況。

所以瓶頸很明顯，在兩個地方，
1.抽取的時候對線上業務有效能壓力，是全量抽取
2.更新的時候是全量更新，欄位匹配資料範圍太大

改進思路相對就很簡單了。

1. 明確增量的資料

2. 使用臨時表或者是在cardinfo中標記增量資料進行增量資料變更

3. 進行完整的資料測試，保證效能改進真實有效。

我們來逐個說一下。

1. 增量的資料，我檢視了源表的欄位,裡面有一個基於時間的欄位，看欄位的名字應該是禮品卡的啟用時間。和開發同事進行了確認，這個地方明確下來。

我們按照這樣的思路來看，增量資料大概在7萬左右。

 SQL> select count(*)from card_new where DRAWDATE>sysdate-10;
  COUNT(*)
----------
     78174

如此一來就抓住了問題的本質，後面的更新部分就可以限制條件，避免全量更新。我就建立建了一個臨時表來處理。得到從源庫抽取所得的增量資料。

2.增量資料變更優化

原本的更新是這樣的邏輯，

 UPDATE cardinfo a
     SET a.used_jewel = (SELECT jewel_total
                           FROM role_costs_info b
                          WHERE b.GROUP_ID = a.groupid
                            AND b.cn_guid = a.role_guid)
   WHERE drawed = 'Y' ;

改進之後，限制了條件，就是下面的形式

 UPDATE cardinfo a
     SET a.used_jewel = (SELECT jewel_total
                           FROM role_costs_info b
                          WHERE b.GROUP_ID = a.groupid
                            AND b.cn_guid = a.role_guid)
   WHERE drawed = 'Y' and cardid in(select cardid from tmp_cardinfo);

當然還有一些小細節處做了改進，再次先不贅述。

3.效能測試

接下來就是效能測試了，如何真實的模擬測試這個問題，11g中要充分利用Sapshot Standby的福利。

備庫切換為Snapshot Standby的方法
dgbroker中把當前的備庫設定為disable
然後使用sqlplus在備庫操作:

recover managed standby database cancel; --取消日誌應用
alter database convert to snapshot standby; --切換為Snapshot Standby
alter database open;  --切換後開啟資料庫
select database_role,open_mode from v$database;  --檢查變更是否生效

然後開始效能測試，我把資料來源指向了源庫對應的備庫，這樣對線上就沒有直接的壓力。在目標資料庫中修改儲存過程，執行測試。

SQL> exec PROC_UPDATE_CARDINFO1;
PL/SQL procedure successfully completed.
Elapsed: 00:01:04.38

原本執行至少4個小時的儲存過程現在1分鐘即可搞定。

完成測試，開始恢復備庫為Physical Standby：
sqlplus備庫:  shutdown immediate
 startup mount
 alter database convert to physical standby; --切換資料庫為physical standby
shutdown immediate --修改後資料庫為nomount，重新啟動
startup mount
select database_role,open_mode from v$database;
alter database open;
然後在主庫使用DG Broker來enable原來的備庫即可。

小結

整個一個流程走下來，讓我對這個問題的認知，從原本的閃回區報警逐步發掘，擴充套件到PL/SQL的儲存過程實現，當然這個部分還是花了些時間熟悉了下業務，為了更好的滿足優化需求，優化中尤其需要牢牢把握效能瓶頸，抓住本質，然後逐個擊破即可。而對於效能問題的測試，Snapshot Standby就是一個很不錯的補充。評估執行時間等都會更加真實有效。

最後的效能提升，從4個小時提升為1分鐘。

--------------------------

一週以後，我再次跟蹤這個問題，確認已經修復。閃回前的使用率大大降低。

而實際的SQL執行情況比預期還要好一些，原本的update語句執行需要個把小時，當前執行只需要1秒鐘。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-2130742/，如需轉載，請註明出處，否則將追究法律責任。