56-Oracle SQL Tuning Advisor(STA)

各位小伙伴，一般都用哪些优化工具，Oracle SQL Tuning Advisor (STA)用的多吗，Profile就是它的其中1个产物，下一期再弄Profile，STA 的核心功能是自动化诊断高负载SQL的性能瓶颈​（如全表扫描、缺失索引），通过深度分析执行计划提供优化建议（如索引创建、SQL结构重写），并生成SQL Profile，不侵入、在不修改原SQL的前提下注入优化器指令，强制修正基数估计偏差或访问路径，从而提升查询效率。

​解放双手显著降低人工调优成本，尤其对复杂查询和大规模数据场景，能快速提供量化收益（如索引优化提升性能），还可以优化适配第三方封装SQL；不过优化同样不是万能的，STA依赖统计信息准确性（过期统计会导致建议失效）且仅针对单条SQL优化，可能忽略全局影响（如索引增加写负载），复杂逻辑场景需要人工参与和经验的判断。结合AWR定位TOP SQL后调用STA生成方案，关键建议需测试验证，并定期复审Profile有效性，以适配数据与业务侧的调整变更。

​一、STA技术原理与核心功能​

​1. 优化器双模式机制​

• ​Normal 模式​：毫秒级生成执行计划，基于现有统计信息，受时间限制可能无法探索最优路径。
• ​Tuning 模式​：通过 ​Automatic Tuning Optimizer (ATO)​​ 深度分析（分钟级），突破时间限制模拟多种路径，输出优化建议而非直接计划。

​2. 四大分析维度​

ATO在 Tuning 模式下执行，整合统计信息、索引、SQL 重写等多维度建议。：

1. ​统计信息分析​检测缺失/过时的对象统计（如索引未分析）。
2. ​访问路径分析​评估索引合理性，建议新建索引（如缺失高选择性索引）推荐缺失索引或物化视图（SQL Access Advisor）。
3. ​SQL 结构分析​重写低效语法（如子查询解嵌套、NOT IN → NOT EXISTS）。
4. ​SQL Profiling​注入动态采样或修正因子（如基数缩放），持久化存储于数据字典，优先级高于原始统计信息。

​3. SQL Profile 工作（单开一期）​

• ​作用​：修正优化器成本计算误差（如基数估计偏差）。
• ​优先级​：高于 SQL 文本中的 Hint，​不修改原 SQL​。
• ​类型​：
  • ​自动 Profile​：由 STA 生成，动态适配数据变化。
  • ​手工 Profile​：强制指定执行计划（如固定连接顺序）。

二、演进过程与版本特性  

版本​	​关键特性​	​技术突破​
​10g	首次引入 STA	集成 AWR 高负载 SQL 捕获，支持基础分析
​11g	SQL Plan Management (SPM)	替代 Outline，支持执行计划稳定性控制
​12c	Adaptive Plans	运行时动态调整执行计划（如连接方式切换）
​19c	Automatic Indexing	自动创建/验证/删除索引，需启用AUTO_INDEX​
23ai​	AI 增强优化器	向量统计信息、自适应连接优化、直接连接语法

优化深度对比​： 

​模式​	​响应时间​	​优化深度​	​适用场景​
​Normal 模式​	毫秒级	浅层	常规 SQL 解析
​Tuning 模式​	分钟级	深度分析	高负载 SQL 优化

三、实践脚本（10g 至 23ai）​

1. 自动捕获高负载 SQL（10g~23ai）​

-- 查询V$SQL视图（实时TOP SQL）
SELECT sql_id, sql_text, executions, buffer_gets, disk_reads, elapsed_time
FROM v$sql 
WHERE buffer_gets > 10000   -- 过滤高内存消耗
   OR elapsed_time > 1000000 -- 过滤长耗时
ORDER BY elapsed_time DESC;  -- 按执行时间排序
-- 从 AWR 获取 TOP SQL ID
-- 创建AWR快照
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('TYPICAL');  -- 起始快照

-- 等待高负载时段，后再次创建快照
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('TYPICAL');  -- 结束快照

-- 查询快照ID
SELECT snap_id, begin_interval_time 
FROM dba_hist_snapshot 
ORDER BY snap_id DESC;
--
SELECT snap_id, begin_interval_time
FROM dba_hist_snapshot
  3  ORDER BY snap_id DESC;

   SNAP_ID BEGIN_INTERVAL_TIME
---------- ---------------------------------------------------------------------------
      7022 21-6月 -25 11.00.34.946 上午
      7021 21-6月 -25 10.00.22.850 上午
      7020 21-6月 -25 09.00.10.116 上午
      7019 21-6月 -25 08.00.58.180 上午
      7018 21-6月 -25 07.00.46.532 上午
-- 生成AWR报告中高负载SQL（查询下面手工输入变量快照ID）
SELECT sql_id, executions, buffer_gets, disk_reads, elapsed_time, sql_text
FROM dba_hist_sqlstat 
WHERE snap_id BETWEEN &start_snap AND &end_snap   -- 替换为实际快照ID
ORDER BY buffer_gets DESC;  -- 按内存消耗排序
--不用awr，直接用ASH查询sql_id
-- 使用ASH实时监控（10g+）
SELECT 
    ash.SQL_ID, 
    ash.SESSION_ID, 
    sq.SQL_TEXT,  -- 从 V$SQL 获取文本
    ash.WAIT_TIME, 
    ash.TIME_WAITED
