oracle性能调优总结（数据库性能优化方法）

Oracle在性能调优中提供了丰富的工具和报表支持，如何从众多指标获取有价值的调优信息则需要开发测试人员具有一定的基础和经验。本文主要对近几年碰到频率较高的几类性能问题进行经验总结，帮助开发、测试人员在调优过程中少走弯路，尽快定位、解决性能问题。

除去硬件故障和产品本身bug外，本文分为配置类，sql效率类、应用程序逻辑三大类进行归纳介绍。

1.配置类

1.1绑定变量

在联机交易系统中，对于频繁执行的SQL语句，如果所查数据分布较均匀、分区较均衡，建议使用绑定变量代替常量，以避免多次重复硬解析(Hard Parse)，节省时间、资源成本。

反例：

select * from user where userid=1;

select * from user where userid=2;

正例：

b1=1;

select * from user where userid= :b1;

b1=2;

select * from user where userid= :b1;

硬解析指标参考AWR报告中Load Profile–>Hard Parse/Sec（参考值：< 2 or 10），笔者一般会在大于1时用以下脚本查找可能需使用绑定变量优化的SQL。

1、利用force_matching_signature查询可能可以使用绑定变量的语句数量

select v.force_matching_signature ,count(*) from v$sql v where FORCE_MATCHING_SIGNATURE <> EXACT_MATCHING_SIGNATURE and PARSING_SCHEMA_NAME <> ‘SYS’ group by v.force_matching_signature having count(*)>&a order by 2;

2、查出疑似可使用绑定变量的SQL语句

select PARSING_SCHEMA_NAME,sql_text,sql_id from v$sql where force_matching_signature=’force_matching_signature_IDinStep1′;

3、 根据查询结果与开发人员沟通，确认是否可用绑定变量的方式对SQL语句进行优化。

1）利用force_matching_signature查询可能可以使用绑定变量的语句数量

select v.force_matching_signature ,count(*) from v$sql v where FORCE_MATCHING_SIGNATURE <> EXACT_MATCHING_SIGNATURE and PARSING_SCHEMA_NAME <> ‘SYS’ group by v.force_matching_signature having count(*)>&a order by 2;

2）查出疑似可使用绑定变量的SQL语句

select PARSING_SCHEMA_NAME,sql_text,sql_id from v$sql where force_matching_signature=’force_matching_signature_IDinStep1′;

3）根据查询结果与开发人员沟通，确认是否可用绑定变量的方式对SQL语句进行优化。

绑定变量一定能优化性能吗？

–小心”绑定偷窥”！！！

T1表包含100万条记录，status字段含’A’，’C’两种不同取值，其中：

status=’A’ 99万9990条

status=’C’ 10条

SQL1：Select * from T1 where status=:b1

:b1 =’A’，则单表访问路径走全表扫描

SQL2：Select * from T1 where status=:b1

:b1 =’C’，则单表访问路径走索引范围扫描

理想情况下，传入不同变量的值，应该走不一样的单表访问路径，但Oracle优化器还不够智能。Oracle在第一次做硬解析（内存中没有缓存执行计划）的时候，会先”偷窥”一眼，变量的值传入的是什么，如果传入的是”A”,则走全表扫描；并且把执行计划缓存。

下一次执行的时候，由于执行计划已经缓存，就不再”偷窥”变量的值了，而是直接沿用全表扫描的执行计划。这个时候即使传入的status变量为C，也走不上索引了。

这个现象称为”绑定变量偷窥现象”。一条SQL语句适合采用何种解析方式需要衡量硬解析带来的资源开销和查询计划不准带来的资源开销，从而确定是否采用绑定变量。两种解析适用场景总结如下：

1.2连接池

经常收到一些”怎么查看应用到Oracle连接池是否够用”，”系统tps很低，SQL也简单，为啥数据库服务器cpu>10%？”

除了根据服务器连接数或利用第三方工具，可从以下4个方面间接判断连接池是否够用：

1. 参考AWR报告中Load Profile–>Logons /Sec，参考值：< 2 or 10。

2. 参考ADDM中出现Session Connect and Disconnect–> Percent of Activity > 10。

