ORACLE 9i資料庫優化案例(4) --- 索引改善UPDATE

junsansi發表於2010-12-08
原文網址 : http://blog.itpub.net/7607759/viewspace-681170/
Oracle資料庫優化索引

C>. 索引對於update操作也很重要

接下來是條UPDATE語句：

    CPU Elapsd

    Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value

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

    98,751,515 14,404 6,855.8 7.4 2176.33 2182.08 3309920380

    update T_WIKI_DOC_LOCK set DOC_LOCK_USER_ID=:1,DOC_LOCK_START_TI

    ME=to_date(:2,¨yyyy-mm-dd hh24:mi:ss¨),DOC_LOCK_HEARTBEAT_TIME=t

    o_date(:3,¨yyyy-mm-dd hh24:mi:ss¨),DOC_LOCK_STATE=:4,DOC_LOCK_US

    ER_ID_ENCRYPT=:5,DOC_ID_ENCRYPT=:6,DOC_LOCK_USER_IP=:7,DOC_TITLE

    =:8,DOC_USER_ID_EN=:9,DOC_USER_NICK=:10 where DOC_ID=:11

該語句一小時內執行1萬5千餘次，每小時佔用CPU時間2176.33s，產生邏輯讀近億次，耗費大量系統資源，想想不應該啊，一條簡單的UPDATE語句而已，而且看起來每次只更新了n條記錄(doc_id做為限制條件)，還是檢視下執行計劃吧：

    SQL> explain plan for

    2 update T_WIKI_DOC_LOCK

    3 set DOC_LOCK_USER_ID = :1,

    4 DOC_LOCK_START_TIME = to_date(:2, ¨yyyy-mm-dd hh24:mi:ss¨),

    5 DOC_LOCK_HEARTBEAT_TIME = to_date(:3, ¨yyyy-mm-dd hh24:mi:ss¨),

    6 DOC_LOCK_STATE = :4,

    7 DOC_LOCK_USER_ID_ENCRYPT = :5,

    8 DOC_ID_ENCRYPT = :6,

    9 DOC_LOCK_USER_IP = :7,

    10 DOC_TITLE = :8,

    11 DOC_USER_ID_EN = :9,

    12 DOC_USER_NICK = :10

    13 where DOC_ID = :11

    14 /

    Explained

    SQL> select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT

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

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

    | Id | Operation | Name | Rows | Bytes | Cost |

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

    | 0 | UPDATE STATEMENT | | 1 | 38 | 3 |

    | 1 | UPDATE | T_WIKI_DOC_LOCK | | | |

    |* 2 | TABLE ACCESS FULL | T_WIKI_DOC_LOCK | 1 | 38 | 3 |

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

    Predicate Information (identified by operation id):

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

    2 - filter("T_WIKI_DOC_LOCK"."DOC_ID"=TO_NUMBER(:Z))

    Note: cpu costing is off

    15 rows selected

根本原因在於DOC_ID無索引，每次更新均為全表掃描，雖然寫很少，但是讀很多。對於這種型別的SQL語句，解決起來最簡單了，DOC_ID列建立索引即是：

    SQL> CREATE INDEX IND_T_WIKI_DOC_LOCK_DOCID ON T_WIKI_DOC_LOCK(DOC_ID);

    Index created

再次查詢執行計劃確認：

    SQL> explain plan for

    2

    2 update T_WIKI_DOC_LOCK

    3 set DOC_LOCK_USER_ID = :1,

    4 DOC_LOCK_START_TIME = to_date(:2, ¨yyyy-mm-dd hh24:mi:ss¨),

    5 DOC_LOCK_HEARTBEAT_TIME = to_date(:3, ¨yyyy-mm-dd hh24:mi:ss¨),

    6 DOC_LOCK_STATE = :4,

    7 DOC_LOCK_USER_ID_ENCRYPT = :5,

    8 DOC_ID_ENCRYPT = :6,

    9 DOC_LOCK_USER_IP = :7,

    10 DOC_TITLE = :8,

    11 DOC_USER_ID_EN = :9,

    12 DOC_USER_NICK = :10

    13 where DOC_ID = :11

    14 /

    Explained

    SQL> select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT

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

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

    | Id | Operation | Name | Rows | Bytes | Cost

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

    | 0 | UPDATE STATEMENT | | 1 | 38 |

    | 1 | UPDATE | T_WIKI_DOC_LOCK | | |

    |* 2 | INDEX RANGE SCAN | IND_T_WIKI_DOC_LOCK_DOCID | 1 | 38 |

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

    Predicate Information (identified by operation id):

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

    2 - access("T_WIKI_DOC_LOCK"."DOC_ID"=TO_NUMBER(:Z))

    Note: cpu costing is off

    15 rows selected

變成範圍掃的更新，這個結果已經比較理想了。

D>. 統計資訊很重要

對於ORACLE的CBO來說，生成的執行計劃是否智慧，統計資訊所起到的作用非常關鍵，因此上述操作完成後，建議在適當時間段重新生成相關物件的統計資訊，以便ORACLE能夠自動選擇更加合理的執行計劃：

    begin

    dbms_stats.gather_table_stats(user,¨T_WIKI_DOC_TOPICS¨,method_opt => ¨FOR ALL INDEXED COLUMNS ¨);

    dbms_stats.gather_table_stats(user,¨T_WIKI_EVENT¨,method_opt => ¨FOR ALL INDEXED COLUMNS ¨);

    dbms_stats.gather_table_stats(user,¨T_WIKI_DOC_LOCK¨,method_opt => ¨FOR ALL INDEXED COLUMNS ¨);

    end;

整個優化操作至此告以段落，沒錯，就是幾個索引，推薦再返回到前頁看看系統效能的前後對比，我想，這將更有助於加深印象吧！

另外，必須說明，並不是說所有資料庫效能問題，通過文中提到的這種方式都能予以處理，本文僅闡述一種方式，一種思路，一種特定環境下的優化實施過程，用上那經典的四個字：僅供參考！

通過現象來判讀，根據現狀來入手，再進行優化的操作就不會再像之前那樣，總感覺無從著手了！

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

相關文章