SQL Tuning Advisor（SQL 调优顾问）
Oracle 10g的查询优化器具有自动SQL调整功能。它基于CBO优化器，调用DBMS_SQLTUNE包进行SQL优化
SQL Tuning Advisor （STA）将一个或多个 SQL 语句作为输入并调用自动调优优化器对语句执行 SQL 调优。 SQL Tuning Advisor 的输出采用一个或多个建议的形式，以及每个建议的基本原理及其预期收益。该建议涉及对象统计信息的收集、新索引的创建、SQL 语句的重组或 SQL 配置文件的创建。酌情选择是否接受建议以完成 SQL 语句的调优。
SQL Tuning Advisor 输入可以是单个 SQL 语句或一组语句。要对多个语句进行调优，必须首先创建 SQL TUNING SET (STS)。 STS 是一个数据库对象，用于存储 SQL 语句及其执行上下文。 STS 可以使用命令行 API 手动创建，也可以使用 Oracle Enterprise Manager 自动创建。
体系架构
SQL TUNING SET 来源
- 通过ADDM发现的高负载的SQL
- 处于缓存中的SQL
- 来自AWR中的SQL
- 用户定义的SQL系列
查询优化器模式
- Normal mode：在正常模式下，优化器编译 SQL 并生成执行计划。优化器的正常模式为绝大多数 SQL 语句生成合理的执行计划。在正常模式下，优化器在非常严格的时间限制下运行，通常是几分之一秒，在此期间它必须找到一个好的执行计划。
- Tuning mode：在调优模式下，优化器会进行额外的分析，以检查正常模式下生成的执行计划是否可以进一步改进。查询优化器的输出不是执行计划，而是一系列操作，以及它们产生显着优越计划的基本原理和预期收益。
- 在调优模式下调用时，优化器被称为自动调优优化器。由自动调优优化器执行的调优称为自动 SQL 调优（Automatic SQL Tuning）。
- 在调优模式下，优化器可能需要几分钟时间来调优单个语句。每次必须对查询进行硬解析时，调用自动调整优化器都会占用大量时间和资源。 Automatic Tuning Optimizer 旨在用于对整个系统具有重大影响的复杂和高负载 SQL 语句。自动数据库诊断监视器 (ADDM) 主动识别适合自动 SQL 调优的高负载 SQL 语句。
调优分析的类型
统计数据分析（Statistics Analysis）
查询优化器依赖对象统计来生成执行计划。如果这些统计信息过时或丢失，优化器就没有它需要的必要信息，并且可能生成糟糕的执行计划。 Automatic Tuning Optimizer （自动调优优化器）检查每个查询对象是否有缺失或陈旧的统计信息，并产生两种类型的输出：
- 为具有陈旧或没有统计信息的对象收集相关统计信息的建议。
- 没有统计数据的对象以统计形式提供的辅助信息，以及具有陈旧统计数据的对象的统计调整因子。
此辅助信息存储在称为 SQL 配置文件的对象中。
SQL配置文件分析（SQL Profiling）
检验其在统计数据分析阶段进行的分析，并可能采用各种优化提示，来尝试多种执行方案
Automatic Tuning Optimizer 创建 SQL 语句的概要文件，称为 SQL 概要文件，由特定于该语句的辅助统计信息组成。正常模式下的查询优化器对基数、选择性和成本进行估计，这些估计有时会大量偏离，从而导致执行计划不佳。 SQL Profile 通过使用抽样和部分执行技术收集附加信息来验证并在必要时调整这些估计值来解决此问题。
访问路径分析（Access Path Analysis）
通过减少对大型表进行全表扫描的需要，索引可以极大地提高 SQL 语句的性能。有效的索引是一种常见的调优技术。 Automatic Tuning Optimizer 还探索新索引是否可以显着提高查询的性能。如果确定了这样的索引，它会建议创建它。
由于自动调优优化器不会分析其索引建议如何影响整个 SQL 工作负载，因此它还建议对 SQL 语句以及具有代表性的 SQL 工作负载运行 SQLAccess Advisor 实用程序。在提出任何建议之前，SQLAccess Advisor 会查看创建索引对整个 SQL 工作负载的影响。
SQL 结构分析（SQL Structure Analysis）
Automatic Tuning Optimizer 识别可能导致性能不佳的 SQL 语句结构的常见问题。这些可能是语句的句法、语义或设计问题。在每种情况下，自动调优优化器都会提出相关建议来重构 SQL 语句。
使用STA优化SQL
流程
- 创建 SQL 调优集（如果调优多个 SQL 语句）
- 创建 SQL 调优任务
- 执行 SQL 调优任务
- 显示 SQL 调优任务的结果
- 酌情实施建议方案
所需权限
执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限：
grant advisor to user_name;
创建 SQL 调优集
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT /*+ ORDERED */ * ' ||
'FROM employees e, locations l, departments d ' ||
'WHERE e.department_id = d.department_id AND ' ||
'l.location_id = d.location_id AND ' ||
'e.employee_id < :bnd';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'HR',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_task',
description => 'Task to tune a query on a specified employee');
END;
/
执行 SQL 调优任务
-- 获取任务名称
SELECT task_name FROM DBA_ADVISOR_LOG where owner = 'HR';
-- 执行任务
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
END;
/
检查 SQL 调优任务的状态
SELECT task_name, status FROM dba_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task';
检查 SQL Tuning Advisor 的进度
SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE user_name = 'HR' AND task_name = 'my_sql_tuning_task';
显示 SQL 调优任务的结果
SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task')
FROM DUAL;
删除SQL调优任务
exec dbms_sqltune.drop_tuning_task('my_sql_tuning_task');
