大话系统架构优化项目之数据库优化

【案例】某企业内部核心业务系统数据库出现业务高峰CPU使用率居高不下，存在大数据量查询、多表连接造成查询性能下降、表索引建立不合理等问题，最终通过以下办法将业务高峰期CPU使用率控制在30%内：

在SQL*PLUS下执行下面语句：

SQL> set line 1000 –设置每行显示1000个字符

SQL> set autotrace traceonly –显示执行计划和统计信息，但是不显示查询输出

执行效率低下SQL语句：

select variablein0_.TOKENVARIABLEMAP_ as TOKENVAR7_1_
from JBPM_VARIABLEINSTANCE variablein0_
where variablein0_.TOKENVARIABLEMAP_ = ‘4888804’
查看优化前的执行计划：

执行计划

select variablein0_.TOKENVARIABLEMAP_ as TOKENVAR7_1_
from JBPM_VARIABLEINSTANCE variablein0_
where variablein0_.TOKENVARIABLEMAP_ = '4888804'

统计信息

1 recursive calls
1 db block gets
48995 consistent gets
48982 physical reads
0 redo size
1531 bytes sent via SQL*Net to client
248 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

从执行计划看该语句缺少索引导致全表扫描。消耗总一致性读占用为：48995，平均每行一致性读：48995/9=5444，物理读为：48982，不满足正常性能需要。创建索引优化后的执行计划：

统计信息

1 recursive calls
0 db block gets
6 consistent gets
4 physical reads
0 redo size
1530 bytes sent via SQL*Net to client
248 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

从执行计划看该语句消耗总一致性读占用为：6，平均每行一致性读：6/9=0.67，物理读为：4，为比较高效的SQL。

一般认为平均每行一致性读超过100的为执行效率比较低的SQL，10以内为执行效率比较高的SQL。

根据以往优化实践， 引起SQL效率低下的问题 主要集中在如下几个方面：

（1）访问路径，主要集中在由于索引缺失或者数据迁移导致索引失效引起的SQL执行时无法使用索引扫描，而被迫使用全表扫描访问路径。此时的解决方法是建立缺失的索引或者重建索引。

（2）过度使用子查询，在某些情形下我们会连接多个大表，而此时由于业务逻辑的需要我们经常会使用到某些子查询，由于语句的逻辑太过复杂，致使oracle无法自动将子查询语句转换为多表连接操作，由此带来的结果是导致oracle选择错误的执行路径，带来语句执行性能的急剧下降。因此，我们需要尽可能使用连接查询代替子查询，这样可以帮助oracle查询优化器根据数据分区情况、索引设计情况，选择合理的连接顺序、连接技术以及表访问技术，即选择最高效的执行计划。

（3）使用绑定变量的好处是可以避免硬解析，好处在此不多谈，但带来的坏处是有可能选择错误的执行计划，而这有可能引起性能的急剧下降。目前oracle 10g中已经引入绑定变量分级机制来着手处理这个问题， 11g通过创建新的子游标而维护一个新的执行计划。在11g下我们可以大胆地使用绑定变量。