Oracle DB 自动SQL 优化

• 设置和修改自动SQL 优化

• 使用PL/SQL  接口进行微调

• 查看和解释自动SQL 优化生成的报表

• Oracle Database 10g 中的SQL 优化

Oracle Database 10g 中的SQL 优化

Oracle Database 10 g 引入了SQL 优化指导，用于帮助DBA 和应用程序开发人员提高SQL  语句的性能。该指导可以解决编写不佳的SQL 问题；这些语句没有采用最有效的方式进 行设计。此外，该优化指导还可以解决SQL 语句执行效果较差的问题（此问题较常 见），对于这些SQL 语句，优化程序由于缺乏精确的相关数据统计信息而生成了较差的 执行计划。在所有情况下，该指导都会提供具体的建议来提高SQL 性能，但是否实施建 议案由用户决定。

除了SQL 优化指导以外，Oracle Database 10 g 还有一个自动进程，可确定系统中的高负载 SQL 语句。自动数据库诊断监视程序(ADDM)  就是这样的进程，它可自动确定应进行优 化的高负载SQL 语句。

但是，还是存在一些重要问题：虽然ADDM 确实可以确定一些应该进行优化的SQL ，但 用户仍必须手动查看ADDM 报表，然后根据这些报表运行 SQL 优化指导以进行优化。

• Oracle Database 11g 中的自动 SQL 优化

Oracle Database 11g 中的自动SQL 优化

Oracle Database 11 g 可以确定有问题的SQL 语句，对这些语句运行SQL 优化指导，并实 施获得的SQL 概要文件建议案来优化语句，不需要用户的干预，因而进一步加强了SQL  优化进程的自动化程度。自动SQL 优化通过一个在默认情况下每晚都会运行的名为“自 动SQL 优化”的新任务使用AUTOTASK 框架。下面简要描述了Oracle Database 11 g 中 的自动SQL 优化进程：

步骤1：根据AWR 顶层SQL 标识（在以下四个不同时间段处于顶层的SQL ：过去一 周、过去一周中的任何一天、过去一周中的任何一小时或者单个响应时间），自动SQL  优化可以确定自动优化对象。

步骤2  和3：在维护窗口中执行自动SQL 优化任务时，将通过调用SQL 优化指导自动优 化以前确定的SQL 语句。因此，将根据需要为这些语句创建SQL 概要文件。但是，在做 出决定之前，需要认真测试新的概要文件。

步骤4：在任何时间点都可以请求有关这些自动优化活动的报表。 然后，可以选择检查优化的SQL 语句以验证或删除生成的自动SQL 概要文件。

• Oracle Database 11g 中的自动化功能概要

• 自动运行任务（AUTOTASK  框架）

• 自动选择工作量（无SQL 优化集）

• 自动测试SQL 概要文件

• 自动实施SQL 概要文件

• 自动返回回归的SQL 

• 在任意时段进行报告

• 选择可能的 SQL 语句以进行优化

选择可能的SQL 语句以进行优化

Oracle Database 11 g 会分析AWR 中的统计信息，并生成可以进行优化的SQL 语句列表。 这些语句包括对系统有显著影响的重复性高负载语句。仅优化那些其执行计划具备很高的 改进潜力的SQL 语句。忽略递归SQL 和最近（上个月）优化过的语句，如并行查询、 DML、DDL 和带有并发性问题导致的性能问题的SQL 语句。 然后，被选为候选语句的SQL 语句将按其性能影响进行排序。SQL 语句的性能影响按如 下方法进行计算：将AWR 中对于过去一周中的该SQL 语句捕获的CPU 时间和I/O  时间 相加。

• 维护窗口时间表

维护窗口时间表

自动SQL 优化进程在维护窗口中进行。此外，还作为单个AUTOTASK 作业的一部分在单 个实例上运行该进程以避免并发性问题。图中介绍了一种方案。

在此方案中，在维护窗口开始后的某个时间，AUTOTASK 启动了自动SQL 优化作业 ( SYS_AUTO_SQL_TUNING_TASK) 。该作业将首先根据AWR 来源生成要用于优化的候选 SQL 列表。完成此列表后，该作业将按重要性顺序依次优化每个语句（一次只考虑一个语 句）。在此方案中，该作业首先优化了S1；SQL 优化指导为其生成了一个SQL 概要文件 建议案( P1) 。成功测试了P1后，就会接受P1，作业继续优化下一个语句S2。 默认情况下，自动SQL 优化在维护窗口中最多运行一个小时。可以使用类似于下面的调用 更改此设置：

dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 

'TIME_LIMIT', 7200);  

注：图中框的宽度并不表示相对的执行时间。执行优化和测试应是迄今为止成本最高 的进程，其它所有进程的完成速度相对较快。

• 自动优化进程

自动优化进程

使用已经建立和排序的候选SQL 列表，就可以使用SQL 优化指导优化语句了。在优化过 程中，将考虑并报告所有建议案类型，但只能自动实施SQL 概要文件 （ACCEPT_SQL_PROFILES 任务参数设置为TRUE 时）。在其它情况下，仅在自动SQL  优化报表中报告要创建SQL 概要文件的建议案。

在Oracle Database 11 g 中，在实施SQL 概要文件之前，必须至少有三项性能提高因素。

正如我们所看到的，自动SQL 优化进程仅自动实施SQL 概要文件建议案。在SQL 优化 进程中会生成其它建议案（用于创建新索引、刷新过时统计信息或调整SQL 语句），但 并不实施这些建议案。DBA 需对这些建议案进行检查，然后在适当的情况下手动实施。

下面简要描述了常规优化进程：

将逐条对语句进行优化。因为 只能实施SQL 概要文件，所以不需要考虑此类建议案对工 作量的整体影响。对于每条语句（按重要性排序），优化进程将执行以下各个步骤：

1. 使用SQL 优化指导优化语句。查找SQL 概要文件；如果找到了此概要文件，则验证 其基础优化程序统计信息是否为最新。

2. 如果建议了某个SQL 概要文件，则执行以下操作：

- 使用和不使用该建议案执行语句，测试新的SQL 概要文件。

- 如果生成了SQL 概要文件，并且该文件导致优化程序为该语句选择了一个不同的 执行计划，则优化指导必须确定是否实施SQL 概要文件。优化指导将根据 流程图做出决定。虽然此处的优势阈值适用于CPU 时间和I/O  时间的总和， 但是，如果统计信息表现出性能下降，就不会接收SQL 概要文件。因此，要求 CPU 时间和I/O  时间的总和改善三倍，并且统计信息中没有表现出性能下降。通过 这种方式，语句的运行速度将比不使用概要文件时快，甚至会出现CPU 或I/O  的 争用情况。

3. 如果找到了过时或丢失的统计信息，则将此类信息提供给GATHER_STATS_JOB 。

自动实施优化建议案仅适用于SQL 概要文件，因为SQL 概要文件的风险较少，DBA 很容 易还原实施。

注：所有SQL 概要文件都是在标准EXACT模式下创建的。系统会根据CURSOR_SHARING 参数的当前值匹配和跟踪这些概要文件。DBA 负责为这些概要文件的工作量设置相应的 CURSOR_SHARING 参数。

• DBA 控制

• 自动任务配置：

– 打开/关闭开关

– 运行优化任务的维护窗口

– 优化任务的 CPU 资源消耗

• 任务参数：

– 实施SQL 概要文件的自动/手动开关

– 优化任务的全局时间限制

– 优化任务的每个 SQL 的时间限制

– 禁用以节省时间的测试-执行模式

– 按执行以及整体上自动实施的最大 SQL 概要文件数

– 任务执行有效期

DBA 控制

以下是自动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‘, ’MA X_SQL_PROFILES_PER_EXEC‘, 50);

dbms_sqltune.set_tuning_task_parameter(’SYS_AUTO_SQL_TUNING_TASK‘, ’MA X_AUTO_SQL_PROFILES‘, 10002);

END;

自动SQL 优化任务仅支持此示例中的最后三个参数。还可以使用LOCAL_TIME_LIMIT 或TIME_LIMIT之类的参数；这些参数是传统SQL 优化任务的有效参数。一个重要的示

例是禁用测试-执行模式（以节省时间），以及仅使用执行计划成本做出有关性能的决定 （使用TEST_EXECUTE参数）。

此外，还可以控制自动SQL 优化任务的运行时间以及允许使用的CPU 资源。

• 自动SQL 优化任务

自动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 优化

配置自动SQL 优化

可以使用“Automatic SQL Tuning Settings（自动SQL 优化设置）”页配置各种自动SQL  优化参数。

