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);