通往测试架构师的必经路：Oracle常见性能问题调优总结

Oracle常见性能问题调优主要分为配置类、SQL效率类、应用程序逻辑三大类，以下是对各类问题的详细总结：

• 绑定变量

  使用场景：在联机交易系统中，对于频繁执行且所查数据分布较均匀、分区较均衡的SQL语句，使用绑定变量代替常量，可避免多次重复硬解析，节省时间和资源成本。

  反例：

  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：

  利用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;

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

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

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

  绑定变量偷窥现象：Oracle优化器在第一次硬解析时会“偷窥”变量的值，并根据该值确定执行计划并缓存。后续执行时不再“偷窥”，而是直接沿用缓存的执行计划。这可能导致传入不同变量值时，无法选择最优的执行路径。例如，T1表中status字段有‘A’和‘C’两种取值，当第一次传入‘A’时走全表扫描并缓存执行计划，后续即使传入‘C’也会走全表扫描而无法使用索引。因此，是否采用绑定变量需衡量硬解析带来的资源开销和查询计划不准带来的资源开销。

• 连接池

  判断连接池是否够用的方法：

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

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

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

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

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

• 并行模式PARALLEL

  适用场景：并行模式适用于大数据量的操作，应用得当能大大缩短计算时间。

  注意事项：

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

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

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

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

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

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

• 统计信息缺乏或陈旧

  问题影响：开发测试环境往往缺乏统计信息更新机制，统计信息陈旧可能造成SQL查询计划有误，查询效率低下。

  优化措施：大量的数据加载或更新后应及时收集统计信息。

• 物化视图

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

  注意事项：

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

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

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

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

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

不合理的数据结构设计、SQL书写不规范会导致笛卡尔积操作、全表扫描、索引跳扫、索引全扫、filter低效过滤等低效操作，从而导致SQL效率甚至应用性能大打折扣。

• 表结构不合理

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

  示例：

  

• SQL书写较差

  表现：SQL语句书写不规范，可能导致性能问题。

  示例：

  

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

• 高频的SQL运行导致CPU繁忙：SQL语句平均执行时间很快，但单笔交易运行相同SQL达100遍以上，需要结合业务逻辑考虑SQL设计的合理性。
• 高频的记日志导致IO等待：例如单笔普通查询交易按照动账类金融交易严格记录日志，查询交易吞吐量较高时增加数据库服务器IO瓶颈。
• 字段长度不满足业务增长需求：导致键值冲突等异常。
• 未对用户反复提交查询作出限制：尤其对于响应时间较长的SQL以及结果集可能比较大的SQL，如未防止用户反复点击会对数据库产生严重影响。
• 分页查询：客户往往只关注查询排序后的部分结果集，为了控制输出结果集大小，减少系统中IO，将结果尽快地返回给客户端，开发上经常采用分页查询。