要访问该页面，请单击“Automated Maintenance Tasks（自动维护任务）”页上的 “Configure（配置）”按钮。此时将显示“Automated Maintenance Tasks Configuration （自动维护任务配置）”页，可在其中查看随Oracle Database 11 g 提供的各种维护窗口。

默认情况下，自动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，使用DBMS_WORKLOAD_REPOSITORY 关闭AWR 快照，或者AWR 保留期限少于七天，则也会停止自动SQL 优化。

• 自动SQL 优化结果概要

自动SQL 优化结果概要

此外，“Automatic SQL Tuning Result Summary（自动SQL 优化结果概要）”页还包含各 种概要图形，您可以用来控制自动SQL 优化任务。幻灯片中显示了一个示例。“Overall  Task Statistics （总体任务统计信息）”部分的第一个图形按指定时段的查找结果类型显示 细分。通过选择“Time Period （时段）”列表中的值，可以控制要为其生成报表的时段。

示例中使用了“All（所 有）”涵盖迄今为止该任务的所有执行。用户可以请求过去一个月中任何时段的运行，因 为这是优化指导保留其优化历史记录的时间期限。然后，单击“View Report（查看报 表）”生成报表。

在“Breakdown by Finding Type（按照查找结果细分）”图形上，可以清楚地看到仅SQL  概要文件可以实施。虽然还推荐了许多其它概要文件，但并非所有概要文件都被自动实 施，原因如前所述。类似地，创建索引的建议案以及其它类型的建议案不会被实施。但 是，如果希望以后实施这些建议案，优化指导会保留与所有这些建议案有关的历史信息。

在“Profile Effect Statistics（概要文件效果统计信息）”部分，可以看到“Tuned SQL DB  Time Benefit（优化的SQL DB  时间优势）”图形，该图形显示了实施概要文件和其它建 议案前后的DB 时间。

• 自动SQL 优化：结果详细资料

自动SQL 优化：结果详细资料

在“Automatic SQL Tuning Result Details （自动SQL 优化结果详细资料）”页上，还可以 看到各个自动优化的SQL 语句的各种重要信息，包括语句的SQL 文本和SQL ID、SQL 

优化指导执行的建议案类型、经过验证的优势百分比、是否自动实施了特定建议案以及建 议案的日期等。

在此页中，可以单击SQL 语句的相应SQLID 链接细化到SQL 语句本身；也可以选择其 中一条SQL 语句，然后单击“View Recommendations （查看建议案）”按钮，了解有关 该语句的建议案的其它详细资料。

注：所显示的每个建议案的优势百分比是使用以下公式得出的：

bnf% = (time_old - time_new)/(time_old) 。使用此公式可以看到获得了三倍的 优势（例如，time_old = 100 、time_new = 33），对应值为66% 。因此，该系统实施 任何概要文件都可获得超过66% 的优势。根据此公式，98% 表示获得50 倍的优势。

• 自动SQL 优化结果概要：细化

自动SQL 优化结果概要：细化

在“Recommendations for SQL ID（以下SQL ID 的建议案）”页上，可以看到对应的建议 案，可以手动实施这些建议案。

单击“SQL Test （SQL 测试）”链接可以访问“SQL Details （SQL 详细资料）”页；在 此页上可以看到优化历史记录以及与SQL 语句关联的计划控制。

在图中，可以看到自动SQL 优化已经优化了该语句，并且已自动实施了关联的概要 文件。

• 自动SQL 优化：微调

• 使用DBMS_SQLTUNE：

– SET_TUNING_TASK_PARAMETER 

– EXECUTE_TUNING_TASK 

– REPORT_AUTO_TUNING_TASK

• 使用DBMS_AUTO_TASK_ADMIN：

– ENABLE 

– DISABLE

• 字典视图：

– DBA_ADVISOR_EXECUTIONS 

– DBA_ADVISOR_SQLSTATS 

– DBA_ADVISOR_SQLPLANS 

自动SQL 优化：微调

可以使用DBMS_SQLTUNE PL/SQL 程序包控制SYS_AUTO_SQL_TUNING_TASK 的各个 方面。

1. SET_TUNING_TASK_PARAMETERS ：系统仅对自动优化任务支持以下参数：

- ACCEPT_SQL_PROFILES：TRUE/FALSE，系统是否自动接受SQL 概要文件

