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 重写等多维度建议。：

统计信息分析检测缺失/过时的对象统计（如索引未分析）。
访问路径分析评估索引合理性，建议新建索引（如缺失高选择性索引）推荐缺失索引或物化视图（SQL Access Advisor）。
SQL 结构分析重写低效语法（如子查询解嵌套、NOT IN → NOT EXISTS）。
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_snapshot3  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 NULLAND ash.TIME_WAITED > 100
ORDER BY ash.SAMPLE_TIME DESC;  -- 按采样时间排序
---

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

-- 创建任务（支持 SQL_ID 或 SQL 文本），从AWR或是top sql中查询SQL ID
--通过
DECLAREtask_name VARCHAR2(30);
BEGINtask_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 fromsys.obj$ o where o.obj# = c.obj#))and c.obj# < :1  andrownum <=  :2
………………
ALTERNATIVE PLANS SECTION
---------------------------------------------------------
----------------------Plan 1
------Plan Origin                 :Cursor Ca
chePlan Hash Value             :159303012Executions                  :42Elapsed Time                :0.006 secCPU Time                    :0.005 secBuffer Gets                 :2680Disk Reads                  :2Disk Writes                 :0Notes: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
BEGINDBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'tuning_task1',name      => 'profile_force_index',force_match => TRUE  -- 12c+ 支持结构相似 SQL 匹配);
END;
/-- 手工创建 Profile（强制索引）
BEGINDBMS_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;      -- 必要执行权限

版本差异验证：

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

效果验证：

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