ORACLE 9i数据库优化案例(4) --- 索引改善UPDATE

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7607759/viewspace-681170/