- REPLACE_USER_SQL_PROFILES：TRUE/FALSE，任务是否替换用户创建的 SQL 概要文件

- MAX_SQL_PROFILES_PER_EXEC：为每次运行创建的最大SQL 概要文件数量

- MAX_AUTO_SQL_PROFILES ：系统中允许的自动SQL 概要文件最大总数

- EXECUTION_DAYS_TO_EXPIRE：指定在优化指导框架方案中任务历史记录的 保存天数。默认情况下，任务历史记录保存30 天后即失效

2. EXECUTE_TUNING_TASK 函数：用于手动在前台运行新的任务执行（行为方式与在 后台运行相同）

3. REPORT_AUTO_TUNING_TASK ：获取一个涵盖一系列任务执行的文本报表

可以使用DBMS_AUTO_TASK_ADMIN PL/SQL程序包启用和禁用 SYS_AUTO_SQL_TUNING_TASK。

也可以通过幻灯片中列出的字典视图访问自动SQL 优化信息：

• DBA_ADVISOR_EXECUTIONS：获取有关各个任务执行的数据

• DBA_ADVISOR_SQLSTATS：查看在测试SQL 概要文件时生成的测试-执行统计信息

• DBA_ADVISOR_SQLPLANS：查看在测试-执行过程中遇到的计划

• 使用PL/SQL  接口生成报表

使用PL/SQL  接口生成报表

图中的示例显示了如何生成一个文本报表，用于显示在最近的执行中分析的所有 SQL 语句，包括未实施的建议案。该报表总共包括以下几个部分：

根据报表中的各个部分，可以在报表的以下部分中查看有关自动SQL 优化任务的信息：

• 一般信息部分提供对自动SQL 优化任务的高级别描述，包括有关为报表提供的输入 的信息、维护过程中优化的SQL 语句数量以及创建的SQL 概要文件数量等。

• 概要部分列出了在维护窗口期间优化的SQL 语句（按SQL ID 排列），以及每个SQL  概要文件的估计优势或者使用SQL 概要文件测试-执行了SQL 语句之后概要文件的 实际执行统计信息。

• 优化查找结果部分提供与每个SQL 语句关联的所有查找结果和统计信息，以及是否 接受了概要文件（及接受原因）的信息。

• 解释计划部分显示SQL 优化指导分析的每个SQL 语句所用的新旧解释计划。

• 错误部分列出了自动SQL 优化任务遇到的所有错误。

注：使用以下命令可获取执行列表：

select execution_name,status,execution_start,execution_end   from dba_advisor_executions  where task_name='SYS_AUTO_SQL_TUNING_TASK';

• 自动SQL 优化注意事项

• 自动SQL 优化不考虑的SQL：

– 临时SQL 或极少重复的 SQL 

– 并行查询

– 概要分析后长时间运行的查询

– 递归SQL 语句

– DML 和DDL 

• 仍可使用SQL 优化指导对上述语句进行手动优化

自动SQL 优化注意事项

自动SQL 优化并不寻求解决系统中出现的所有SQL 性能问题。它不考虑以下类型的 SQL ：

• 临时SQL 或极少重复的SQL ：如果某条SQL 不以相同的形式执行多次，则优化指导 将忽略该语句。在一周内没有重复出现的SQL 也不在考虑之列。

• 并行查询。

• 长时间运行的查询（概要分析后）：如果某个查询在经过SQL 概要分析后运行的时 间太长，则进行测试-执行就不很实际，因此优化指导将其忽略。请注意，这并不意 味着优化指导将忽略所有长时间运行的查询。如果优化指导可以找到一个SQL 概要 文件，让原本花费几小时的查询在几分钟内运行，则可能接受此类查询，因为仍然可 以进行测试-执行。优化指导会用足够长的时间执行旧计划，确定旧计划劣于新计划 后，就会终止测试-执行而不等待旧计划完成，并在此切换执行的顺序。

• 递归SQL 语句

•DML，如INSERT SELECT 或CREATE TABLE AS SELECT

除了真正的临时SQL ，上述限制仅适用于自动SQL 优化。仍可手动运行SQL 优化指导对 上述语句进行优化。

• 小结

• 设置和修改自动SQL 优化

• 使用PL/SQL  接口进行微调

• 查看和解释自动SQL 优化生成的报表