Dav_笔记12：Automatic SQL Tuning 之 1 概述

自动调整优化器概述

Oracle数据库使用优化程序为已提交的SQL语句生成执行计划。优化器以以下模式运行：

■普通模式

优化器编译SQL并生成执行计划。正常模式为绝大多数SQL语句生成合理的计划。在正常模式下，优化器以非常严格的时间约束运行，通常只有几分之一秒。

■调整模式

优化程序执行其他分析以检查是否可以进一步改进在正常模式下生成的计划。优化器输出不是执行计划，而是一系列操作，以及它们的基本原理和产生明显更好的计划的预期收益。在调整模式下运行时，优化程序称为自动调整优化程序。

在调整模式下，优化器可能需要几分钟来调整单个语句。每次必须对查询进行硬解析时，调用自动调整优化器既耗时又耗费资源。自动调整优化器适用于对数据库有重大影响的复杂和高负载SQL语句。

自动数据库诊断监视器（ADDM）主动识别适合SQL调优的高负载SQL语句（请参阅Dav_笔记 1 “自动执行性能诊断”）。 自动SQL调整功能还可以自动识别有问题的SQL语句，并在系统维护窗口期间实现调整建议，作为自动维护任务。

自动调整优化器执行以下类型的调整分析：

■统计分析

■SQL分析

■访问路径分析

■SQL结构分析

■替代计划分析

统计分析

优化器依赖于对象统计信息来生成执行计划。如果这些统计信息过时或缺失，则优化程序不具备所需的必要信息，并且可能会生成较差的执行计划。自动调整优化程序检查每个查询对象是否有丢失或过时的统计信息，并生成两种类型的输出：

■建议收集具有过期或无统计信息的对象的相关统计信息

由于自动收集和刷新优化程序统计信息，因此仅在禁用自动优化程序统计信息收集时才会出现此问题。请参阅第13-2页的“管理自动优化程序统计信息收集”。

■没有统计信息的对象的辅助统计信息，以及具有过时统计信息的对象的统计调整因子

数据库将此辅助信息存储在称为SQL配置文件的对象中。

SQL分析

SQL配置文件是一组特定于SQL语句的辅助信息。从概念上讲，SQL配置文件是SQL语句对表或索引的统计信息。数据库可以使用辅助信息来改进执行计划。

访问路径分析

访问路径是从数据库中检索数据的手段。

例如，使用索引的查询和使用全表扫描的查询使用不同的访问路径。

通过减少对大型表进行完全扫描的需要，索引可以极大地提高SQL语句的性能。

有效的索引是一种常见的调优技术。自动调整优化器探索新索引是否可以显着提高查询性能。

如果是，那么顾问建议创建索引。

由于自动调整优化程序不会分析其索引建议如何影响整个SQL工作负载，因此还建议在SQL语句上运行SQL Access Advisor以及具有代表性的SQL工作负载。

SQL Access Advisor在提出建议之前会查看在整个SQL工作负载上创建索引的影响。

请参见Dav_笔记12上的“自动SQL调整功能”。

SQL结构分析

自动调整优化程序可识别可能导致性能不佳的SQL语句结构的常见问题。

这些可能是语法，语义或设计问题。在每种情况下，自动调整优化程序都会提供相关建议来重构语句。

建议的替代方案与原始陈述类似，但不相同。

例如，优化器可能建议使用UNION ALL替换UNION运算符，或者使用NOT EXISTS替换UN IN运算符。然后，您可以确定该建议是否适用于您的情况。

例如，如果模式设计不可能重复，则UNION ALL运算符比UNION运算符更有效。这些更改需要很好地理解数据属性，并且只有在仔细考虑后才能实现。

替代计划分析

在调整SQL语句时，SQL Tuning Advisor会搜索实时和历史性能数据，以获取该语句的替代执行计划。如果存在除原始计划之外的计划，则SQL Tuning Advisor将报告备用计划查找。

SQL Tuning Advisor验证备选执行计划并记录任何不可重现的计划。

找到可重现的替代计划后，您可以创建SQL计划基准，以指示优化程序将来选择这些计划。

例17-1显示了SELECT语句的替代计划查找。

例17-1替代计划查找

2-替代计划发现

---------------------------

通过搜索找到了该语句的一些替代执行计划

系统的实时和历史性能数据。

下表按平均经过时间列出了这些计划。

有关每个部分的详细信息，请参阅“替代计划部分”部分计划。

信息

-----------

- 根据每次执行所用的时间，原始计划似乎具有最佳性能。

但是，如果您知道一个备用计划比原始计划更好，则可以为其创建SQL计划基准。

这将指示Oracle优化器在将来的任何其他选择中选择它。

execute 
dbms_sqltune.create_sql_plan_baseline
(
task_name => 'TASK_XXXXX',

object_id => 2, 
task_owner => 'SYS', 
plan_hash => xxxxxxxx
);

例17-1显示SQL Tuning Advisor发现了两个计划，一个在共享SQL区域，另一个在SQL调优集中。 共享SQL区域中的计划与原始计划相同。

如果原始计划的耗用时间比备用计划差，则QL Tuning Advisor仅建议备用计划。

在这种情况下，SQL Tuning Advisor建议用户在计划上创建具有最佳性能的SQL计划基准。

在示例17-1中，备用计划的性能与原始计划不同，因此SQL Tuning Advisor不建议使用备用计划。

在示例17-2中，SQL Tuning Advisor输出的备用计划部分包括原始计划和备用计划，并总结了它们的性能。

最重要的统计数据是经过的时间。

原始计划使用索引，而替代计划使用全表扫描，将经过时间增加.002秒。

例17-2替代计划部分

无论SQL Tuning Advisor是否推荐使用替代计划，请调用DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE。

您可以使用此过程在任何现有的可重现计划上创建SQL计划基准。