FROM 
    V$ACTIVE_SESSION_HISTORY ash
JOIN 
    V$SQL sq ON ash.SQL_ID = sq.SQL_ID  -- 关联 SQL 文本视图
WHERE 
    ash.SQL_ID IS NOT NULL
    AND ash.TIME_WAITED > 100
ORDER BY 
    ash.SAMPLE_TIME DESC;  -- 按采样时间排序
---

 2. 调优任务通过(sql_id、sql_text)建立

-- 创建任务（支持 SQL_ID 或 SQL 文本），从AWR或是top sql中查询SQL ID
--通过
DECLARE
  task_name VARCHAR2(30);
BEGIN
  task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id        => 'dqucusk8avvuh',   -- 11g+ 支持
    --sql_text      => 'SELECT * FROM HR.EPMLOYEES WHERE salary > :1',
    --sql_text和上面的sql_id，二选一均可10g+
    scope         => 'COMPREHENSIVE',
    time_limit    => 60,
    task_name     => 'tuning_task1'
  );
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name); -- 执行任务
END;
/
--PL/SQL 过程已成功完成。
-- 查看报告
SET LONG 1000000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tuning_task1') FROM DUAL; -- 输出优化建议
--
SYS@test19> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tuning_task1') FROM DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_TASK1')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------
---------------------------
Tuning Task Name   : tuning_task1
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 06/21/2025 12:12:15
Completed at       : 06/21/2025 12:12:15


----------------------------------------------------------
---------------------
Schema Name   : SYS
Container Name: PDBRS6
SQL ID        : dqucusk8avvuh
SQL Text      : delete /* KSXM:CLEAN_COL_USAG
E *//*+ dynamic_sampling(4) */
                 from sys.col_usage$ c   where
((timestamp < sysdate - 367)
                       or not exists
(select /*+ unnest */ 1 from
                sys.obj$ o where o.obj# = c.obj#))
    and c.obj# < :1  and
                rownum <=  :2
………………
ALTERNATIVE PLANS SECTION
---------------------------------------------------------
----------------------

Plan 1
------

  Plan Origin                 :Cursor Ca
che
  Plan Hash Value             :159303012

  Executions                  :42

  Elapsed Time                :0.006 sec

  CPU Time                    :0.005 sec

  Buffer Gets                 :2680

  Disk Reads                  :2

  Disk Writes                 :0

Notes:
  1. Statistics shown are averaged over multiple execu
tions.
  2. 在当前环境中无法重新生成具有 ID 1 的计划。由于此原因, 无法创建 SQL 计划基线以指示
Oracle 优化程序在将来选取该计划。

---------------------------------------------------------
----------------------------------------
------
| Id  | Operation                              | Name
         | Rows  | Bytes | Cost (%CPU)|
Time     |
-------------------------------------------------
----------------------------------------
--------------
|   0 | DELETE STATEMENT
  |              |       |       |    15
 (100)|          |
|   1 |  DELETE
      | COL_USAGE$   |       |       |
          |          |
|   2 |   COUNT STOPKEY                        |
     |       |       |            |
     |
|   3 |    FILTER                              |
         |       |       |            |
         |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED|
COL_USAGE$   |   150 |  2700 |    15   (
0)| 00:00:01 |
|   5 |      INDEX RANGE SCAN
  | I_COL_USAGE$ |    27 |       |     2
   (0)| 00:00:01 |
|   6 |     INDEX SKIP SCAN
      | I_OBJ1       |     1 |     5 |
   2   (0)| 00:00:01 |
---------------------------------------------------------
SYS@test19>

3. SQL Profile 接受应用（11g+）​

-- 接受自动 Profile
BEGIN
  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
    task_name => 'tuning_task1',
    name      => 'profile_force_index',
    force_match => TRUE  -- 12c+ 支持结构相似 SQL 匹配
  );
END;
/

-- 手工创建 Profile（强制索引）
BEGIN
  DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
    name     => 'manual_profile',
    sql_text => 'SELECT * FROM orders WHERE order_id=:1',
    profile  => SQLPROF_ATTR('INDEX(orders idx_order_id)') -- 注入 Hint
  );
END;
/

四、注意事项与验证​

• ​权限要求​：

GRANT ADVISOR, SELECT_CATALOG_ROLE TO user_STA; -- 基础权限
GRANT EXECUTE ON DBMS_SQLTUNE TO user_STA;      -- 必要执行权限

• ​版本差异验证​：

1. 10g：仅支持 SQL 文本调优，不支持 sql_id 参数。
2. 19c：自动索引需启用 OPTIMIZER_AUTO_INDEX。
3. 23ai：向量操作需安装 Vector Option 组件和依赖新发版的优化。

• ​效果验证​：

-- 对比优化前后执行计划
--举例HR.EMPLOYEES
EXPLAIN PLAN FOR SELECT * FROM HR.EMPLOYEES WHERE salary > 5000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);