自动调整优化器是SQL Tuning Advisor使用的中央工具。顾问程序可以从多个来源接收SQL语句作为输入，使用优化器分析这些语句，然后提出建议。
每次硬解析都调用自动调整优化器会消耗大量时间和资源。调优模式适用于严重影响数据库性能的复杂且高负载的SQL语句。
可管理性顾问（例如SQL调整顾问）使用称为顾问框架的通用基础结构。该框架提供了用于存储任务对象的通用架构和接口。顾问程序模式是一组表，用于存储顾问程序中的数据。SQL Tuning Advisor接收调整输入，然后通过Advisor框架写入Advisor模式。SQL Tuning Advisor在生成报告时会从顾问方案中读取数据。
下图显示了SQL Tuning Advisor的基本体系结构。
图24-1 SQL Tuning Advisor架构
本节包含以下主题：
- SQL Tuning Advisor的输入SQL Tuning Advisor的输入可以来自多个来源，包括ADDM，AWR，共享的SQL区域和SQL调整集。
- SQL Tuning Advisor的输出在分析SQL语句之后，SQL Tuning Advisor会发布建议。
- 自动优化优化器分析 在优化模式下，优化器有更多时间考虑选项并收集统计信息。例如，自动调整优化器可以使用动态统计信息和部分语句执行。
也可以看看：
“ SQL解析 ”
24.1.2.1输入到SQL Tuning Advisor
SQL Tuning Advisor的输入可以来自多个来源，包括ADDM，AWR，共享的SQL区域和SQL调整集。
SQL Tuning Advisor使用其输入源如下：
- 自动数据库诊断监视器（ADDM）
SQL Tuning Advisor的主要输入源是ADDM（发音为Adam）。默认情况下，ADDM每小时主动运行一次。为了确定涉及高负载SQL语句的性能问题，ADDM分析了过去一个小时由自动工作负载存储库（AWR）收集的关键统计信息。如果识别出高负载的SQL语句，则ADDM建议在SQL上运行SQL Tuning Advisor。
- AWR
AWR定期捕获系统活动的快照，包括按相关统计信息（例如CPU消耗和等待时间）排名的高负载SQL语句。
您可以查看AWR并手动识别高负载SQL语句。您可以在这些语句上运行SQL Tuning Advisor，尽管Oracle Database作为自动SQL调整的一部分自动执行此工作。默认情况下，AWR保留最近八天的数据。您可以使用此技术来定位和调整在AWR保留期内运行的任何高负载SQL。
- 共享SQL区
数据库使用共享的SQL区域来调优尚未在AWR中捕获的最新SQL语句。共享的SQL区域和AWR提供了从当前时间识别和调整高负载SQL语句的功能，该时间可以追溯到AWR保留允许的时间，默认情况下，该时间至少为8天。
- SQL调整集
甲SQL调整集（STS）是一个数据库对象，其存储SQL与他们的执行上下文沿语句。STS可以包括尚待部署的SQL语句，其目的是测量其单独的性能或识别性能未达到预期的SQL语句。当一组SQL语句用作输入时，数据库必须首先构造并使用STS。
也可以看看：
- “ 关于SQL调优集 ”
- 《 Oracle数据库性能调优指南》以了解ADDM
- Oracle Database Performance Tuning Guide，了解有关AWR的信息
- Oracle Database Concepts了解共享SQL领域
24.1.2.2 SQL Tuning Advisor的输出
在分析完SQL语句之后，SQL Tuning Advisor将发布建议。
具体来说，SQL Tuning Advisor产生以下类型的输出：
- 有关优化执行计划的建议
- 拟议优化的理由
- 预计绩效收益
- SQL语句实现建议
每个建议显示的受益百分比是使用以下公式计算的：
abnf% = (time_old - time_new)/(time_old)
例如，假设调整前的执行时间为100秒，而在实施建议之后，新的执行时间预计为33秒。此性能改进的收益计算如下：
67% = (100 - 33)/(100)
您选择是否接受建议以优化SQL语句。根据其配置方式，自动SQL Tuning Advisor可以实施SQL配置文件建议来调整语句，而无需用户干预。当按需调用时，SQL Tuning Advisor可以建议用户实现SQL配置文件，但是永远不能自动实现它。
24.1.2.3自动调优优化器分析
在调整模式下，优化器有更多时间考虑选项并收集统计信息。例如，自动调整优化器可以使用动态统计信息和部分语句执行。
下图描述了自动调整优化器执行的不同类型的分析。
图24-2自动调整优化器
本节包含以下主题：
也可以看看：
“ 查询优化器概念 ”
24.1.2.3.1统计分析
优化器依靠统计信息来生成执行计划。
如果这些统计信息陈旧或丢失，则优化器可能会生成次优计划。自动调整优化器会检查丢失或过时的统计信息，并建议在需要时收集新的统计信息。
- 对象统计
优化器检查查询中引用的每个对象的统计信息。
- 系统统计
在Oracle Exadata数据库云服务器上，智能扫描的成本取决于系统统计信息I / O查找时间，多块读取计数和I / O传输速度。这些系统统计信息的值通常在Oracle Exadata数据库云服务器上是不同的，因此需要进行分析以确定这些系统统计信息是否不是最新的。如果收集这些统计信息可以改善计划，则SQL Tuning Advisor建议接受SQL配置文件。
下图描述了对象级统计信息的分析。
图24-3通过自动调整优化器进行统计分析
24.1.2.3.2 SQL分析
SQL分析是自动调整优化器对其自身估计的验证。
通过查看执行历史记录和测试SQL，优化器可以确保它具有可用于生成执行计划的最准确的信息。SQL分析与生成SQL Tuning Advisor建议和实现这些建议的步骤有关，但与之不同。
下图显示了SQL Tuning Advisor，它建议一个SQL配置文件并自动实现它。创建概要文件后，优化器可以在生成执行计划时将其用作其他输入。
图24-4 SQL配置文件
本节包含以下主题：
- SQL分析的工作方式 数据库可以分析一些DML和DDL语句。
- SQL配置文件的实现 如果优化器在统计分析或SQL性能分析期间生成辅助信息，则优化器建议实现SQL配置文件。
也可以看看：
“ 关于SQL概要文件 ”
24.1.2.3.2.1 SQL分析如何工作
数据库可以分析一些DML和DDL语句。
具体来说，SQL Tuning Advisor可以分析以下类型的语句：
- DML语句（
SELECT，INSERT有SELECT条款，UPDATE，DELETE，和的更新或插入操作MERGE） CREATE TABLE语句（仅与AS SELECT子句一起使用）
执行分析后，SQL Tuning Advisor推荐或不建议实现SQL配置文件。
下图显示了SQL分析过程。
图24-5 SQL分析
在进行SQL分析时，优化程序将验证语句的成本，选择性和基数。优化器使用以下两种方法之一：
- 对数据进行采样，并对采样应用适当的谓词
优化器将新估算值与常规估算值进行比较，如果差异足够大，则应用校正因子。
- 执行SQL语句的一个片段
当谓词提供有效的访问路径时，此方法比采样方法更有效。
优化器使用过去的语句执行历史记录来确定正确的设置。例如，如果历史记录指示SQL语句通常仅部分执行，则优化器将使用FIRST_ROWS而不是ALL_ROWS优化。
也可以看看：
“ 选择优化目标 ”
24.1.2.3.2.2 SQL配置文件的实现
如果优化器在统计分析或SQL性能分析期间生成辅助信息，则优化器建议实现SQL概要文件。
如图24-6所示，可以使用以下选项：
- 当按需运行SQL Tuning Advisor时，用户必须选择是否实施SQL配置文件。
- 将“自动SQL调整”任务配置为自动实施SQL配置文件时，顾问程序行为取决于
ACCEPT_SQL_PROFILE调整任务参数的设置：- 如果设置为
true，则顾问程序将自动实现SQL配置文件。 - 如果设置为
false，则需要用户干预。 - 如果设置为
AUTO（默认），则设置为true至少有一个SQL概要文件存在一个SQL语句false且不满足此条件的情况。
注意：
自动SQL调整任务无法自动创建SQL计划基线或向其中添加计划。
- 如果设置为
图24-6实现SQL配置文件
在自动SQL调整期间或之后的任何时间，您都可以查看报告。该报告详细描述了所分析的SQL语句，生成的建议以及所有自动实施的SQL配置文件。
也可以看看：
- “ 使用命令行配置自动SQL优化任务 ”
- “ 计划演变 ”
- “ 关于SQL概要文件 ”
- Oracle Database PL / SQL软件包和类型参考以了解更多信息
ACCEPT_SQL_PROFILE
24.1.2.3.3访问路径分析
的访问路径是通过该数据库中检索数据的装置。
例如，使用索引的查询和使用全表扫描的查询使用不同的访问路径。在某些情况下，索引可以消除全表扫描，从而大大提高SQL语句的性能。下图说明了访问路径分析。
图24-7访问路径分析
自动调优优化器探索新索引是否可以显着提高查询性能，并建议以下任一项：
- 创建索引
索引建议特定于SQL Tuning Advisor处理的SQL语句。有时，新索引可以快速解决与单个SQL语句相关的性能问题。
- 运行SQL Access Advisor
因为自动调整优化器不会分析其索引建议如何影响整个SQL工作负载，所以它还建议在SQL语句上运行SQL Access Advisor以及代表性的SQL工作负载。在提出建议之前，SQL Access Advisor会检查在SQL工作负载上创建索引的影响。
24.1.2.3.4 SQL结构分析
在结构分析过程中，自动调整优化器会尝试识别可能导致性能欠佳的语法，语义或设计问题。目的是识别编写不正确的SQL语句并为您提供建议以重组它们。
下图说明了结构分析。
图24-8结构分析
一些语法变化会对性能产生负面影响。在结构分析中，自动调整优化器根据一组规则评估语句，识别效率低下的编码技术，并在可能的情况下建议使用替代语句。
如图24-8所示，“自动调整优化器”确定了以下类别的结构问题：
- SQL构造函数使用效率低下
一个次优地执行语句可能是使用
NOT IN的替代NOT EXISTS或UNION代替UNION ALL。与UNION运算符相反，该UNION ALL运算符使用唯一排序来确保结果集中没有重复的行。如果您知道两个查询不返回重复项，请使用UNION ALL。 - 数据类型不匹配
如果索引列和比较值的数据类型不匹配，则由于隐式数据类型转换，数据库不使用索引。另外，数据库必须花费额外的资源来转换数据类型，并且某些SQL语句可能会失败，因为数据值转换不正确。常见的错误包括包含数字数据但从未用于算术运算的列：电话号码，信用卡号和支票号。为了避免基数估计不佳，计划不理想以及
ORA-01722错误，开发人员必须确保绑定变量是类型VARCHAR2而不是数字。 - 设计错误
设计错误的经典示例是缺少连接条件。如果n是查询块中的表数，则必须存在n -1个连接条件，以避免笛卡尔乘积。
在每种情况下，自动调整优化器都会提出相关建议以重组语句。建议的替代声明与原始声明相似但不等效。例如，建议的语句可以使用UNION ALL代替UNION。然后，您可以确定建议是否正确。
24.1.2.3.5替代计划分析
调整SQL语句时，SQL Tuning Advisor会搜索实时和历史性能数据以寻找该语句的替代执行计划。
如果存在除原始计划之外的其他计划，则SQL Tuning Advisor将报告替代计划发现。下图显示了SQL Tuning Advisor查找两个替代计划并生成替代计划的发现。
图24-9替代计划分析
SQL Tuning Advisor验证替代执行计划，并记录所有不可复制的计划。当找到可重现的替代计划时，您可以创建一个SQL计划基准，以指示优化器将来选择这些计划。
示例24-1替代计划查找
以下示例显示了针对SELECT语句的替代计划查找：
2- Alternative Plan Finding
---------------------------
Some alternative execution plans for this statement were found by searching
the system's real-time and historical performance data.
The following table lists these plans ranked by their average elapsed time.
See section "ALTERNATIVE PLANS SECTION" for detailed information on each
plan.
id plan hash last seen elapsed (s) origin note
-- ---------- -------------------- ------------ --------------- ----------------
1 1378942017 2009-02-05/23:12:08 0.000 Cursor Cache original plan
2 2842999589 2009-02-05/23:12:08 0.002 STS
Information
-----------
- The Original Plan appears to have the best performance, based on the
elapsed time per execution. However, if you know that one alternative
plan is better than the Original Plan, you can create a SQL plan baseline
for it. This will instruct the Oracle optimizer to pick it over any other
choices in the future.
execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_XXXXX',
object_id => 2, task_owner => 'SYS', plan_hash => xxxxxxxx);
前面的示例显示SQL Tuning Advisor找到了两个计划，一个在共享SQL区域中，另一个在SQL调整集中。共享SQL区域中的计划与原始计划相同。
如果原始计划的运行时间比替代计划更糟，则SQL Tuning Advisor仅建议替代计划。在这种情况下，SQL Tuning Advisor建议用户在性能最佳的计划上创建SQL计划基线。在示例24-1中，替代计划的性能不如原始计划，因此SQL Tuning Advisor不建议使用替代计划。
示例24-2替代计划部分
在此示例中，SQL Tuning Advisor输出的替代计划部分包括原始计划和替代计划，并总结了它们的性能。最重要的统计数据是经过时间。原始计划使用索引，而替代计划使用全表扫描，将经过时间增加了0.002秒。
Plan 1
------
Plan Origin :Cursor Cache
Plan Hash Value :1378942017
Executions :50
Elapsed Time :0.000 sec
CPU Time :0.000 sec
Buffer Gets :0
Disk Reads :0
Disk Writes :0
Notes:
1. Statistics shown are averaged over multiple executions.
2. The plan matches the original plan.
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | MERGE JOIN | |
| 3 | INDEX FULL SCAN | TEST1_INDEX |
| 4 | SORT JOIN | |
| 5 | TABLE ACCESS FULL| TEST |
--------------------------------------------
Plan 2
------
Plan Origin :STS
Plan Hash Value :2842999589
Executions :10
Elapsed Time :0.002 sec
CPU Time :0.002 sec
Buffer Gets :3
Disk Reads :0
Disk Writes :0
Notes:
1. Statistics shown are averaged over multiple executions.
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | HASH JOIN | |
| 3 | TABLE ACCESS FULL| TEST |
| 4 | TABLE ACCESS FULL| TEST1 |
-------------------------------------
要采用替代计划，而不考虑SQL Tuning Advisor是否推荐它，请致电DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE。您可以使用此过程在任何现有的可复制计划上创建SQL计划基准。
