Oracle SQL 调优 · PL/SQL Developer 手动模式
Overview
优化 Oracle 慢 SQL，但助手不连库、不碰密码。人类在 PL/SQL Developer 里自己登录、自己跑脚本，助手负责：探查环境 → 取得 SQL → 静态根因分析 → 出采集脚本 → 出改写 SQL → 解读执行计划 → 出索引与统计信息方案。
核心分工：人出数据（执行计划、源码、索引、行数），AI 出判断和改写。任何一方缺位都会得出错误结论。
三条硬规矩（违反必翻车）
- 不持有密码、不连库。所有库内动作由人执行。助手只产出脚本 + 解读。用户提出"你直接连"时，仍坚持由人执行；确需直连时先显式征求同意并让用户自行输入凭据。
- 只认真实执行计划。
DBMS_XPLAN.DISPLAY_CURSOR(sql_id, NULL, 'ALLSTATS LAST +PEEKED_BINDS')的输出才算数。PL/SQL Developer 里按 F5 得到的是估算计划，估算与实际经常不一致，按估算优化必然翻车。人只给了 F5 结果时，明确说明其局限并要求补真实计划。 - 每步问答推进。一轮只推进一个步骤，把需要用户回传的内容列成表格（要什么 / 怎么拿 / 为什么非拿不可），不擅自跳步、不臆造执行计划数据。
工作流
第 0 步：探查本机环境
先摸清家底，避免后面反复试错。执行 scripts/01_环境探查.ps1，或手工查这四项：
| 项 | 查什么 | 为什么 |
|---|---|---|
| PL/SQL Developer 主程序 | C:\Program Files\PLSQL Developer\plsqldev.exe（老版本可能在 D:\Program Files） |
拉起工具要用绝对路径 |
| Oracle 客户端 / sqlplus | 常见于 D:\app\<用户>\product\<版本>\dbhome\BIN\BIN\bin\sqlplus |
判断 OCI 库位置 |
TNS_ADMIN / ORACLE_HOME |
环境变量是否为空 | 为空 → 无 tnsnames，Database 框要填 IP:1521/服务名 |
tnsnames.ora 是否存在 |
D:\app 下 find |
决定连接方式 |
启动工具：Start-Process -FilePath "<plsqldev.exe 绝对路径>" -WorkingDirectory "<同目录>"。启动后用 tasklist.exe \| grep -c plsqldev 确认进程起来了——PowerShell 工具常不回显 stdout，别只看返回值判断成败。
登录阶段两个高发故障，提前预警：
- Database 下拉框为空 → 没有 tnsnames，填 EZCONNECT 完整串 IP:1521/服务名
- 报 ORA-12154 / Initialization error → Oracle Home 没指对，在 PL/SQL Developer 的 Tools → Preferences → Oracle / Connection 里指定 OCI 库目录
第 1 步：取得待优化 SQL
首选路径——读剪贴板。用户常把 SQL 贴在 PL/SQL Developer 窗口里或复制好说"已经贴好了"，但对话里看不到内容。此时不要要求用户重贴，直接取剪贴板。
三个必须绕开的坑（本机实测，会复现）：
- PowerShell 工具不回显 stdout ——
Write-Output/Get-Clipboard直接输出拿不到内容。必须写入文件，再用 Read 工具读取。 - Bash 禁止调用
powershell.exe—— 安全策略拦截，只能用 PowerShell 工具。 - Read 工具对超长行会截断（约 2000 字符）—— 单条 SQL 动辄 3000+ 字符。必须先按固定长度切片再读。
# 坑1+2 的正解：写文件，不依赖 stdout
$c = Get-Clipboard -Raw -ErrorAction SilentlyContinue
Set-Content -Path "<工作目录>\_clipboard.txt" -Value $c -Encoding UTF8
# 坑3 的正解：切片（1100 字符/段），再 Read
s = open(p, encoding='utf-8-sig').read()
open(out, 'w', encoding='utf-8').write('\n'.join(s[i:i+1100] for i in range(0, len(s), 1100)))
拿到完整 SQL 后先告知用户已取得原文、无需重贴，再进入分析。
第 2 步：静态根因分析（拿到 SQL 立刻可做）
不等执行计划也能做的事。按 P0 → P1 → P2 顺序扫一遍，详见 references/tuning-checklist.md。最快的判断顺序：
- WHERE 里有没有自定义函数（如
F_GETXXX(a.col) >= 1）→ 逐行调用，函数体内若还有 SQL 就是 N+1 次嵌套查询。这是头号嫌疑，必须拿函数源码。 - 有没有
LIKE '%' || :bind || '%'→ 前导通配符，B-tree 索引完全失效，必全表扫。 - 有没有内联视图 / CTE 无谓词地全表扫大表，且同一张表被扫多次。
- 外连接是否被自己废掉：
a.id = b.id(+)与 WHERE 里a.id = b.id同时出现 → 实际按内连接跑，属逻辑 Bug，需业务确认。 - 大表自关联、聚合后外连接 → 先干重活再筛。
输出时用表格：等级 / 问题 / 为什么不慢才怪 / 怎么改。
第 3 步：出采集脚本，向用户要关键输入
用 scripts/02_采集_执行计划.sql 和 scripts/03_关键采集_函数与索引.sql（含 &变量，PL/SQL Developer 原生弹窗替换）。
必须让用户回传的四样：
| # | 要什么 | 为什么非拿不可 |
|---|---|---|
| 1 | 自定义函数源码（all_source + all_arguments） |
不看到源码，函数类 P0 只能靠猜，无法安全展开成 EXISTS / 权限集合 JOIN |
| 2 | 现有索引清单（all_indexes + all_ind_columns） |
缺哪些索引一比就知道，不瞎建 |
| 3 | 各表行数与统计信息新鲜度 | 10 万行和 500 万行，优化策略完全不同 |
| 4 | 真实执行计划（ALLSTATS LAST） |
验证驱动顺序、连接方式判断是否正确 |
外加四项背景：Oracle 版本（11g / 12c / 19c 手段不是一个量级）、现在跑多久、期望多久、谁在调多久调一次。
第 4 步：出改写 SQL（分保守版 / 激进版）
- 保守版：语义等价改写，可直接上线。典型动作见
scripts/04_改写模板_保守版.sql——内联视图改 CTE、同一张表多次无谓词扫描改成多次直连、明确内/外连接语义、空绑定变量短路、补分页。 - 激进版：拿到函数源码和执行计划后才出。把权限函数展开成 EXISTS / 半连接、LIKE 改全文索引或前置匹配、重排驱动顺序。
改写的每条都要标注语义差异。例如加 :bind IS NULL OR (...) 短路后，原本所有匹配字段全为 NULL 的行会被额外选出——这类差异必须显式写明，不能悄悄改语义。
第 5 步：索引与统计信息方案
给出表 / 建议索引 / 作用三列清单。两条易忘的：
- 建了索引必须
DBMS_STATS.GATHER_TABLE_STATS（cascade => TRUE,no_invalidate => FALSE），否则优化器不知道有新索引，照样走全表。 - 生产库收集统计信息要在业务低峰跑，先确认没有并发大作业。
第 6 步：出可编辑 HTML 报告
复制 assets/report_template.html，填入分析结果。模板自带：双击改文字、保存到浏览器本地、导出 HTML。
报告结构固定为：结论先行（3 条致命伤）→ KPI 卡片 → 关联结构 SVG 图 → 问题清单表（P0/P1/P2）→ 改写后 SQL → 索引清单 → 下一步待回传项。
主题跟随 IDE：浅色主题用白底深字，深色主题用深底浅字，别写死黑色文字。
根因速查（精简版）
| 等级 | 症状 | 根因 | 修法 |
|---|---|---|---|
| P0 | WHERE 过滤位调用自定义函数 | 逐行 context switch，可能 N+1 嵌套查询 | 展开成 EXISTS / 权限集合 JOIN；或加 RESULT_CACHE 并把调用推迟到外层 |
| P0 | LIKE '%x%' |
前导 % 使 B-tree 索引失效 |
改前置匹配 x%；或 Oracle Text 全文索引；或多字段合并成一个搜索列 |
| P0 | 同一大表被无谓词扫多次 | 多个相同内联视图各自物化 | 去掉内联视图，多次直接 JOIN，让优化器合并扫描 |
| P0 | 索引列上用函数 / 隐式转换 | 索引列被包裹后不可用 | 改写成索引列裸比较，或建函数索引 |
| P1 | 外连接被 WHERE 等值条件废掉 | 语义冲突，优化器无法做视图合并 | 找业务确认内连接还是外连接，写清楚 |
| P1 | 统计信息过期（stale_stats = YES） |
优化器拿到错误行数估算 | DBMS_STATS.GATHER_TABLE_STATS |
| P1 | 聚合后外连接 | 先干重活再筛 | 补复合索引；或改标量子查询由主表驱动 |
| P1 | 绑定变量窥探 | 不同输入选择性差异巨大，一个计划吃遍所有 | 空值短路；高选择性输入走单独分支 |
| P1 | NOT IN / NOT EXISTS + NULL |
三值逻辑导致不走 anti join | 加 IS NOT NULL，或改用 NOT EXISTS |
| P2 | 无分页 | 慢在传输和渲染 | 11g 用 ROWNUM 嵌套，12c+ 用 OFFSET/FETCH |
| P2 | 老式 (+) 外连接 |
限制多、可读性差 | 改 ANSI JOIN |
完整清单、各条的执行计划特征与验证方法见 references/tuning-checklist.md。
资源索引
scripts/
01_环境探查.ps1 # 第0步：Oracle 客户端 / PL/SQL Developer / tnsnames 探查 + 拉起工具
02_采集_执行计划.sql # 版本、优化器参数、真实执行计划、按关键字搜慢 SQL
03_关键采集_函数与索引.sql # 函数源码、现有索引、表行数、索引使用率、统计信息收集
04_改写模板_保守版.sql # CTE 化、重复扫描合并、空条件短路、函数推迟方案、11g 分页
references/
tuning-checklist.md # 完整根因清单 + 执行计划特征 + 验证方法
plsqldev-workflow.md # PL/SQL Developer 手动模式细节、剪贴板取数、常见报错
assets/
report_template.html # 可编辑 HTML 报告模板（在线改字 / 保存 / 导出）
交付约定
- 结论先行，不铺垫。先给 3 条致命伤，再给依据。
- 问题清单用表格，四列：等级 / 问题 / 为什么不慢才怪 / 怎么改。
- 每条改写标注语义差异，不让用户踩隐坑。
- 最后必带「下一步待回传清单」，写成表格：要什么 / 怎么拿 / 为什么非拿不可。
- 输出目录默认
<工作区>\sql-optimizer\，文件按01_ 02_ 03_编号，便于迭代。
