当前位置：首页 > news >正文

聊聊Oracle自适应查询优化

news 来源：原创 2025/6/26 20:42:40

成也AQO败也AQO

因为工作的原因，我们接触到的客户大部分是金融和运营商行业，这些客户有个最大的特点是追求稳定，对于使用数据库新特性持保守的态度，不会轻易尝试某些可能会导致生产系统不稳定的新特性。上线前通常都会将一些新特性禁用，避免上线后可能会由于这些新特性而导致性能出现抖动。

但是近期遇到的一个case，却颠覆了我对这些新特性的看法。

最近我们在帮客户分析一条SQL的性能问题，过程中发现由于数据库的Adaptive Plan参数是默认的开启状态，产生了比较多的子游标，当使用某个条件时就会出现性能下降的情况。作为本能的反应，我们建议客户关闭Adaptive Plan功能，给出的理由是“虽然未必是最优的，却是我们能接受的稳定性能”。修改完参数客户重新测试执行了相关的业务模块，之前有问题的SQL按照预期很顺利的执行完成，但是又出现了新的情况，有另一条在之前的测试中秒级执行的SQL这次却整整用了2000+秒才执行完成。这不由得让我们重新审视Adaptive Plan带给数据库的正面影响。

什么是 AQO (自适应查询优化)

为了SQL语句能够始终以最优的执行计划执行，Oracle在不断的探索和革新。从9i的绑定变量Peeking，到11g的ACS和Statistics Feedback，在12c中则引入了Adaptive Query Optimization。

Statistics Feedback在SQL第一次执行时，根据统计信息生成的执行计划执行SQL，执行过程中执行计划不能改变，如果统计信息不准确，在SQL第一次执行时可能就会引起灾难性的问题。而且，Statistics Feedback生成的数据只能保存在内存中而不能固化下来，如果过程中数据库发生了重启，需要重新收集Statistics Feedback信息，这可能导致再一次的灾难发生。

Adaptive Query Optimization就是为了彻底解决这两个问题而引入的，具体包括两方面的功能：自适应执行计划和自适应统计信息。
在这里插入图片描述

自适应计划 (Adaptive Plans)

区别于Statistics Feedback在第一次执行后对比实际运行数据和统计信息对比，发现差异较大再对执行计划进行干预的方式不同，Adaptive Plans将执行计划决策和统计信息收集结合起来，在运行时检测基数估计值是否与执行计划中操作所看到的实际行数有很大的差异，如果差异较大，将会对执行计划进行自动调整，避免SQL语句选择次优的执行计划影响执行性能。具体的干预方式有Join Method、Parallel Distribution Methods和Bitmap Pruning 3种。

Join Method

主要是在Nest Loop和Hash Join之间进行评估决策。执行计划根据收集到的统计信息，计算不同执行计划优劣的“临界点”。比如当A表扫描的行数少于10时Nest Loop是最优的，当扫描的行数大于10则Hash Join是最优的，那么10就是这两种连接方式的“临界点”。有了这个值之后，优化器配置Buffer统计收集器缓存数据，如果扫描涉及到的行数超过10则使用Hash Join，反之则使用Nest Loop。

PARALLEL DISTRIBUTION METHODS

当SQL语句并行执行时，某些操作（如排序、聚合和连接）需要在执行该语句的并行服务器进程之间重新分配数据。优化器选择的分发方法取决于操作、所涉及的并行服务器进程的数量以及预期的行数。如果优化器不准确地估计行数，那么所选择的分布方法可能不是最优的，并可能导致一些并行服务器进程未得到充分利用。

和Join Method的决策方式类似，使用新的自适应分布式方法HYBRID HASH，优化器可以将其分布式方法决策推迟到执行时。相关的并行操作之前会收集统计信息，如果实际涉及的行数少于阈值，则将分布式方法从Hash切换到Broadcast；如果涉及的行数达到阈值，则使用Hash方法。

自适应统计信息