3. top命令中cpu消耗排在前10位进程中含tnslsnr，或该进程消耗cpu > 10%。

4. alert.log中出现连接频繁建立或断开的告警。

若出现上述现象，应考虑适当增加连接池或检查应用是否用到连接池。

1.3并行模式PARALLEL

并行模式适用于针对大数据量的操作，应用得当能大大缩短计算时间。但其劣势在于：资源调度、合并结果集等比较消耗资源，不建议在系统超负荷运行的情况下使用。并行模式使用应注意以下几项：

1.联机交易往往并发较高，应避免使用并行。

2.联机交易高峰时段，避免批量或报表使用并行。

3.并行查询的优先级为语句提示(hint)、表级定义、数据库初始化参数。后两者易造成响应时间慢、表扫描、会话阻塞等异常，不建议在应用运行时使用。

4.对于较大的数据量的查询，可以使用提示(hint)来强制Oracle使用并行查询。

5.建表、索引时如需使用PARALLEL，完成后切记关闭并行度，否则会造成后续使用该表、索引的SQL启用了并行,占用过多资源，导致其它会话等待，影响系统整体性能。

6.任务并行度不应大于服务器CPU数，建议单个任务并行度应小于CPU数/2。

1.4统计信息缺乏或陈旧

开发测试环境往往缺乏统计信息更新机制，统计信息陈旧可能造成SQL查询计划有误，查询效率低下。大量的数据加载或更新后应及时收集统计信息。

1.5物化视图

物化视图是一种特殊的物理表，占用实际的存储空间，可用于读写分离，或者预先计算并保存表连接、嵌套或聚集等耗时较多的操作结果，在执行查询时能避免这些耗时操作，从而快速得到结果。物化视图主要用于数据仓库和决策支持系统，使用物化视图需注意：

1.对于高并发的联机系统、基表数据频繁更新且对数据实时性要求高的交易避免访问物化视图。

2.基表数据变更频繁，一般不建议使用ON COMMIT数据刷新模式，推荐使用默认的ON DEMAND手工模式。

3. ON DEMAND模式下用到FAST增量刷新时，必须在创建有物化视图日志的情况下才能使用。

4.物化视图日志的大小直接会影响刷新速度。物化视图长时间不刷新，或者基表的一次批量数据变更均会导致物化视图日志变得很大。

5.物化视图日志的高水位达到较高位置，即使物化视图日志中记录很少甚至没有，仍然会影响物化视图的刷新速度。

2. SQL效率类

不合理的数据结构设计，SQL书写不规范会导致笛卡尔积操作、全表扫描、索引跳扫、索引全扫、filter低效过滤等低效操作，从而导致SQL效率甚至应用性能大打折扣。本章节列出了常见的导致SQL低效的条例，实际开发测试过程中可能需要结合查询计划、统计信息、V$_*等进行调优验证。

2.1表结构不合理

表结构不合理一般表现在：缺少主键、索引或索引设计不当，尤其是复合索引的选择和排序上。表连接的时候恰当使用索引可以避免表扫和排序的发生。

2.2 SQL书写较差

3. 应用程序逻辑

在性能测试测试中曾遇见因应用设计导致数据库服务器瓶颈，常见类型有：

1.高频的SQL运行导致CPU繁忙。SQL语句平均执行时间很快，但通过对单笔交易运行的sql语句发现单笔交易运行相同SQL达100遍以上，需要结合业务逻辑考虑SQL设计的合理性。

2.高频的记日志导致IO等待。例如单笔普通查询交易按照动账类金融交易严格记录日志，查询交易吞吐量较高时增加数据库服务器IO瓶颈。

3.字段长度不满足业务增长需求，导致键值冲突等异常。

4.未对用户反复提交查询作出限制。尤其对于响应时间较长的SQL以及结果集可能比较大的SQL，如未防止用户反复点击会对数据库产生的严重影响。

5.客户往往只关注的查询排序后的部分结果集，为了控制输出结果集大小，减少系统中IO，将结果尽快地返回给客户端，开发上经常采用分页查询。