笙亿网络策划

Oracle优化器性能调优策略，解决查询缓慢与执行计划不稳定的痛点

约 6 分钟读完 2 阅
文章导读
Oracle优化器性能调优的关键是使用SQL Profile来固定高效执行计划，避免因统计信息变化或参数设置不当导致的计划不稳定和查询缓慢。
📋 目录
  1. 壹 Oracle优化器性能调优策略，解决查询缓慢与执行计划不稳定的痛点
  2. 贰 为什么查询会变慢且执行计划不稳定？
  3. 叁 如何发现和诊断问题？
  4. 肆 固定执行计划的最佳实践
  5. 伍 调整优化器参数和统计信息管理
  6. 陆 常见误区与注意事项
  7. 柒 FAQ
A A

Oracle优化器性能调优策略，解决查询缓慢与执行计划不稳定的痛点

Oracle优化器性能调优的关键是使用SQL Profile来固定高效执行计划，避免因统计信息变化或参数设置不当导致的计划不稳定和查询缓慢。

为什么查询会变慢且执行计划不稳定？

很多人在使用Oracle数据库时，会遇到查询速度时快时慢的情况。今天查询很快，明天同样的查询却变得很慢，这通常是因为执行计划发生了变化。执行计划是优化器决定如何执行SQL语句的路线图，比如是全表扫描还是使用索引。优化器在生成执行计划时，依赖数据库的统计信息，比如表中数据的总行数、数据分布情况等。如果统计信息不准确，比如过期了或者收集得不对，优化器就会做出错误的判断，选择低效的执行计划。另外，数据库的某些参数设置，比如优化器模式，也会影响执行计划的选择。有时候，多版本并发控制（MVCC）或者绑定变量窥探等功能，也可能导致执行计划不稳定。理解这些原因是解决问题的第一步。

如何发现和诊断问题？

首先，你可以使用Oracle提供的工具来查看SQL语句的执行计划。最常用的方法是使用EXPLAIN PLAN命令，或者在SQL*Plus中执行`SET AUTOTRACE ON`。更深入一点，可以查询动态性能视图，比如`V$SQL`和`V$SQL_PLAN`，来查看实际执行过的SQL语句及其执行计划。重点关注那些执行时间长的语句，或者执行次数多但每次执行时间波动大的语句。通过比较不同时间点的执行计划，你可以发现是否有计划改变了。同时，检查相关表的统计信息是否最近被更新过，以及统计信息是否反映了数据的真实情况。如果发现统计信息有问题，可能需要重新收集。但注意，不要盲目收集统计信息，因为这可能引发新的问题。

固定执行计划的最佳实践

如果确认某个SQL语句有一个高效的执行计划，但优化器经常选错，那么最好的方法是固定这个好的执行计划。Oracle提供了几种方法，比如存储大纲（Stored Outline）、SQL计划基线（SQL Plan Baseline）和SQL Profile。其中，SQL Profile是一个推荐的工具。你可以使用DBMS_SQLTUNE包来创建SQL Profile。基本步骤是：首先，找到问题SQL语句的SQL_ID；然后，使用`DBMS_SQLTUNE.CREATE_TUNING_TASK`创建一个调优任务；接着，执行`DBMS_SQLTUNE.EXECUTE_TUNING_TASK`来运行任务；最后，接受推荐的SQL Profile。一旦SQL Profile被创建和应用，优化器就会倾向于使用固定的高效执行计划，即使统计信息发生变化。这种方法可以有效地稳定性能，避免查询速度的意外下降。

Oracle优化器性能调优策略，解决查询缓慢与执行计划不稳定的痛点

调整优化器参数和统计信息管理

除了固定执行计划，你还可以通过调整优化器参数来影响其行为。比如，设置`OPTIMIZER_MODE`参数，可以选择基于成本或基于规则的优化模式。不过，在现代Oracle版本中，基于成本的优化是默认和推荐的。另一个重要参数是`OPTIMIZER_DYNAMIC_SAMPLING`，它控制动态采样的级别，可以在没有统计信息时提供帮助。对于统计信息管理，建议采用自动统计信息收集作业，但需要监控其效果。对于大型表或分区表，可能需要手动制定更精细的统计信息收集策略，比如只收集变化大的分区。避免在业务高峰时段收集统计信息，以免影响性能。同时，可以考虑使用增量统计信息收集来提高效率。

常见误区与注意事项

在性能调优过程中，有一些常见的误区需要注意。一是过度依赖索引。并不是索引越多越好，不合适的索引反而会拖慢写入速度，并可能导致优化器选择错误的执行计划。二是盲目收集统计信息。频繁或不当的统计信息收集可能导致性能波动。三是忽视SQL语句本身的质量。有时候，优化器的问题根源在于SQL写得不好，比如使用了低效的连接方式或函数。因此，在调优优化器之前，先检查SQL语句是否可以被重写以更高效。最后，记住调优是一个持续的过程，需要定期监控和调整，因为数据和访问模式会随着时间变化。

FAQ

问题1：如何快速判断一个SQL语句的执行计划是否发生了变化？
回答：你可以查询`V$SQL`视图，根据SQL_ID找到该语句，然后比较不同时间点的`PLAN_HASH_VALUE`值。如果这个值发生了变化，通常意味着执行计划改变了。也可以使用AWR报告或SQL监控报告来查看历史执行计划对比。

Oracle优化器性能调优策略，解决查询缓慢与执行计划不稳定的痛点

问题2：固定执行计划后，如果数据量发生巨大变化，固定的计划还适用吗？
回答：不一定是适用的。固定执行计划虽然能稳定性能，但如果数据分布发生显著变化（例如，从小表变成大表），原来高效的执行计划可能变得低效。因此，需要定期审查固定的执行计划，在数据变化大时重新评估。可以使用DBMS_SQLTUNE包来重新分析SQL Profile的建议。

问题3：除了SQL Profile，还有哪些方法可以稳定执行计划？
回答：还有其他方法，比如使用SQL计划基线（SQL Plan Baseline），它允许优化器在多个执行计划中选择，但可以标记某些计划为不可接受。存储大纲（Stored Outline）是较旧的方法，但仍然可用。另外，通过调整优化器参数或使用提示（Hints）在SQL语句中直接指定执行路径，也可以影响计划选择，但提示需要修改SQL代码，灵活性较差。

引用来源：
1. Oracle Database SQL Tuning Guide, 19c: "Using SQL Profiles"
2. Oracle Database Performance Tuning Guide, 19c: "Managing Optimizer Statistics"
3. Oracle Base: "SQL Plan Management (SPM) and SQL Profiles" (在线资源)