通过上述的介绍，相信大家已经理解了Adaptive Plans是如何指导优化器生成最优执行计划的，在这其中统计信息发挥了非常重要的作用。接下来我们再来看看AQP中自适应统计信息又是如何工作的。

自适应统计信息包括Dynamic Statistics、Automatic Re-optimization和SQL Plan Directives三部分内容。

Dynamic Statistics

在12c之前称为Dynamic Sampling，进化后的动态统计信息引入了Level 11，允许优化器在所有表已经存在统计信息的情况下，自动选择是否使用动态统计信息。动态统计信息收集的数据不仅仅针对单表访问，还包括Join和Group-By谓词条件的统计信息，以此来获得更准确的基数评估。

Automatic Re-optimization

主要包括两部分内容，Statistics Feedback和Performance Feedback。

Statistics Feedback就是11g中的Cardinality Feedback，前面我们也多次提到过其相关的功能，这里就不再赘述。

Performance Feedback主要用于提高Automatic Degree of Parallelism模式下，重复执行的SQL语句选择的并行度。

SQL Plan Directives

前面讲到Statistics Feedback不能保存评估出的信息，因此Oracle又引入了SQL plan directives功能。SQL plan directives 可以看成是持久化的动态采样信息，当优化器发现有评估错误的数据时，会自动生成SPD，这些数据首先保存在SGA内存中，每隔15分钟由后台进程写出到数据字典表中。和SQL Profile和SPM等SQL执行计划稳定工具不同的是，SPD关联的是表或者列等特定对象，而不是某条特定SQL语句。

SPD包括DYNAMIC_SAMPLING 和DYNAMIC_SAMPLING_RESULT两种类型，其中DYNAMIC_SAMPLING用于指示优化器当看到谓词过滤涉及多个列的查询时，使用dynamic sampling来解决基数评估不准确的问题；而DYNAMIC_SAMPLING_RESULT则替代了12.1中的Result Cache，将动态抽样的结果保存在SQL directive仓库中。

写在最后

总体来看，Adaptive Query Optimization涉及到非常多的组件，这也让整个实现流程变得非常复杂，以至于即使笔者这样的老DBA也花了不少时间梳理各个组件之间的关系和理解工作原理，从而更好的用于指导生产实施。

理想美好而现实却是骨感的，AQP的设计理念非常完美但在实际生产过程中仍然存在不少的问题。

统计信息收集非常频繁，生产环境中的各种组合查询千差万别，使得动态统计信息收集介入非常频繁，极端的情况下，9万条SQL可能有7万条是系统自发采集统计信息的，这也让生产系统无形中承载了更多的负担；

过多的SPD会让系统变的更敏感。不同的绑定变量代入值会生成不同的游标，让同一条SQL的执行计划切换非常频繁，而这种切换并不能保证每次都是在最优路线上。对于稳定压倒一切的大多数客户来说，宁愿稳定的跑在次优路线上，也不愿意承担哪怕万分之一的不稳定带来的风险。

或者正因为存在着种种的问题，12.2进行了较大的调整，optimizer_adaptive_features参数被废弃，引入了两个新的参数optimizer_adaptive_plans 和optimizer_adaptive_statistics。其中，optimizer_adaptive_plans用于控制是否允许创建Adaptive Plan，该参数默认为TRUE；optimizer_adaptive_statistics 则用于控制是否允许优化器使用 Adaptive Statistics，该参数默认为FALSE，从而使得优化器不会在解析时间修改SQL语句的执行计划。这也是关注到大多数客户优先考虑的是系统稳定性而不是最大化查询执行性能，最终权衡的一个结果。

最后，我们也看到了积极的一面，在关闭了AQP功能之后，才发现不知不觉中SPD也帮我们规避了不少的风险，默默的让数据库运行的更加高效。只是从理想到现实，仍然有很长的路要走，这也是Oracle不断前进的方向和动力，相信在后续的版本中会有更大的进步！