通過Snapshot Standby來精確評估SQL效能

    最近處理了一個需求，比較緊急，對映到資料庫層面是需要更新17萬id的值，聽起來是不少，根據資料架構進行了分析，發現目前是做了分庫分表的方式，所以這17萬的id在這些分庫中都可能存在，而跨部門的資料交付中，也沒有做分庫的區分，所以拿到的id是一個籠統的概念，即哪個id對應哪個分庫沒有事先過濾甄別，這個工作就自然而然的下落到了DBA頭上。
   分庫分表的方式，目前有12個分庫，是以十二個使用者的形式體現出來的，所以如果要整體更新，那麼每個分庫都需要更新一遍，有匹配的資料就更新，否則忽略。如此一來，更新的資料規模是就有幾種計算方式，一種是每個id更新對應一條sql語句，那麼語句就有17000*12=2040000條，200多萬條，規模是相當驚人了。執行期間的鎖暫且不考慮，光是執行時間在毫秒，百萬的基數也會把差距放大。還有一種思路是提供12個sql語句，每個分庫各一條sql語句，把17萬的id放入一個臨時表中，關聯更新，這種方式執行時間肯定相比單條語句要長，但是具體多多少還是未知數。
    尤其是線上系統，關鍵的業務系統，這類的操作就尤其敏感。如果有幾種方案，需要給出一個基本的分析和評定，哪種更好，有什麼準確的資料呢，主庫中是萬萬使不得，需要有把握再動手。所以我隆重推薦使用Snapshot Standby來實現這類需求，評估效能，預估影響範圍和操作時間，在完全一致的資料基礎上操作，得到的資料更加有說服力。
我想了幾個方案作為備用方案：
方法:
每個id對應1個sql，17萬sql*12個分庫

方法2:
一個臨時表，12個分庫，12個sql

方法3:
一個臨時表，12個分庫，並行執行

方法4:
每個id對應1個sql,12個分庫並行執行

這些方案在Snapshot Standby的環境中都可以輕鬆實現。主要原理是基於閃回日誌，而亮點則在於備庫可讀可寫，測試完畢之後可以繼續閃回，應用最新的資料變更。
而對於上面方案中的臨時表，我的考慮是基於外部表，因為本身要把這些資料匯入，用完之後還得刪除，熱插拔的方式更加實惠。
建立外部表的語句如下：
CREATE TABLE  test_uin
      (uin    varchar2(30)
       )
    ORGANIZATION EXTERNAL
      (TYPE ORACLE_LOADER
      DEFAULT DIRECTORY batch_query_dir
      ACCESS PARAMETERS
        (
        RECORDS DELIMITED BY NEWLINE      
        )
      LOCATION ('uin.txt')
     );

開啟備庫為Snapshot Standby
DGMGRL> convert database s2test0 to snapshot standby;
Converting database "s2test0" to a Snapshot Standby database, please wait...
Database "s2test0" converted successfully
DGMGRL> show configuration;

Configuration - test0_dg

  Protection Mode: MaxPerformance
  Databases:
    stest032 - Primary database
    stest0   - Physical standby database
    s2test0  - Snapshot standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
首先我測試了臨時表通過insert填充資料的步驟，17萬的id，用了大概5分鐘。由此可想如果是12個分庫，17萬id更新，那麼序列下來，少說也要1個小時，這個對於線上系統來說是很嚴重的延遲了。
然後我在這個備庫中進行關聯更新。
原來的語句如下:
update TEST_USER_INFO set  status=-99 where uin=?;
改為臨時表的結果集來處理。
update TEST_USER_INFO set  status=-99 where uin in (select uin from test_uin);
這種方式大概用了30秒的時間就在12個分庫順利完成，平均每個分庫大概是2秒鐘的執行效率。
如此一來有了很精確的評估，所以實施起來就會很得心應手，而在部署前，得到的臨時調整，需要修改的id變為了38萬，當然看起來資料翻了一倍，但是執行效率還是槓槓的。大概是30秒就順利完成。所以通過這種方式還是能夠很精準的分析潛在的效能問題，而對於上面逐步分析的集中測試場景，其實有了這些資料就瞭然於胸。

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