- 描述语句概要分析
- 使用 SQL 优化指导
- 使用 SQL 访问指导
- 使用自动 SQL 优化
自动优化 SQL 语句
- 自动优化 SQL 语句可简化 SQL 优化的整个过程，并取代手动 SQL 优化。
- 优化程序模式：
–正常模式
–优化模式或自动优化优化程序 (ATO)
- SQL 优化指导用于访问优化模式。
- 应仅对高负载的 SQL 语句使用优化模式。
自动优化 SQL 语句是查询优化程序自动执行整个 SQL 优化过程的功能。此自动过程取代了复杂、重复且费时的手动 SQL 优化功能。SQL 优化指导向用户公开了 SQL 优化的功能。增强的查询优化程序有两种模式：
- 在正常模式下，优化程序编译 SQL 并生成执行计划。正常模式下的优化程序会为绝大多数的 SQL 语句生成一个合理的执行计划。在正常模式下，优化程序遵循非常严格的时间约束条件，通常为一秒钟的若干分之几，在此期间它必须找到一个有效的执行计划。
- 在优化模式下，优化程序执行更多分析，检查是否可以进一步改善在正常模式下生成的执行计划。在优化模式下查询优化程序的输出并不是一个执行计划，而是一系列操作及其理由和预期优点（用于生成一个有明显优势的更好计划）。在优化模式下调用的优化程序被称为自动优化优化程序 (ATO)。ATO 执行的优化被称为系统 SQL 优化。
在优化模式下，优化程序可以用几分钟的时间来优化单条语句。对于对整个系统产生重要影响的高负载的复杂 SQL 语句，应使用 ATO。
应用程序优化面临的挑战
甚至对于专家而言，确定高负载 SQL 语句并对其进行优化也是一项非常具有挑战性的任务。SQL 优化不仅是数据库服务器性能管理最重要的方面之一，而且也是最难完成的任务之一。从 Oracle Database 10g 开始，确定高负载 SQL 语句的任务由自动数据库诊断监视器 (ADDM) 自动执行。虽然 ADDM 识别的高负载 SQL 语句数量可能只占 SQL 总工作量的一个非常小的百分比，但优化这些语句的任务仍十分复杂，需要具有高水平的专业
知识。
此外，SQL 优化活动是一项持续进行的任务，因为在部署新应用程序模块时 SQL 工作量通常发生相对更改。
Oracle Database 10g 引入的 SQL 优化指导旨在取代手动优化 SQL 语句的过程。消耗大量资源（例如 CPU、I/O 和临时空间）的 SQL 语句是 SQL 优化指导的目标对象。该指导接收一条或多条 SQL 语句作为输入后，会提供有关优化执行计划的建议、该建议的理由、估计的性能改善以及实施建议的实际命令。您可以接受建议，从而优化 SQL 语句。引入 SQL 优化指导后，您现在可以让 Oracle 优化程序为您优化 SQL 代码。
SQL 优化指导：概览
SQL 优化指导主要是用作优化过程的驱动者。它通过调用自动优化优化程序 (ATO) 来执行以下四种特定类型的分析：
- 统计信息分析：ATO 检查每个查询对象，确定是否缺少统计信息，或统计信息是否已过时，然后提出收集相关统计信息的建议。同时它还收集辅助信息，以便在无法实施建议的情况下提供缺少的统计信息或更正过时统计信息。
- SQL 概要分析：ATO 会验证它自身的估计值并收集辅助信息以消除估计错误。同时它还根据 SQL 语句的过去执行历史记录，以自定义优化程序设置（例如第一批行和所有行）的形式收集辅助信息。它使用辅助信息构建一个 SQL 概要文件并提出创建 SQL 概要文件的建议。创建 SQL 概要文件后，此概要文件以正常模式启用查询优化程序，以生成经过良好优化的计划。
- 访问路径分析：ATO 会检查新索引是否可明显地改进查询中每个表的访问性能，并且在适当的时候提供创建这种索引的建议。
- SQL 结构分析：在这里，ATO 会尝试确定导致不佳计划的 SQL 语句，并提供相关建议来调整它们。建议的调整可能是对 SQL 代码的语法更改，也可能是语义更改。
过时或缺少的对象统计信息
- 对于优化程序而言，对象统计信息是关键输入。
- ATO 验证每个查询对象的对象统计信息。
- ATO 使用动态采样，并生成以下内容：
–辅助对象统计信息，用于弥补缺少的或修正过时的对象
统计信息
–收集合适的对象统计信息的建议：
DBMS_STATS.GATHER_TABLE_STATS(
ownname=>’SH’, tabname=>’CUSTOMERS’,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
查询优化程序依赖对象统计信息生成执行计划。如果统计信息已过时或缺少统计信息，则优化程序得不到所需的必要信息，因而生成的执行计划可能不是最理想的。
ATO 检查每个查询对象，确定其是否缺少统计信息或统计信息是否已过时，并生成两种类型的输出：
- 统计信息形式的辅助信息（适用于没有统计信息的对象）和统计信息调整系数（适用于具有过时统计信息的对象）
- 为具有过时统计信息或没有统计信息的对象收集相关统计信息的建议
为了获得最佳效果，您应在获得建议时收集统计信息，然后重新运行自动优化程序。但是，如果接受此建议，可能会对系统中的其它查询产生影响，这一点可能令您对是否立即接受建议有所顾虑。
SQL 语句概要分析
- 对于优化程序而言，统计信息是关键输入。
- ATO 验证语句统计信息，例如：
–谓词选择性
–优化程序设置（FIRST_ROWS 与 ALL_ROWS）
- 自动优化优化程序使用以下方法和对象：
–动态采样
–执行语句的一部分
–语句的过去执行历史记录统计信息
- 如果已生成了统计信息，ATO 会构建一个概要文件：
exec :profile_name := dbms_sqltune.accept_sql_profile( task_name =>’my_sql_tuning_task‘);
在 SQL 概要分析期间，主要验证步骤是验证查询优化程序自己所估计的被优化语句的成本、选择性和基数。
在 SQL 概要分析期间，ATO 执行验证步骤，以验证自己的估计值。验证包括对数据进行采样，并对样本应用适当的谓词。对新估计值和常规估计值进行比较，如果区别足够大，则应用更正系数。另一种估计值验证方法需要执行 SQL 语句的一部分。在各自谓词都提供有效的访问路径时，部分执行方法比采样方法更高效。ATO 选择适当的估计值验证
方法。
ATO 还可以使用 SQL 语句的过去执行历史记录来确定正确的设置。例如，如果执行历史记录表明在多数时候仅部分执行 SQL 语句，则 ATO 使用 FIRST_ROWS 优化，而不使用
ALL_ROWS。
如果在统计信息分析或 SQL 概要分析期间生成了辅助信息，则 ATO 会构建 SQL 概要文件。在构建 SQL 概要文件后，它会生成一个创建 SQL 概要文件的用户建议。
在此模式下，ATO 可能会建议接受生成的 SQL 概要文件，以将其激活。
计划优化流程和 SQL 概要文件创建
SQL 概要文件是在自动优化 SQL 语句期间构建的辅助信息集合。因此，SQL 概要文件与 SQL 语句对应，而统计信息与表或索引对应。创建概要文件之后，正常模式下的查询优化程序将 SQL 概要文件与现有统计信息结合在一起使用，为相应 SQL 语句生成经过良好优化的计划。SQL 概要文件将永久存储在数据字典中。但是，常规字典视图不会显示 SQL 概要文件信息。在创建 SQL 概要文件后，每次在正常模式下编译相应 SQL 语句时，查询优化程序就会使用 SQL 概要文件生成经过良好优化的计划。
幻灯片显示了创建和使用 SQL 概要文件的流程。此流程包括两个不同阶段：系统 SQL 优化阶段和常规优化阶段。在系统 SQL 优化阶段中，您可以使用 Oracle Enterprise Manager Database Control 或命令行界面选择要进行系统优化的 SQL 语句，并运行 SQL 优化指导。SQL 优化指导会调用 ATO 生成优化建议（ATO 可能会使用 SQL 概要文件）。如果已构建了 SQL 概要文件，则您可以接受它。接受 SQL 概要文件后，此概要文件会存储在数据字典中。在下一阶段，当最终用户发出相同 SQL 语句时，查询优化程序（在正常模式下）会使用 SQL 概要文件构建一个经过良好优化的计划。SQL 概要文件的使用过程对于最终用户是完全透明的，并且不需要对应用程序源代码进行更改。
SQL 优化循环
SQL 概要文件中包含的辅助信息以特定方式进行存储，在数据库发生更改（如添加或删除索引、表大小增长以及定期收集数据库统计信息）后这些信息仍保持相关。所以，在创建概要文件时，不会冻结相应计划（如在使用大纲时）。
但是，SQL 概要文件可能不再适应数据库中发生的大量更改或者在很长一段时间内累积起来的更改。在这种情况下，需要构建新的 SQL 概要文件，以取代旧的概要文件。
例如，当 SQL 概要文件已过时时，相应 SQL 语句的性能可能会显著变差。在这种情况下，相应 SQL 语句可能会开始表现为高负载或顶级 SQL 语句，因而会再次成为系统 SQL 优化的目标。在这样的情形下，ADDM 会再次将此语句捕获为高负载 SQL。如果发生此情况，您可能决定为该语句重新创造一个新的概要文件。
访问路径分析
ATO 还提供有关索引的建议。有效地编制索引是一种广为人知的优化技术，该技术可以通过减少对全表扫描的需求来显著提高 SQL 语句的性能。ATO 生成的任何索引建议都专用于正在进行优化的 SQL 语句。所以，对于与单个 SQL 语句相关的性能问题，它可以提供快速解决方案。
由于 ATO 并不对索引建议会如何影响整个 SQL 工作量执行分析，所以建议对典型 SQL 工作量中的 SQL 语句运行访问指导。访问指导将收集为 SQL 工作量中每条语句提供的建议，并将其合并成对整体 SQL 工作量的全局建议。
访问路径分析可以提供以下建议：
- 如果新索引能显著改善性能，则创建新索引。
- 运行 SQL 访问指导，基于应用程序工作量执行全面的索引分析。
SQL 结构分析
SQL 结构分析的目标是帮助确定编写不当的 SQL 语句，并就如何调整这些语句提供建议。
语法上的某些变化会对性能产生负面影响。在此模式下，ATO 会根据一组规则对语句进行评估，确定效率较低的编码技术，并提供相应备选语句作为建议。建议可能与原始查询很相似，但不完全相同。例如，NOT EXISTS 和 NOT IN 构造器很相似，但不完全相同。所以，您必须自己确定建议是否有效。由于此原因，ATO 不自动重新编写查询，而是提供建议。
SQL 结构分析可以检测以下类别的问题：
- SQL 构造器的使用，例如使用了 NOT IN，而不是 NOT EXISTS，或者使用了 UNION 而不是 UNION ALL
- 谓词的使用，例如谓词涉及的索引列的数据类型不匹配，妨碍索引的使用
- 设计错误（例如笛卡尔积）
SQL 优化指导：使用模型
SQL 优化指导可以接受一条或多条 SQL 语句作为输入。输入可以来自不同的来源：
- ADDM 确定的高负载 SQL 语句
- 当前在游标高速缓存中的 SQL 语句
- 来自自动工作量资料档案库 (AWR) 的 SQL 语句：用户可以选择 AWR 捕获的任何 SQL 语句集。可以使用快照或基线完成此操作。
- 自定义工作量：用户可以创建一个只包含用户感兴趣的语句的自定义工作量。这些语句可能不在游标高速缓存中，并且不是 ADDM 或 AWR 要捕获的高负载语句。对于这样的语句，用户可以创建一个自定义工作量，并使用指导对其进行优化。
可以对来自游标高速缓存、AWR 和自定义工作量的 SQL 语句进行过滤和排序，然后再将其输入到 SQL 优化指导中。
如果输入的语句有多条，则系统会提供一个名为 SQL 优化集 (STS) 的新对象。STS 可存储多条 SQL 语句及其执行信息：
- 执行上下文：分析方案名称和绑定值
- 执行统计信息：平均所用时间和执行计数
注：创建 STS 时，可以将另一 STS 用作信息来源。
Database Control 和 SQL 优化指导
从 Oracle Enterprise Manager 访问 SQL 优化指导最简单的方法是使用“Advisor Central（指导中心）”页。在主页中，单击位于“Related Links（相关链接）”部分中的“Advisor Central（指导中心）”链接打开“Advisor Central（指导中心）”页。
在“Advisor Central（指导中心）”页上，单击“SQL Advisors（SQL 指导）”链接。在“SQL Advisors（SQL 指导）”页中，单击“SQL Tuning Advisor（SQL 优化指导）”链接。此时将转到“Schedule SQL Tuning Advisor（调度 SQL 优化指导）”页。在此页中，您会发现到其它不同页的链接。单击“Top Activity（顶级活动）”链接打开“Top Activity（顶级活动）”页。
运行 SQL 优化指导：示例
可以使用 Database Control 确定高负载或顶级 SQL 语句。可以从 Database Control 中的多个位置中为所确定的一条或多条 SQL 语句（即 STS）启动 SQL 优化指导：
- 优化 ADDM 确定的 SQL 语句：ADDM 的“Finding Details（查找结果详细资料）”页会显示 ADDM 确定的高负载 SQL 语句。其中每条高负载 SQL 语句都消耗一个或多个系统资源（例如 CPU 时间、缓冲区获取数、磁盘读取数等等）的很大一部分。可以使用此页对所选高负载 SQL 语句启动 SQL 优化指导。
- 优化顶级 SQL 语句：另一个 SQL 源是顶级 SQL 语句列表。本幻灯片展示了这种情形。您可以通过查看在所选时间范围内累积的语句执行统计信息，确定顶级 SQL 语句的列表。用户可以选择由 SQL ID 标识的一条或多条顶级 SQL 语句，并对其启动
SQL 优化指导。 - 优化一个 SQL 优化集：还可以查看不同用户创建的不同 STS。可能已通过从 AWR 创建的一系列快照中选择 SQL 语句，或通过选择自定义 SQL 语句，基于顶级 SQL 语句列表创建了 STS。
实施建议
启动 SQL 优化指导之后，Oracle Enterprise Manager 会自动创建优化任务，前提是用户有相应的 ADVISOR 权限来执行此操作。Oracle Enterprise Manager 在“Schedule SQL Tuning Advisor（调度 SQL 优化指导）”页上显示优化任务及其自动默认设置，如上一张幻灯片所示。在此页上，用户可以更改与优化任务相关的自动默认设置。
其中一个重要选项是选择优化任务的范围。如果选择“Limited（有限制）”选项，SQL 优化指导会根据统计信息检查、访问路径分析和 SQL 结构分析来生成建议。将范围设为“Limited（有限制）”时不会生成 SQL 概要文件建议。如果选择“Comprehensive（综合）”选项，SQL 优化指导不仅会执行“Limited（有限制）”范围下的所有建议，如果适用，还会在 SQL 概要分析模式下调用优化程序来构建 SQL 概要文件。使用“Comprehensive（综合）”选项时，还可以指定优化任务的时间限制，这个时间限制的默认值是 30 分钟。另一个有用的选项是立即运行优化任务，或将其安排在以后的时间运行。
可以在“Schedule Advisor（调度指导）”页配置优化任务。要执行此操作，请选择“Schedule SQL Tuning Advisor（调度 SQL 优化指导）”操作，然后单击“Go（执行）”返回“Top Activity（顶级活动）”页后，您可以单击优化过的语句，打开“SQL Details（SQL 详细资料）”页，以便查看优化信息。
这会显示已完成的优化任务。通过单击此任务，可以查看其常规 SQL 优化结果。通过单击“View（查看）”按钮，可以查看其详细资料。如图所示，已创建了 SQL 概要文件；在查看了新计划后，如果需要，则可以实施它。
SQL 访问指导：概览
如何定义适当的访问结构以优化 SQL 查询一直是开发人员所关心的问题。因此，为了解决该问题，相关人员已经写了大量的论文和脚本，还开发了一些高端工具。此外，随着分区和实体化视图技术的发展，确定访问结构也变得更加复杂。
作为 Oracle Database 10g 和 11g 中的可管理性增强功能的一部分，引入了 SQL 访问指导来解决这个非常关键的需求。
SQL 访问指导可以建议要创建、删除或保留的索引、实体化视图、实体化视图日志或分区，从而确定并帮助解决与执行 SQL 语句相关的性能问题。可以从 Database Control 或者从命令行使用 PL/SQL 过程来运行 SQL 访问指导。
SQL 访问指导可以接受实际工作量作为输入，或者根据方案推导出一个假想工作量。然后，它会推荐合适的访问结构以使用速度较快的执行路径。SQL 访问指导具有以下优点：
- 不需要拥有专业知识
- 根据基于成本的优化程序 (CBO) 中实际存在的规则做决定
- 与优化程序以及 Oracle DB 增强功能同步
- 是涵盖 SQL 访问方法所有方面的单个指导
- 提供用户友好的简单 GUI 向导
- 生成可用于实施建议的脚本
SQL 访问指导：使用模型
SQL 访问指导可接受从以下多个来源派生而来的工作量作为输入：
- SQL 高速缓存，接受 V$SQL 的当前内容
- 假想工作量，根据维模型生成一个可能工作量。在初始设计系统时，这个选项比较
有用 - SQL 优化集，来自工作量资料档案库
SQL 访问指导还提供强大的工作量过滤功能，可用于确定优化目标。例如，用户可以指定 SQL 访问指导只观察工作量中 30 个资源最密集的语句（根据优化程序开销确定）。对于指定的工作量，SQL 访问指导随后会执行以下操作：
- 同时考虑索引解决方案、实体化视图解决方案、分区解决方案或者全部三个解决方案的组合
- 考虑存储的创建和维护成本
- 不为部分工作量生成删除建议
- 优化实体化视图以最大化查询重写使用率和快速刷新
- 建议用于快速刷新的实体化视图日志
- 建议对表、索引和实体化视图进行分区
- 将类似的索引组合为单个索引
- 生成支持多个工作量查询的建议
可能的建议
| 建议 | 综合 | 有限制 |
| 对表或实体化视图添加新的（分区的）索引。 | 是 | 是 |
| 删除未使用的索引。 | 是 | 否 |
| 通过更改索引类型修改现有索引。 | 是 | 否 |
| 通过在末尾添加列修改现有的索引。 | 是 | 是 |
| 添加新的（分区的）实体化视图。 | 是 | 是 |
| 删除未使用的实体化视图（日志）。 | 是 | 否 |
| 添加新的实体化视图日志。 | 是 | 是 |
| 修改现有的实体化视图日志以添加新列或子句。 | 是 | 是 |
| 对现有的未分区表或索引进行分区。 | 是 | 是 |
SQL 访问指导会仔细考虑建议的整体影响，并仅使用已知的工作量和提供的信息生成建议。可以使用两种工作量分析方法：
- 综合：SQL 访问指导通过这种方法解决优化分区、实体化视图、索引和实体化视图日志的所有方面。SQL 访问指导假定工作量包含一个完整的有代表性的应用程序 SQL 语句集。
- 有限制：与综合工作量方法不同，有限制的工作量方法假定工作量仅包含有问题的 SQL 语句。因此，将寻求提高一部分应用程序环境性能的建议。
如果选择了综合工作量分析，则 SQL 访问指导将生成一个较好的全局优化优化集，但所需分析时间会比较长。如表中所示，选择的工作量方法决定了 SQL 访问指导生成的建议类型。
注：仅对至少包含 10,000 行的表和在 NUMBER 或 DATE 类型的列上有一些谓词或联接的工作量给出分区建议。只能针对这些类型的列生成分区建议。此外，只能为单列间隔分区和散列分区生成分区建议。间隔分区建议可能输出为范围语法，但默认值是间隔。执行散列分区只是为了利用智能化分区联接。
SQL 访问指导会话：初始选项
接下来的几张幻灯片将介绍一个典型的 SQL 访问指导会话。可以通过单击数据库主页上的“Advisor Central（指导中心）”链接访问 SQL 访问指导，也可以通过单个预警页或性能页进行访问，这些页可能包含用于帮助解决性能问题的链接。SQL 访问指导包括多个步骤，可在执行这些步骤的过程中提供要优化的 SQL 语句，以及要使用的访问方法类型。
在“SQL Access Advisor: Initial Options（SQL 访问指导: 初始选项）”页中，可以在启动向导前选择用来填充默认选项的模板或任务。可以单击“Continue（继续）”启动向导，或者单击“Cancel（取消）”返回到“Advisor Central（指导中心）”页。
注：在生成建议的过程中，SQL 访问向导可以被打断，从而允许您查看结果。
有关使用 SQL 访问指导的常规信息，请参阅《Oracle Data Warehousing Guide》“SQL Access Advisor”一课中的“Overview of the SQL Access Advisor”部分。
如果在“Initial Options（初始选项）”页上选择了“Inherit Options from a Task or Template（从任务或模板继承选项）”选项，则可以选择一个现有的任务或模板以继承 SQL 访问指导的选项。默认情况下，将使用 SQLACCESS_EMTASK 模板。
通过选择相应的对象并单击“View Options（查看选项）”，可以查看任务或模板定义的各种选项。
SQL 访问指导：工作量来源
可以从三个不同的来源中选择工作量来源：
- Current and Recent SQL Activity（当前和最近的 SQL 活动）：此来源对应于仍缓存在系统全局区 (SGA) 中的 SQL 语句。
- Use an existing SQL Tuning Set（使用现有的 SQL 优化集）：您也可以创建并使用存放语句的 SQL 优化集。
- Hypothetical Workload（假想的工作量）：此选项将提供允许指导搜索维表并生成工作量的方案。此来源在初始设计方案时很有用。
使用“Filter Options（过滤选项）”部分可以进一步过滤工作量来源。过滤选项有：
- Resource Consumption（资源消耗）：按优化程序成本、缓冲区获取数、CPU 时间、磁盘读取数、所用时间、执行数排序的语句数量
- Users（用户）
- Tables（表）
- SQL Text（SQL 文本）
- Module IDs（模块 ID）
- Actions（操作）
SQL 访问指导：建议选项
在“Recommendations Options（建议选项）”页中，可以选择是否限制 SQL 访问指导基于单个访问方法提出建议。可以选择 SQL 访问指导要推荐的结构的类型。如果没有选择三个可能值中的任何一个，则 SQL 访问指导将评估现有的结构，而不尝试推荐新结构。
可以使用“Advisor Mode（指导模式）”部分，以两种模式之一运行指导。这些模式会影响建议的质量和处理所需的时间。在“Comprehensive Mode（综合模式）”中，指导将搜索候选的大型池，以便得到最高质量的建议。在“Limited Mode（限制模式）”中，SQL 访问指导将快速执行，通过仅处理最高成本的语句来限制候选建议。
注：可以单击“Advanced Options（高级选项）”以显示或隐藏选项，这些选项可用于设置空间限制、优化选项和默认存储位置。
SQL 访问指导：安排和复查
SQL 访问指导：结果
通过“Advisor Central（指导中心）”页，可以检索用于您的分析的任务详细资料。通过选择“Advisor Central（指导中心）”页上“Results（结果）”部分中的任务名称，可以访问“Results for Task（任务结果）”的“Summary（概要）”页；可在此页上看到 SQL 访问指导查找结果的概览。该页中显示了图表和统计信息，为建议提供了整体工作量性能和改善查询执行时间方面的可能性。使用该页可以显示语句计数和建议操作计数。
SQL 访问指导：结果和实施
要查看 SQL 访问指导任务结果的其它方面，可单击该页上其它三个选项卡之一：“Recommendations（建议）”、“SQL Statements（SQL 语句）”或“Details（详细
资料）”。
在“Recommendation（建议）”页上，可以细化到各个建议。对于其中的每个建议，可以查看“Select Recommendations for Implementation（选择要实施的建议）”表中的重要信息。然后，可以选择一个或多个建议，并安排实施。
如果单击特定建议的 ID，则将进入“Recommendation（建议）”页，该页显示了指定建议的所有操作，可以根据需要修改语句的表空间名称。完成了任何更改后，单击“OK（确定）”将应用更改。在“Recommendation（建议）”页中，可以查看一个操作的完整文本，方法是单击指定操作的“Action（操作）”字段中的链接。单击“Show SQL（显示 SQL）”可以查看建议中所有操作的 SQL。
“SQL Statements（SQL 语句）”页（本幻灯片没有显示此页）显示了一个图表和一个对应的表，其中列出了最初按成本改善程度由高到低排序的 SQL 语句。最上面的 SQL 语句通过实施关联建议可得到最大程度的改善。
“Details（详细资料）”页显示了创建任务时所用的工作量和任务选项。此页还提供了在任务执行过程中记录的所有日记条目。
还可以通过单击“Schedule Implementation（安排实施）”按钮来安排建议的实施。
SQL 优化循环
Oracle Database 10g 引入了 SQL 优化指导，用于帮助应用程序开发人员改善 SQL 语句的性能。该指导用于解决 SQL 编写不当这一问题；这些 SQL 语句没有采用最有效的方式进行设计。此外，该优化指导还可以解决 SQL 语句执行效果较差的问题（此问题较常见），对于这些 SQL 语句，优化程序由于缺乏精确的相关数据统计信息而生成了较差的执行计划。在所有情况下，该指导都会提供具体的建议来提高 SQL 性能，但是否实施建议由用户决定。
除了 SQL 优化指导以外，Oracle Database 10g 还有一个自动进程，可确定系统中的高负载 SQL 语句。ADDM 就是这样的进程，它可自动确定应进行优化的高负载 SQL 语句。
但是，还是存在一些重要问题：虽然 ADDM 确实可以确定一些应该进行优化的 SQL，但用户仍必须手动查看 ADDM 报表，然后根据这些报表运行 SQL 优化指导以进行优化。
自动 SQL 优化
Oracle Database 11g 可以确定有问题的 SQL 语句，对这些语句运行 SQL 优化指导，并实施获得的 SQL 概要文件建议来优化语句，不需要用户的干预，因而进一步提高了 SQL 优化进程的自动化程度。自动 SQL 优化通过在默认情况下每晚运行的名为“自动 SQL 优化”的新任务使用 AUTOTASK 框架。下面简要描述了 Oracle Database 11g 中的自动 SQL 优化过程：
- 步骤 1：根据 AWR 顶级 SQL 标识（在以下四个不同时间段处于顶级的 SQL：过去一周、过去一周中的任何一天、过去一周中的任何一小时或者单个响应时间），自动 SQL 优化可以确定自动优化目标。
- 步骤 2 和 3：在维护窗口中执行自动 SQL 优化任务时，将通过调用 SQL 优化指导自动优化以前确定的 SQL 语句。因此，如果需要，将为这些语句创建 SQL 概要文件。但是，在做出决定之前，需要认真测试新的概要文件。
- 步骤 4：您在任何时间点都可以请求有关这些自动优化活动的报表。
然后，可以选择检查优化的 SQL 语句以验证或删除生成的自动 SQL 概要文件。
自动优化过程
在优化过程中，将考虑并报告所有建议类型，但只能自动实施 SQL 概要文件（ACCEPT_SQL_PROFILES 任务参数设置为 TRUE 时）。在其它情况下，仅在自动 SQL 优化报表中报告创建 SQL 概要文件的建议。
在 Oracle Database 11g 中，性能改善系数必须至少等于三，才会实施 SQL 概要文件。正如我们所看到的，自动 SQL 优化过程仅自动实施 SQL 概要文件建议。在 SQL 优化过程中会生成其它建议（用于创建新索引、刷新过时统计信息或调整 SQL 语句），但并不实施这些建议。您需要对这些建议进行检查，然后在适当的情况下手动实施。
下面简要描述了常规自动优化过程：
优化以语句为单位执行。因为只能实施 SQL 概要文件，所以不需要考虑此类建议对工作量的整体影响。对于每条语句（按重要性排序），优化过程将执行以下各个步骤：
- 使用 SQL 优化指导优化语句。查找 SQL 概要文件；如果找到了此概要文件，则验证其基础优化程序统计信息是否为最新。
- 如果建议了某个 SQL 概要文件，则执行以下操作：
-通过在使用该建议和不使用该建议两种情况下执行语句，测试新的 SQL 概要
文件。
-如果生成了 SQL 概要文件，并且该文件导致优化程序为该语句选择了一个不同的执行计划，则优化指导必须确定是否实施 SQL 概要文件。优化指导将根据幻灯片中的流程图做出决定。虽然此处的性能提高阈值适用于 CPU 时间和输入/输出 (I/O) 时间的总和，但是，如果其中任意一方的统计信息表现出性能下降，就不会接受 SQL 概要文件。因此，要求 CPU 时间和 I/O 时间的总和改善三倍，并且其中任意一方的统计信息中没有表现出性能下降。通过这种方式，语句的运行速度将比不使用概要文件时快，即使出现 CPU 或 I/O 的争用情况也是如此。
- 如果发现了过时或丢失的统计信息，则将此类信息提供 GATHER_STATS_JOB。
自动实施优化建议仅适用于 SQL 概要文件，因为 SQL 概要文件的风险较小，还原实施很容易。
注：所有 SQL 概要文件都是以标准 EXACT 模式创建的。系统会根据 CURSOR_SHARING 参数的当前值匹配和跟踪这些概要文件。您负责为工作量正确地设置 CURSOR_SHARING。
自动 SQL 优化控制
- 自动任务配置：
–打开/关闭开关
–运行优化任务的维护窗口
–优化任务的 CPU 资源消耗
- 任务参数：
–SQL 概要文件实施自动/手动开关
–优化任务的全局时间限制
–优化任务的每个 SQL 的时间限制
–禁用测试-执行模式以节省时间
–为每个执行以及从整体上自动实施的最大 SQL 概要
文件数
–任务执行有效期
以下是自动 SQL 优化任务的一个 PL/SQL 控制示例：
BEGIN
dbms_sqltune.set_tuning_task_parameter(‘SYS_AUTO_SQL_TUNING_TASK’, ‘LOCAL_TIME_LIMIT’, 1400);
dbms_sqltune.set_tuning_task_parameter(‘SYS_AUTO_SQL_TUNING_TASK’, ‘ACCEPT_SQL_PROFILES’, ‘TRUE’);
dbms_sqltune.set_tuning_task_parameter(‘SYS_AUTO_SQL_TUNING_TASK’, ‘MAX_SQL_PROFILES_PER_EXEC’, 50);
dbms_sqltune.set_tuning_task_parameter(‘SYS_AUTO_SQL_TUNING_TASK’, ‘MAX_AUTO_SQL_PROFILES’, 10002);
END;
此示例中的最后三个参数仅在自动 SQL 优化任务中受支持。您还可以使用 LOCAL_TIME_LIMIT 或 TIME_LIMIT 之类的参数；这些参数是传统 SQL 优化任务的有效参数。一个重要的示例是通过使用 TEST_EXECUTE 参数，禁用测试-执行模式（以节省时间），并仅使用执行计划成本做出有关性能的决定。
此外，还可以控制自动 SQL 优化任务的运行时间以及允许使用的 CPU 资源。
自动 SQL 优化任务
如前所述，自动 SQL 优化是作为自动维护任务实施的；该任务本身称为“自动 SQL 优化”。在“Automated Maintenance Tasks（自动维护任务）”页中可以看到与最近运行的自动 SQL 优化任务相关的一些高级别信息：要打开此页，请在 Database Control 主页中单击“Server（服务器）”选项卡。在打开的“Server（服务器）”选项卡式页中，单击“Tasks（任务）”部分中的“Automated Maintenance Tasks（自动维护任务）”链接。
在“Automated Maintenance Tasks（自动维护任务）”页上，可以看到预定义的任务。然后，单击相应的链接访问每个任务，获取有关任务本身的详细信息（如幻灯片中所示）。单击“Automatic SQL Tuning（自动 SQL 优化）”链接或最近的执行的图标（时间表上的绿色区域）时，“Automatic SQL Tuning Result Summary（自动 SQL 优化结果概要）”页就会打开。
配置自动 SQL 优化
可以使用“Automatic SQL Tuning Settings（自动 SQL 优化设置）”页配置各种自动 SQL 优化参数。
要定位至该页，请单击“Automated Maintenance Tasks（自动维护任务）”页上的“Configure（配置）”按钮。此时您会看到“Automated Maintenance Tasks Configuration（自动维护任务配置）”页，可在其中查看 Oracle Database 11g 提供的各种维护窗口。
默认情况下，自动 SQL 优化可在 MAINTENANCE_WINDOW_GROUP 中的所有预定义维护窗口上运行。可以针对一周中的特定日期禁用自动 SQL 优化。在此页上，还可以编辑每个窗口以更改其特性，方法是单击“Edit Window Group（编辑窗口组）”按钮。
要定位至“Automatic SQL Tuning Settings（自动 SQL 优化设置）”页，请在“Task Setting（任务设置）”部分中单击与“Automatic SQL Tuning（自动 SQL 优化）”对应的行上的“Configure（配置）”按钮。
在“Automatic SQL Tuning Settings（自动 SQL 优化设置）”页上，可以指定幻灯片中显示的参数。默认情况下，“Automatic Implementation of SQL Profiles（自动实施 SQL 概要文件）”处于未选中状态。
注：如果将 STATISTICS_LEVEL 设置为 BASIC，使用 BMS_WORKLOAD_REPOSITORY 关闭 AWR 快照，或者 AWR 保留期限少于七天，也会停止自动 SQL 优化。
自动 SQL 优化：结果概要
此外，“Automatic SQL Tuning Result Summary（自动 SQL 优化结果概要）”页还包含各种概要图形，您可以用来控制自动 SQL 优化任务。幻灯片中显示了一个示例。“Overall Task Statistics（总体任务统计信息）”部分的第一个图形按指定时段的查找结果类型显示细分。通过选择“Time Period（时段）”列表中的值，可以控制要为其生成报表的时段。示例中使用了“Customized（定制）”，显示的是最近一次运行。可以选择“All（所有）”涵盖迄今为止该任务的所有执行。用户可以请求过去一个月中任何时段的运行，因为这是优化指导保留其优化历史记录的时间期限。然后，单击“View Report（查看报表）”生成报表。
在“Breakdown by Finding Type（按照查找结果细分）”图形上，可以清楚地看到仅 SQL 概要文件可以实施。虽然还推荐了许多其它概要文件，但并非所有概要文件都被自动实施，原因如前所述。类似地，创建索引的建议以及其它类型的建议不会被实施。但是，如果希望以后实施这些建议，优化指导会保留与所有这些建议有关的历史信息。
在“Profile Effect Statistics（概要文件效果统计信息）”部分，可以看到“Tuned SQL DB Time Benefit（优化后的 SQL DB 时间优势）”图形，该图形显示了实施概要文件和其它建议前后的 DB 时间。
自动 SQL 优化：结果详细资料
在“Automatic SQL Tuning Result Details（自动 SQL 优化结果详细资料）”页上，还可以看到各个自动优化的 SQL 语句的重要信息，包括语句的 SQL 文本和 SQL ID、SQL 优化指导执行的建议类型、经过验证的性能提高百分比、是否自动实施了特定建议以及建议的日期等。
在此页中，可以单击 SQL 语句的相应 SQL ID 链接细化到 SQL 语句本身；也可以选择其中一条 SQL 语句，然后单击“View Recommendations（查看建议）”按钮，了解有关该语句的建议的其它详细资料。
注：所显示的每个建议的性能提高百分比是使用以下公式得出的：
bnf% = (time_old – time_new)/(time_old)。使用此公式，您可以看出三倍性能优势（例如，time_old = 100、time_new = 33）的对应值为 66%。因此，该系统实施所有性能提高超过 66% 的概要文件。根据此公式，98% 表示获得 50 倍的优势。
自动 SQL 优化结果详细资料：细化
在“Recommendations for SQL ID（以下 SQL ID 的建议）”页上，可以看到对应的建议，可以手动实施这些建议。
单击“SQL Test（SQL 测试）”链接可以访问“SQL Details（SQL 详细资料）”页；在此页上可以看到优化历史记录以及与 SQL 语句关联的计划控制。
在幻灯片中，可以看到自动 SQL 优化已经优化了该语句，并且已自动实施了关联的概要文件。
自动 SQL 优化注意事项
- 自动 SQL 优化不考虑的 SQL：
–临时 SQL 或极少重复的 SQL
–并行查询
–概要分析后长时间运行的查询
–递归 SQL 语句
–DML 和 DDL
- 仍可使用 SQL 优化指导对上述语句进行手动优化。
自动 SQL 优化并不力求解决系统中出现的所有 SQL 性能问题。它不考虑以下类型的 SQL：
- 临时 SQL 或极少重复的 SQL：如果某条 SQL 不以相同的形式执行多次，则优化指导将忽略该语句。在一周内没有重复出现的 SQL 也不在考虑之列。
- 并行查询。
- 长时间运行的查询（概要分析后）：如果某个查询在经过 SQL 概要分析后运行的时间太长，则进行测试-执行就不很实际，因此优化指导将其忽略。请注意，这并不意味着优化指导将忽略所有长时间运行的查询。如果优化指导可以找到一个 SQL 概要文件，让原本花费几小时的查询在几分钟内运行，则可能接受此概要文件，因为仍然可以进行测试-执行。优化指导会用足够长的时间执行旧计划，确定旧计划劣于新计划后，就会终止测试-执行而不等待旧计划完成，并因此切换执行的顺序。
- 递归 SQL 语句
- DML，例如 INSERT SELECT 或 CREATE TABLE AS SELECT
除了真正的临时 SQL，上述限制仅适用于自动 SQL 优化。仍可手动运行 SQL 优化指导对上述语句进行优化。
Leave a Reply