自动重写顾问（Auto SQL Tuning Advisor）介绍

# 自动重写顾问（Auto SQL Tuning Advisor）介绍

## 一、产品定位与由来
Oracle 10g 首次推出 SQL Tuning Advisor，11g 又将其封装为“自动重写顾问”（Automatic SQL Tuning Advisor，下文简称 ASTA），成为默认的四大自动维护任务之一。其核心使命只有一句话：**在夜间低峰时段，无需人工干预，自动发现并修复高负载 SQL 的性能回归**。对 DBA 而言，ASTA 就像 7×24 的“夜班调优工程师”，把最常见的统计信息缺失、索引缺失、错误执行计划等问题在萌芽阶段解决。

## 二、技术原理总览
ASTA 依赖“双模式优化器”工作：

1. 正常模式（Normal Mode）
用户日常硬解析时，优化器必须在毫秒级内给出计划，采用贪心+成本阈值剪枝，可能错过最优路径。

2. 调优模式（Tuning Mode）
ASTA 在夜间维护窗口把高负载 SQL 载入内存，**解除时间限制**，调用“自动调优优化器”（Automatic Tuning Optimizer，ATO）做深度分析：
- 动态采样补充统计信息
- 模拟并评估多条备用计划
- 生成 SQL Profile、索引建议或结构重写方案
单条语句可花费数分钟，追求“理论最优”。

ATO 输出的不是执行计划，而是一组“重写动作”及预期收益，例如：
“创建复合索引 (STATUS, CREATE_TIME) 可把全表扫描成本从 12000 降到 120，预计节省 98 % 逻辑读”。

## 三、四大分析维度
ASTA 会对每条候选 SQL 做四类体检：

1. 统计信息分析（Statistics Analysis）
检查表/索引/列的统计信息是否过时或缺失，必要时调用动态采样重新收集，并给出“SCALE_ROWS”等修正因子。

2. 访问路径分析（Access Path Analysis）
评估新增索引、分区裁剪、位图索引、物化视图日志等路径，计算成本收益比。

3. SQL 性能分析（SQL Profiling）
构建“SQL Profile”——一组存储在数据字典里的**调优提示**，下次解析时让 CBO 使用更准确的列选择性、Join 成本、CPU 指令数，而无需改动 SQL 文本。

4. SQL 结构分析（SQL Structure Analysis）
识别写法缺陷，如 SELECT *、隐式转换、NOT IN 可改 NOT EXISTS、OR 可改 UNION ALL 等，给出重写模板。

## 四、自动实施与灰度策略
Oracle 11g 起支持“自动接受 SQL Profile”：

- 默认夜间任务只**生成建议**，不立即实施；
- 设置 `ACCEPT_SQL_PROFILES = TRUE` 后，若 ATO 验证收益 ≥ 3×，且 CPU/IO 采样无回归，则自动把 Profile 写入数据字典；
- 新计划与旧计划同时保存到 SQL Plan Baseline，若后续性能下降，SPM Evolve Advisor 会回退。

整个流程满足“可灰度、可回滚、可审计”：

- 所有变更记录于 `DBA_SQL_PROFILES` 与 `DBA_ADVISOR_ACTIONS`；
- 提供 `DBMS_SQLTUNE.DROP_SQL_PROFILE` 一键撤销；
- 若系统负载突增，自动任务可被 Resource Manager 暂停，防止“调优反而拖库”。

## 五、使用方式（实战 3 步走）

1. 开启自动任务
```sql
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
```

2. 查看昨夜报告
```sql
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('SYS_AUTO_SQL_TUNING_TASK') FROM dual;
```
输出示例：
```
Recommendation
--------------
- 创建索引 HR.IDX_BIG_ON_NAME (OBJECT_NAME)
估算收益: 97.4 % 逻辑读减少
自动实施: YES
状态: ACCEPTED
```

3. 手工复核（可选）
```sql
SELECT sql_id, plan_hash_value, executions, elapsed_time
FROM dba_hist_sqlstat
WHERE sql_id = 'xxxxxx'
ORDER BY snap_id DESC;
```
确认执行时间下降后，关闭任务或继续观察。

## 六、成功案例
某省电信计费系统升级至 19c 后，夜间出账批跑从 4 h 延长到 6 h。
ASTA 报告发现：
- 新表 BILL_DETAIL 无统计信息，导致 Nest Loop 被误选；
- 复合索引 (BILL_MONTH, REGION_ID) 缺失。

自动实施 SQL Profile + 索引建议后，同一批跑窗口恢复至 2.5 h，CPU 下降 30 %，且全程零人工干预。

## 七、边界与误区
1. 只解决“单条 SQL”局部最优，跨 SQL 的索引合并、业务语义重写仍需 DBA；
2. 对短时高频 OLTP 查询（<50 ms）收益有限，因动态采样开销可能大于收益；
3. 不支持改写业务逻辑，如把“拉单全表”改分页，需要开发配合；
4. 版本限制：11g 仅支持单实例，12c 后支持 CDB；19c 需 Exadata/云环境才能开“自动实施”。

## 八、一句话总结
自动重写顾问把“DBA 半夜加 hint”的传统打法，升级为**数据驱动、灰度验证、自动回滚**的闭环系统——让数据库在夜里悄悄给自己“动手术”，第二天上班只递上一份“已痊愈”的诊断报告。