Oracle SQLT整包拆解：10g-19c执行计划漂移诊断与采集避坑

简介SQL执行计划是优化器基于统计信息、绑定变量和优化器参数为SQL选择的访问路径。当统计信息失真、参数变化或容器/版本差异出现时同一SQL可能发生执行计划漂移表现为索引失效、耗时骤增。SQLTSQLTXPLAIN是一组运行在Oracle库内的sqlplus脚本可把SQL文本、执行计划、CBO参数、对象统计、10053追踪及AWR/ASH片段打包成诊断快照为慢SQL定位和厂商支持提供统一材料。生产库排查通常优先XPLAIN只读采集历史信息可走XTRACTXECUTE需谨慎PDB/RAC场景还要确认容器与实例。围绕10g到19c整包安装建用户、采集方法取舍、表空间与授权边界、报告对比验证构成一条可落地排障路径。1. 一条 SQL 从 0.2 秒掉到 18 秒先别猜跑一遍 sqlt一条跑了三年的报表 SQL周一早上突然从 0.2 秒掉到 18 秒执行计划里那个唯一索引说没就没。这种时候老 DBA 一般不会先猜而是让你去跑一遍 sqlt。这次拆的 sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip就是 SQLTSQLTXPLAIN在 2020 年 6 月打的一份整包快照覆盖 10g、11g、12c、18c、19c 五个版本线。它不是监控平台也不是调优神器本质是一套跑在库里的 sqlplus 脚本集合把 SQL 文本、执行计划、CBO 参数、对象统计、10053 追踪、AWR/ASH 片段一次性抓下来压成一个 zip。适合两类人被 Oracle 执行计划漂移折腾过的 DBA以及要给厂商或支持团队交材料的开发。2. 拆包先看骨架install/run/utl 三个目录和 10g 到 19c 的入口分支2.1 解压后先认目录别急着 sqlplus压缩包解开后是一个 sqlt 目录里面没有二进制、没有安装程序全是 sql 和 shell 包装脚本。我一般先看一眼顶层结构再动手因为后面所有操作都围绕这几个目录走路径搞错了脚本会到处找不到公共文件。# 解压到独立工具目录不要放在 ORACLE_HOME 下面 unzip sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip -d /u01/tools cd /u01/tools/sqlt ls -1-d指定解压目标目录保持解压后的顶层目录名是 sqlt不要改成别的名字run 目录里的脚本会用相对路径去调 utl。用普通 oracle 用户解压即可不需要 root也不建议放在$ORACLE_HOME里升级或打补丁时容易被覆盖。目录职责可以按下面这张表理解目录典型文件作用installsqcreate.sql、sqdrop.sql创建和删除 SQLT 用户初始化内部表runsqltxplain.sql、sqltxtract.sql、sqltxecute.sql面向用户的采集入口按版本和场景选一个utlsqltcommon.sql 等公共脚本被 run 目录调用负责具体取数和拼报告不要手改docreadme、changelog记录版本变更、已知问题和参数说明最关键的是 run 目录。同一个包里放了多个入口不是让你随便挑而是对应不同的数据库版本和采集方式。10g 时代留下的习惯是跑 sqltxecute.sql11g 之后 sqltxplain.sql 成了主入口sqltxtract.sql 则偏向从 AWR 里提取历史信息。入口选错轻则报告缺段重则脚本在编译阶段就报错。2.2 版本分支10g 走 sqltxecute11g 以后走 sqltxplainSQLT 内部靠dbms_db_version和v$database判断当前库的版本、是否 CDB、是否 RAC然后决定走哪条取数路径。这个判断是脚本自己做的但入口脚本要人选。我的经验是10.2.0.5 这类老库优先 sqltxecute.sql11.2、12.1、12.2 用 sqltxplain.sql18c、19c 必须用 sqltxplain.sql并且用户要建在 PDB 里。12c 之后多租户带来的坑比版本本身还多。SQLT 用户建在 CDB$ROOT采集时连的也是根容器那么业务 SQL 的游标、AWR 里的 sql_id 很可能不在当前容器可见范围内报告里就会出现大量 N/A。正确做法是alter session set container业务PDB之后再建用户或者直接用 PDB 的服务名连进去建。RAC 环境还要注意 connect_identifier 的写法单实例填1/这种实例序号多实例要确认 SQL 真正跑在哪个实例上否则共享池里找不到对应游标。2.3 落库前的五项环境核对动手建用户之前我习惯先跑几条查询把底摸清。这几条不花时间但能省掉后面一半的返工。-- 1. 版本与补丁 select banner_full from v$version; -- 2. 是否 CDB、当前容器 select name, cdb, open_mode from v$database; show con_name; -- 3. 各表空间余量重点看 SYSAUX 和计划给 SQLT 用的表空间 select tablespace_name, round(sum(bytes)/1024/1024/1024, 2) as total_gb from dba_data_files group by tablespace_name order by 2 desc; -- 4. 临时表空间 select tablespace_name, round(sum(bytes)/1024/1024/1024, 2) as total_gb from dba_temp_files group by tablespace_name;第一条确认版本决定用哪个入口脚本第二条确认容器决定用户建在哪第三、四条看空间SQLT 生成 10053 追踪和中间结果时对表空间、临时空间都有消耗。还有一项不在 SQL 里确认这套库有没有买诊断包授权。SQLT 报告里的 AWR、ASH 片段依赖 AWR 仓库商业环境里这部分要按授权来拿不准就在测试库跑别在生产库硬采。3. 建 SQLT 用户sqcreate.sql 的交互项、表空间和权限边界3.1 表空间留多少按 SQL 复杂度估不按库大小估SQLT 用户自己会建一批表存中间结果报告本身是文件但采集过程会在库里落数据。表空间给多大不看整库几个 T看你要采的 SQL 有多复杂。普通 OLTP 单条 SQL1GB 到 2GB 足够如果 SQL 里有大量 UNION ALL、子查询展开、绑定变量多或者要跑 XECUTE 模式我会直接给 5GB 并开 autoextend省得跑到一半报 ORA-01652 再回来扩。-- 用独立表空间不要蹭 SYSTEM 或业务表空间 create tablespace SQLT_DATA datafile /u01/oradata/orcl/sqlt_data01.dbf size 2G autoextend on next 512M maxsize 10G extent management local autoallocate segment space management auto; -- 临时表空间确认至少有 1GB 空闲 select tablespace_name, bytes_used, bytes_free from v$temp_space_header;建表空间这条语句里autoextend on next 512M maxsize 10G是给自己留余地采集大 SQL 时中间表增长很快extent management local autoallocate让区管理交给数据库不用手工算。临时空间那条是确认不是必须新建但如果bytes_free只剩几十 MB先扩临时表空间再跑采集。权限方面SQLT 用户需要能读数据字典、能建表建过程、能读 AWR 相关视图。sqcreate.sql 会按脚本内置的清单授权常见做法是额外给SELECT ANY DICTIONARY和SELECT_CATALOG_ROLE但不要图省事直接给 DBA。给 DBA 能让脚本一次跑通可一旦这个用户密码泄露等于把整库交出去不划算。3.2 交互式安装sqcreate.sql 一路填什么安装用 sysdba 连进去执行 install 目录下的 sqcreate.sql。不同小版本的提示语会有细微差异但顺序基本固定用户名、密码、默认表空间、临时表空间、是否启用 AWR 相关采集。export ORACLE_SIDorcl export ORACLE_HOME/u01/app/oracle/product/19.0.0/dbhome_1 export PATH$ORACLE_HOME/bin:$PATH sqlplus / as sysdba这三行是标准环境准备ORACLE_SID换成目标库ORACLE_HOME换成实际安装路径。环境不对时 sqlplus 会连到别的实例建出来的用户也就不在你要诊断的库里。SQL /u01/tools/sqlt/install/sqcreate.sql执行后按提示逐项填写关键几项如下提示项建议值说明SQLT user nameSQLTXPLAIN默认名改也可以后面脚本要对应Password单独设一个不要复用业务账号密码Default tablespaceSQLT_DATA第 3.1 节建的那个Temporary tablespaceTEMP按库实际情况填AWR 相关采集有授权选 Y没授权选 N避免报告缺段还以为是脚本问题填完之后脚本会建用户、建表、编译包最后打印一串授权和初始化结果。看到SQLT installation completed之类的收尾字样才算成功。中途如果有 ORA- 报错先别重跑把屏幕输出存下来按报错行号去 utl 目录找对应脚本通常是权限或表空间问题。3.3 卸载与重装sqdrop.sql 和 here-doc 半自动装坏了或者要换表空间用 sqdrop.sql 把用户删掉重来比手工 drop user 干净因为它会连内部对象一起清。SQL /u01/tools/sqlt/install/sqdrop.sql这条会提示确认用户名输入 SQLTXPLAIN 后执行删除。注意它会删用户及下属对象确认这个用户只归 SQLT 用别把业务 schema 误填进去。如果要在多套库上批量装可以把回答按顺序喂给 sqlplus做成半自动。密码不要明文写在脚本里我一般用read -s读进变量再拼接跑完即弃。#!/bin/bash # 半自动安装示例密码从终端读不落盘 read -s -p SQLT password: SQLT_PWD echo sqlplus -s / as sysdba EOF /u01/tools/sqlt/install/sqcreate.sql SQLTXPLAIN $SQLT_PWD SQLT_DATA TEMP Y EOF unset SQLT_PWDhere-doc 里的顺序必须和交互提示一致多一个空行都可能让脚本把空值当成回答。-s是静默模式去掉 sqlplus 的横幅方便把输出重定向到日志。跑完unset清掉变量避免留在当前 shell 环境里。4. 采一份报告XPLAIN/XTRACT/XECUTE 三种方法的取舍与参数4.1 三种采集方法的差别run 目录下的入口最终都会让你选采集方法。名字看起来接近行为差别很大选错在生产库上是要出事的。方法是否真正执行 SQL主要数据来源适用场景风险XPLAIN否共享池游标、数据字典、AWR生产库只读排查低XTRACT否AWR 历史快照SQL 已不在共享池但 AWR 里还有中依赖 AWR 保留期XECUTE是实时执行、运行时统计测试库复现绑定变量问题高会在库里真跑XPLAIN 是默认选项绝大多数生产问题用它就够它把游标缓存里的执行计划、绑定变量、优化器环境、对象统计抓出来不碰业务数据。XTRACT 适合那种 SQL 已经执行完、共享池被刷掉、但 AWR 里还留着快照的情况采集的是历史信息。XECUTE 会带绑定变量真执行一遍能拿到运行时实际行数、物理读代价是可能把一条慢 SQL 再跑一次生产库上我基本不选。4.2 交互执行 sqltxplain.sql 的完整过程用 sysdba 连进去执行 run 目录下的入口然后按提示填 SQL_ID、连接标识、方法和是否包含 AWR。sqlplus / as sysdbaSQL /u01/tools/sqlt/run/sqltxplain.sql一个典型会话如下提示语以实际包为准Enter SQL_ID: 8k9x2m3n4p5q6 Enter Connect Identifier: 1/ Enter Method (XPLAIN/XTRACT/XECUTE) [XPLAIN]: XPLAIN Include AWR/ASH sections (Y/N) [Y]: YSQL_ID从v$sql、AWR 报告或应用日志里拿长度不对脚本会直接拒绝。Connect Identifier单实例填1/表示实例 1如果是服务名方式可以填//host:1521/pdb1这种形式PDB 环境必须指向业务 PDB 的服务名。方法保持 XPLAINAWR 段按授权选 Y 或 N。执行过程中屏幕会滚动打印正在采集的模块名最后输出报告文件名和存放路径。整个过程几分钟到十几分钟不等取决于 SQL 复杂度和 AWR 段多少。中途不要 CtrlC中断会留下半截中间表下次跑之前最好用 sqdrop 重装一次。4.3 输出目录、zip 命名和报告骨架报告默认落在执行 sqlplus 时的当前目录也有打包版本会在 sqlt 下建 out 目录以屏幕最后打印的 Report name 为准。典型的产物是下面几类文件内容用途sqlt_sqlid_*.html主报告入口浏览器打开按章节看sqlt_sqlid_*.zip整包给厂商或支持团队别只发 html10053_*.trcCBO 追踪看优化器为什么选错计划*.log执行日志采集中途报错时定位主报告一般按 SQL 文本、执行计划、优化器参数、对象统计、索引、AWR、ASH、10053 分段。先看执行计划段里的 Plan Hash Value 和 Cost再对照绑定变量段最后才翻 10053。顺序反了容易被几百行追踪带偏先建立“计划长什么样”的印象再去解释为什么。5. 避坑与排查五条现场记录每条都对应一个报错5.1 现象脚本一跑就报 ORA-00904 或 ORA-06550现象很直接sqcreate.sql 执行到一半屏幕刷出ORA-00904: invalid identifier或ORA-06550: line N, column M包编译不过用户建了一半。原因通常是包和库版本对不上。19c 库里跑了一份很老的 SQLTutl 里引用了新版本视图才有的列或者反过来10g 库用了新包脚本里调用的dbms_*过程在老库上不存在。这个包虽然标称覆盖 10g 到 19c但入口脚本要人选选错入口等于让老脚本去适配新字典。解决分三步先看 doc 目录里的 changelog确认这份包对目标版本的支持情况10g 换 sqltxecute.sql11g 以上换 sqltxplain.sql如果还报错按报错行号去 utl 下找对应文件看它引用的视图在当前库是否存在。实在对不上换一份与库版本更接近的 SQLT 包别硬改脚本改完的报告缺段更麻烦。5.2 现象报告里 AWR 段全是 N/A报告能生成HTML 也能打开但 AWR、ASH 章节全是 N/A执行计划段只有当前游标没有历史计划对比。第一个原因是授权。AWR 仓库属于诊断包范畴商业库没有对应授权时脚本取不到数据只能留空。第二个原因是 AWR 快照被刷掉了默认保留期通常几天到一周一条月初的 SQL 到月中再查快照早没了。第三个原因是 sql_id 根本没进过 AWR比如只跑过一次的临时查询。先确认授权边界没有授权就别在生产库反复重跑换测试库复现。有授权的情况查dba_hist_snapshot看目标时间段有没有快照没有就只能靠 XPLAIN 的当前游标信息。想以后能查把 AWR 保留期调长或者出问题时第一时间采集别等业务催了两天再动手。5.3 现象ORA-01652 临时段或表空间撑爆采集跑到一半报ORA-01652: unable to extend temp segment或者 SQLT_DATA 表空间告警报告只生成了一半。原因是 SQL 太复杂10053 追踪加中间结果表把空间吃满。带大量子查询、UNION ALL、绑定变量多的 SQL追踪文件几百 MB 很常见临时段排序也跟着涨。默认给 1GB 的表空间在这种场景下不够用。解决是扩容加清理把 SQLT_DATA 的 autoextend 打开next 调到 512M临时表空间至少留 1GB 空闲跑之前清掉上一次的中间表用 sqdrop 重装最干净。还有一个办法是减少采集范围AWR 段选 N或者只采执行计划不采 10053先拿到核心信息再说。5.4 现象生产库上 XECUTE 把业务 SQL 跑成锁等待采集期间业务反馈同一条 SQL 变慢甚至出现 TM、TX 锁等待监控上看就是采集会话和业务会话撞上了。原因是选了 XECUTE。这个模式会带着绑定变量真正执行 SQL如果绑定变量选得不好执行计划可能变成全表扫读的块数比业务还多如果 SQL 本身有 DML 或者调用了有副作用的函数影响更大。生产库上这不是诊断是压测。处理原则很简单生产只用 XPLAIN需要看运行时行数就去测试库用 XECUTE 复现复现时把绑定变量固定成生产实际值。采集前跟业务确认时间窗别在高峰期跑。已经撞上了就 kill 掉采集会话检查有没有残留锁再决定要不要重采。5.5 现象PDB 里建完用户采集时提示找不到 SQL_ID用户建好了脚本也能跑但报告里提示 SQL_ID 在共享池里找不到执行计划段是空的。原因多半是容器不对。SQLT 用户建在 CDB$ROOTsqlplus 连的也是根容器业务 SQL 的游标在 PDB 里根容器看不见。另一种情况是Connect Identifier填了 CDB 的服务名脚本连进根容器去查 PDB 的 sql_id自然查不到。解决是在业务 PDB 内建 SQLT 用户或者alter session set container业务PDB之后再执行安装和采集Connect Identifier填 PDB 的服务名不要填 CDB 的。RAC 环境再确认实例号SQL 跑在实例 2 上连接标识却写1/同样找不到游标。6. 进阶验证把两份 SQLT 报告和 10053 放到一条时间线上单份报告只能告诉你“现在是什么样”要回答“为什么变慢”得把改参数前、改参数后两份报告摆在一起看。我一般固定三个锚点执行计划的 Plan Hash Value、OPTIMIZER_FEATURES_ENABLE的取值、相关表最近一次统计信息收集时间。这三个锚点任何一个变了执行计划漂移就有了可解释的入口。比如 Plan Hash 变了但统计信息时间没变优先怀疑绑定变量窥探或参数变化统计信息时间变了先看新统计是否失真再谈计划。历史计划用 dbms_xplan 从 AWR 里直接取和 SQLT 报告里的执行计划段交叉验证-- 从 AWR 取指定 sql_id 的历史执行计划 select * from table( dbms_xplan.display_awr(sql_id, null, null, ALL) ); -- 对照相关表的统计信息时间与采样比例 select table_name, last_analyzed, num_rows, sample_size from dba_tables where table_name table_name;第一条的第二个参数是 plan_hash_value留空表示取该 sql_id 的所有历史计划第三个参数是 child number留空取全部第四个ALL让输出包含 outline 和优化器信息。第二条重点看last_analyzed和sample_size采样比例过低时统计信息失真CBO 选错索引就有了理由。验证顺序我习惯倒着来先看执行计划有没有变再看统计信息是否过期或采样不足然后看绑定变量是否触发不同的 child cursor最后才翻 10053 里的成本计算和隐含参数。10053 文件很长直接搜Best so far和Cost:比从头读快得多。改完参数不要只看一次执行计划跑一遍业务 SQL 拿实际逻辑读对比报告里的估算行数和实际行数差一个数量级说明统计信息还得再收。从那以后我每次上生产前都强制在测试库先跑一遍 XPLAIN 和 XECUTE 的对比把两份报告的 Plan Hash 和 10053 里的成本行并排放着看确认没有计划突变了才放行。这套动作多花二十分钟比事后翻车再补报告省事得多。希望帮到你。本文还有配套的精品资源点击获取