2013年（350）
分类： Oracle
2013-04-10 13:35:22
接下来是条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
根本原因在于DOC_ID无索引，每次更新均为全表扫描，虽然写很少，但是读很多。对于这种类型的语句，解决起来最简单了，DOC_ID列创建索引即是：
SQL> CREATE INDEX IND_T_WIKI_DOC_LOCK_DOCID ON T_WIKI_DOC_LOCK(DOC_ID);
再次查询执行计划确认：
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
变成范围扫的更新，这个结果已经比较理想了。
对于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;整个优化操作至此告以段落，没错，就是几个索引，推荐再返回到前页看看系统性能的前后对比，我想，这将更有助于加深印象吧！
另外，必须说明，并不是说所有性能问题，通过文中提到的这种方式都能予以处理，本文仅阐述一种方式，一种思路，一种特定环境下的优化实施过程，用上那经典的四个字：仅供参考！
通过现象来判读，根据现状来入手，再进行优化的操作就不会再像之前那样，总感觉无从着手了！
