欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-03-03 20:01:13
...

【案例】某企业内部核心业务系统数据库出现业务高峰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下我们可以大胆地使用绑定变量。

相关标签: 数据库优化