简介面向Oracle DBA与数据库性能调优开发者的SQLT工具脚本合集覆盖10g、11g、12c、18c、19c多个版本可用于分析执行计划不佳的SQL语句、生成并迁移SQL Profile从而缩短执行时间、降低资源消耗。压缩包共205个文件以160个sql脚本为主体辅以19个pkb包体、19个pks包规范及少量txt说明与html文档整体约927KB体积轻便却结构完整便于按版本与功能模块检索调用。其中coe_xfr_sql_profile一类脚本可用于在不同环境间转移SQL Profile配合SQL Tuning Advisor的建议落地优化策略。已有422人学习下载适合需要排查SQL性能瓶颈、复用调优脚本的中高级DBA参考使用。1. 一个压缩包名暴露了 Oracle 版本迁移最脏的那段路看到sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip这个名字做过 Oracle 升级的人大概都会心一笑SQLTSQLTXPLAIN工具覆盖 10g 到 19c 五个大版本打包日期 2020 年 6 月。这不是一个普通的工具包它几乎就是一条时间线——从 10g 到 19cOracle 优化器换了三代SQL 执行计划能翻车的姿势也翻了三代。真正让人头疼的从来不是升级本身而是升级之后某条 SQL 突然变慢而你手里没有升级前的执行计划做对比。SQLT 就是干这个的把一条 SQL 的执行计划、统计信息、优化器参数、绑定变量、等待事件全部抓下来生成一份可对比的诊断报告。这篇文章讲的就是怎么用这个工具包在跨版本迁移场景里把 SQL 性能问题从玄学变成可复现的排查流程。2. SQLT 到底抓了什么先搞懂 XPLAIN 和 XTRACT 的分工很多人拿到 SQLT 压缩包解压完看到一堆脚本就懵了。其实核心入口只有两个sqltxtract.sql和sqltxplain.sql。前者针对单条 SQL 做深度诊断后者针对整个 SQL 集合做批量采集。搞清这两个脚本的定位后面的操作才不会乱。2.1 SQLT 的采集模型为什么它比 AWR 更适合单条 SQL 对比AWR 是实例级的采样粒度是快照间隔一条 SQL 在两次快照之间跑了多少次、每次的执行计划是什么AWR 只能给你一个聚合后的近似值。SQLT 不一样它是 SQL 级的精确采集你给它一个 SQL_ID 或者直接贴 SQL 文本它会把这条 SQL 在共享池里的游标信息、执行计划哈希值、优化器环境optimizer_features_enable、optimizer_mode、系统统计信息、对象统计信息、绑定变量捕获值全部落盘。这个差异在跨版本迁移里是致命的。10g 升 19c优化器从 CBO 的早期形态变成了自适应执行计划加 SQL Plan Management 的体系同一条 SQL 在两边可能生成完全不同的计划。AWR 里你只能看到“升级后这条 SQL 平均耗时从 0.5 秒变成 8 秒”但看不到“为什么”。SQLT 的报告里会明确告诉你计划哈希变了、某个索引从 INDEX RANGE SCAN 变成了 FULL TABLE SCAN、原因是统计信息在 19c 下被自动收集任务刷新了。常见做法是升级前在源库对关键 SQL 跑一次 XTRACT升级后在目标库对同样的 SQL 再跑一次两份报告做 diff。这个流程听起来简单但实际操作里有几个参数必须调对否则报告根本没法对比。2.2 安装 SQLT三个目录权限和一条必须改的配置SQLT 的安装不是sqlplus / as sysdba install.sql就完事的。它需要在目标库创建一个专门的 schema通常叫SQLTXPLAIN并且这个 schema 需要一系列系统权限。下面是我一般会走的步骤。# 1. 解压到独立目录不要放在 ORACLE_HOME 下面 unzip sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip -d /u01/tools/sqlt cd /u01/tools/sqlt # 2. 确认当前 sqlplus 版本与目标库一致 sqlplus -v # 3. 以 sysdba 连接目标库执行安装脚本 sqlplus / as sysdba sqlt/install/sqcreate.sql安装脚本会交互式问你几个问题SQLT schema 的密码、默认表空间、临时表空间。这里有个血泪经验——默认表空间不要用 SYSAUX 或者 USERS单独建一个 200MB 起步的表空间。SQLT 在采集大 SQL 的时候会把执行计划、统计信息、甚至 CBO 的 trace 全部存进自己的表里一条复杂 SQL 的 XPLAIN 报告可能产生几十MB的基表数据。用 USERS 表空间的话赶上业务库空间紧张采集到一半报 ORA-01653 是常有的事。-- 安装前先建好独立表空间避免和业务对象抢空间 CREATE TABLESPACE sqlt_data DATAFILE /u01/oradata/ORCL/sqlt_data01.dbf SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE 2G; -- 确认 SQLT schema 的默认表空间 ALTER USER sqltxplain DEFAULT TABLESPACE sqlt_data;安装完成后用sqltxplain用户登录执行sqlt/install/sqltusers.sql授权给需要跑采集的 DBA 账号。注意不要用 SYS 直接跑 XTRACTSYS 跑出来的报告里很多对象信息会因为权限过高而显示不全反而丢失了普通用户视角下的执行计划细节。用业务 schema 或者一个有SELECT ANY DICTIONARY权限的 DBA 账号跑报告才贴近真实。2.3 XTRACT 的最小命令SQL_ID、连接串和输出目录假设升级前在 10g 源库上你要采集一条问题 SQL 的诊断信息。最简命令是这样的# 在源库执行采集 SQL_ID 为 8k3m2n1p0q 的 SQL sqlplus sqltxplain/password//source_host:1521/ORCL sqlt/run/sqltxtract.sql \ 8k3m2n1p0q \ TAGpre_upgrade \ METHODXTRACT参数说明第一个位置参数是 SQL_ID必须是在v$sql里能查到的。TAG是给这次采集打的标签会体现在输出文件名里跨版本对比时靠它区分。METHODXTRACT表示只做 SQL 级采集不生成完整的 XPLAIN 报告XPLAIN 会额外采集大量对象统计信息耗时更长适合深度分析单条 SQL 时用。输出文件默认落在 SQLT schema 的安装目录下文件名类似sqlt_s12345_8k3m2n1p0q_pre_upgrade.html。这个 HTML 报告是自包含的直接浏览器打开就能看不需要额外依赖。提示如果 SQL 在共享池里已经被 aged outsqltxtract.sql会报错找不到 SQL_ID。这时候要么从 AWR 历史里拿 SQL_ID 配合sqltxecute.sql重放要么直接用sqltxplain.sql贴 SQL 文本重新解析。前者更准后者更快。2.4 跨版本对比把两份 HTML 报告变成可 diff 的结构SQLT 生成的 HTML 报告人眼看没问题但要做版本对比靠肉眼翻两个几十页的 HTML 是不现实的。我的做法是在采集时加FORMATJSON参数12c 以上版本支持把关键信息导出成结构化数据然后用脚本做字段级 diff。# 12c 及以上版本输出 JSON 格式便于程序化对比 sqlplus sqltxplain/password//target_host:1521/ORCL sqlt/run/sqltxtract.sql \ 8k3m2n1p0q \ TAGpost_upgrade \ METHODXTRACT \ FORMATJSONimport json def load_plan(path): with open(path) as f: data json.load(f) # 提取执行计划的核心字段操作类型、对象名、成本、基数 plan [] for step in data.get(execution_plan, []): plan.append({ operation: step.get(operation), object: step.get(object_name), cost: step.get(cost), cardinality: step.get(cardinality), }) return plan pre load_plan(pre_upgrade.json) post load_plan(post_upgrade.json) # 逐行对比标出操作类型或对象发生变化的步骤 for i, (a, b) in enumerate(zip(pre, post)): if a[operation] ! b[operation] or a[object] ! b[object]: print(fStep {i}: {a[operation]}({a[object]}) - {b[operation]}({b[object]}))这段脚本的逻辑很直白把两份 JSON 里的执行计划按步骤序号对齐逐行比较操作类型和访问对象。一旦发现某一步从INDEX RANGE SCAN变成了TABLE ACCESS FULL或者访问的对象从索引变成了表就打印出来。实际使用中计划步骤数可能不一致比如 19c 下多了自适应计划的分支这时候zip会截断需要改成按计划哈希值分组后再对比。参数上cost和cardinality的差异超过 30% 就值得人工介入看低于这个阈值通常是统计信息正常波动。3. 五个版本之间的坑10g 到 19c 的优化器行为差异SQLT 工具本身是跨版本兼容的但你在 10g 上采集的报告拿到 19c 上对比时很多字段的含义已经变了。不理解这些差异报告看得越多越糊涂。3.1 10g 到 11g绑定变量窥探从“偶尔”变成“默认”10g 时代绑定变量窥探bind peeking只在硬解析的第一次执行时发生之后游标复用就不再看了。11g 引入了自适应游标共享ACS同一个 SQL 在不同绑定变量值下可能生成多个子游标每个子游标有自己的执行计划。这意味着你在 10g 上采集到的是“一个 SQL 一个计划”到 11g 上可能变成“一个 SQL 五个计划”。SQLT 报告里体现这个差异的地方是v$sql的CHILD_NUMBER字段。10g 的报告里通常只有 child 011g 以上可能出现 child 0 到 child 4。对比的时候不能只比 child 0要把所有子游标的计划都拉出来看。我一般会在采集命令里加CHILDALL参数强制采集所有子游标。# 采集所有子游标的执行计划避免漏掉 ACS 产生的分支计划 sqlplus sqltxplain/password//source_host:1521/ORCL sqlt/run/sqltxtract.sql \ 8k3m2n1p0q \ TAGpre_upgrade_all_children \ METHODXTRACT \ CHILDALL3.2 12c 的自适应执行计划报告里多出来的那几行是什么12c 开始优化器可以在运行时决定是否把某个子计划切换成自适应形式。SQLT 报告里会多出ADAPTIVE标记的行以及STATISTICS COLLECTOR这样的步骤。很多人在对比 11g 和 12c 的报告时看到计划步骤数对不上就慌了其实大部分是自适应计划的分支结构导致的。处理办法是在 12c 以上版本采集时加ADAPTIVEOFF参数强制关闭自适应特性再采一次得到一份“退化到 11g 行为”的计划。两份计划一对比就能区分哪些差异是版本特性引入的哪些是真正的性能退化。-- 在会话级别关闭自适应执行计划用于生成对比基线 ALTER SESSION SET _optimizer_adaptive_plans FALSE; ALTER SESSION SET _optimizer_adaptive_statistics FALSE;注意这两个下划线参数是隐藏参数只在诊断会话里临时用不要写进生产环境的初始化参数文件。用完记得ALTER SESSION恢复或者直接断开重连。3.3 18c 和 19cSQL Plan Management 的基线会不会被 SQLT 干扰18c 之后 SPMSQL Plan Management成为默认开启的特性一条 SQL 如果被捕获为基线优化器会强制使用基线里的计划。SQLT 在采集时会查询DBA_SQL_PLAN_BASELINES如果发现当前 SQL 有基线报告里会标注SQL_PLAN_BASELINE字段。这里有个容易翻车的地方在目标库跑 XTRACT 时如果 SPM 已经自动捕获了升级后的新计划作为基线你采集到的就是“被基线锁定后的计划”而不是优化器真正想选的计划。对比的时候会误以为计划没变实际上是被 SPM 兜住了。排查方法是先查基线状态-- 查看目标 SQL 是否被 SPM 基线锁定 SELECT sql_handle, plan_name, enabled, accepted, fixed FROM dba_sql_plan_baselines WHERE sql_text LIKE %你的SQL片段%;如果fixedYES说明计划被强制锁定这时候要么临时ALTER SESSION SET _use_sql_plan_baselineFALSE再采一次要么在报告里明确标注“基线已启用”避免误判。3.4 统计信息收集策略的版本差异为什么同一张表两边行数差一倍10g 的统计信息收集依赖GATHER_STATS_JOB默认只在维护窗口跑而且对分区表的增量统计支持很差。12c 之后引入了增量统计incremental statistics分区表的全局统计信息可以靠分区级的 synopsis 快速合并。19c 更是默认开启了实时统计信息收集real-time statistics在 DML 过程中就会更新统计信息。这个差异导致同一个表在 10g 和 19c 上的NUM_ROWS、BLOCKS、AVG_ROW_LEN可能差很多。SQLT 报告里会列出这些统计信息对比时如果发现基数估算差异巨大先别怀疑优化器去查两边的统计信息收集历史-- 查看表的统计信息最后收集时间和收集方式 SELECT table_name, last_analyzed, num_rows, blocks, stale_stats, stattype_locked FROM dba_tab_statistics WHERE table_name YOUR_TABLE;如果 19c 那边last_analyzed是几小时前而 10g 那边是上周那计划差异很可能只是统计信息新鲜度不同造成的不是版本行为差异。这种情况下在 10g 上手动收集一次统计信息再采一次 XTRACT对比才有意义。4. 避坑SQLT 采集过程中最容易翻车的五个地方4.1 现象采集脚本跑了一半报 ORA-04031共享池不足原因XTRACT 在解析 SQL 时会创建大量的临时游标和 PL/SQL 对象如果共享池碎片化严重或者shared_pool_size偏小跑到一半就报共享池不足。10g 和 11g 上尤其常见因为这两个版本的共享池管理不如 12c 之后的自动内存管理灵活。解决采集前先确认共享池剩余空间SELECT * FROM v$sgastat WHERE poolshared pool AND namefree memory;如果剩余低于 200MB先ALTER SYSTEM FLUSH SHARED_POOL低峰期操作或者临时调大shared_pool_size。更稳妥的做法是在采集会话里设置_cursor_sharing_exact减少游标复用带来的干扰。4.2 现象报告里执行计划是空的只有 SQL 文本原因SQL 在共享池里已经被 aged outv$sql_plan里查不到对应的计划行。SQLT 会退而求其次只输出 SQL 文本和统计信息计划部分为空。这种情况在业务高峰期采集时特别容易遇到因为共享池淘汰速度快。解决改用sqltxplain.sql贴 SQL 文本重新解析或者从 AWR 的dba_hist_sql_plan里导出历史计划。如果 SQL 有绑定变量还需要配合dba_hist_sqlstat里的peeked_binds信息还原当时的绑定值否则重新解析出来的计划可能和出问题时的不一样。4.3 现象跨版本对比时两边的执行计划步骤完全对不上原因除了前面说的自适应计划和 ACS 子游标还有一个常见原因是两边的optimizer_features_enable不一致。10g 源库可能是10.2.0.519c 目标库默认是19.1.0优化器特性集不同计划结构自然不同。解决在目标库采集时临时把optimizer_features_enable设成和源库一致采一份“兼容模式”的计划作为基线再采一份默认模式的计划作为实际计划。两份一对比就能看出哪些差异是版本特性导致的哪些是真正的退化。-- 采集兼容模式基线计划 ALTER SESSION SET optimizer_features_enable 10.2.0.5; -- 执行 XTRACT 采集 -- 采集完成后恢复 ALTER SESSION SET optimizer_features_enable 19.1.0;4.4 现象SQLT 安装时报 ORA-00942表或视图不存在原因安装脚本里引用了某些 12c 以上才有的数据字典视图比如DBA_SQL_PLAN_BASELINES在 10g 上执行时这些视图不存在脚本直接报错中断。SQLT 的安装脚本虽然做了版本判断但判断逻辑依赖v$version的字符串匹配某些打了补丁的 10g 版本字符串格式不标准判断会失效。解决在 10g 上安装时手动注释掉安装脚本里引用 12c 视图的段落或者用sqcreate.sql的MINIMAL模式安装跳过可选组件。安装完成后用sqlt/install/sqltusers.sql补授权即可。4.5 现象报告 HTML 打开后中文乱码原因SQLT 生成的 HTML 默认字符集是AL32UTF8如果数据库的NLS_CHARACTERSET是ZHS16GBK报告里的中文对象名或 SQL 注释会乱码。解决采集时加CHARSETZHS16GBK参数或者在浏览器里手动切换编码。更彻底的办法是在 SQLT schema 的会话级别设置NLS_LANG让生成的报告统一用数据库字符集。5. 把 SQLT 嵌进迁移流程一个可复用的采集清单和验证脚本前面讲的都是单点操作真正让 SQLT 发挥价值的是把它变成迁移流程里的固定环节。我的习惯是在迁移前一周、迁移后当天、迁移后一周各跑一次批量采集形成三个时间点的计划快照。5.1 批量采集从 AWR 里捞出 Top SQL 再逐个 XTRACT手工一条条采集不现实我一般用下面这个脚本从 AWR 里捞出过去七天消耗 CPU 最多的 50 条 SQL生成采集命令列表。-- 从 AWR 历史里提取 Top 50 SQL_ID按 CPU 时间排序 SELECT * FROM ( SELECT sql_id, SUM(executions_delta) AS execs, SUM(cpu_time_delta)/1000000 AS cpu_sec, SUM(elapsed_time_delta)/1000000 AS ela_sec FROM dba_hist_sqlstat WHERE snap_id BETWEEN begin_snap AND end_snap AND executions_delta 0 GROUP BY sql_id ORDER BY cpu_sec DESC ) WHERE ROWNUM 50;拿到 SQL_ID 列表后用 shell 循环调用sqltxtract.sql#!/bin/bash # 批量采集脚本输入文件每行一个 SQL_ID while read sql_id; do echo Collecting ${sql_id}... sqlplus -s sqltxplain/password//host:1521/ORCL EOF sqlt/run/sqltxtract.sql ${sql_id} TAGbatch_pre METHODXTRACT CHILDALL EXIT EOF done sql_id_list.txt这个脚本的关键在于-s静默模式和 heredoc 的配合避免 sqlplus 的交互提示卡住循环。TAG参数统一用batch_pre方便后续按标签批量检索输出文件。实际跑的时候建议加个sleep 2避免连续采集把目标库的 CPU 打满。5.2 验证脚本用计划哈希和成本偏差做自动告警采集完三批报告后用下面这段 Python 做自动比对输出偏差超过阈值的 SQL 清单。import json, glob def extract_plan_hash(path): with open(path) as f: data json.load(f) return data.get(plan_hash_value), data.get(cost) pre_files glob.glob(output/*batch_pre*.json) post_files glob.glob(output/*batch_post*.json) # 按 SQL_ID 建立索引 pre_map {f.split(_)[2]: f for f in pre_files} post_map {f.split(_)[2]: f for f in post_files} for sql_id in pre_map: if sql_id not in post_map: print(f{sql_id}: 升级后未采集到需人工确认) continue pre_hash, pre_cost extract_plan_hash(pre_map[sql_id]) post_hash, post_cost extract_plan_hash(post_map[sql_id]) if pre_hash ! post_hash: delta (post_cost - pre_cost) / pre_cost * 100 if pre_cost else 0 print(f{sql_id}: 计划哈希变化 {pre_hash}-{post_hash}, 成本偏差 {delta:.1f}%)这段脚本的逻辑是按 SQL_ID 对齐升级前后的报告比较计划哈希值。哈希变了说明执行路径变了再算成本偏差百分比。偏差超过 50% 的标红人工看低于 20% 的基本可以忽略。参数上plan_hash_value是 Oracle 执行计划的唯一标识同一个计划在不同版本下哈希值可能不同因为内部算法有调整所以哈希变化只能作为线索最终还是要看具体的计划步骤差异。5.3 一个我踩过的坑别在迁移窗口当天跑全量采集最后一次让我长记性的教训是迁移窗口当天业务刚切过去我急着跑全量 XTRACT 验证结果 50 条 SQL 的采集把目标库的 CPU 打到了 95%业务监控直接告警。后来改成迁移后第二天低峰期跑并且把批量采集的并发控制在 2 个会话以内再没出过问题。SQLT 的采集本身不重但CHILDALL加上大 SQL 的统计信息查询累积起来对共享池和 CPU 的消耗不容小觑。现在我习惯在采集脚本里加一行renice或者用nice -n 19降低优先级让采集进程在系统层面就低人一等。希望这个流程能帮你在下一次跨版本迁移里少花点时间在“为什么这条 SQL 变慢了”的玄学排查上。本文还有配套的精品资源点击获取
简介面向Oracle DBA与数据库性能调优开发者的SQLT工具脚本合集覆盖10g、11g、12c、18c、19c多个版本可用于分析执行计划不佳的SQL语句、生成并迁移SQL Profile从而缩短执行时间、降低资源消耗。压缩包共205个文件以160个sql脚本为主体辅以19个pkb包体、19个pks包规范及少量txt说明与html文档整体约927KB体积轻便却结构完整便于按版本与功能模块检索调用。其中coe_xfr_sql_profile一类脚本可用于在不同环境间转移SQL Profile配合SQL Tuning Advisor的建议落地优化策略。已有422人学习下载适合需要排查SQL性能瓶颈、复用调优脚本的中高级DBA参考使用。1. 一个压缩包名暴露了 Oracle 版本迁移最脏的那段路看到sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip这个名字做过 Oracle 升级的人大概都会心一笑SQLTSQLTXPLAIN工具覆盖 10g 到 19c 五个大版本打包日期 2020 年 6 月。这不是一个普通的工具包它几乎就是一条时间线——从 10g 到 19cOracle 优化器换了三代SQL 执行计划能翻车的姿势也翻了三代。真正让人头疼的从来不是升级本身而是升级之后某条 SQL 突然变慢而你手里没有升级前的执行计划做对比。SQLT 就是干这个的把一条 SQL 的执行计划、统计信息、优化器参数、绑定变量、等待事件全部抓下来生成一份可对比的诊断报告。这篇文章讲的就是怎么用这个工具包在跨版本迁移场景里把 SQL 性能问题从玄学变成可复现的排查流程。2. SQLT 到底抓了什么先搞懂 XPLAIN 和 XTRACT 的分工很多人拿到 SQLT 压缩包解压完看到一堆脚本就懵了。其实核心入口只有两个sqltxtract.sql和sqltxplain.sql。前者针对单条 SQL 做深度诊断后者针对整个 SQL 集合做批量采集。搞清这两个脚本的定位后面的操作才不会乱。2.1 SQLT 的采集模型为什么它比 AWR 更适合单条 SQL 对比AWR 是实例级的采样粒度是快照间隔一条 SQL 在两次快照之间跑了多少次、每次的执行计划是什么AWR 只能给你一个聚合后的近似值。SQLT 不一样它是 SQL 级的精确采集你给它一个 SQL_ID 或者直接贴 SQL 文本它会把这条 SQL 在共享池里的游标信息、执行计划哈希值、优化器环境optimizer_features_enable、optimizer_mode、系统统计信息、对象统计信息、绑定变量捕获值全部落盘。这个差异在跨版本迁移里是致命的。10g 升 19c优化器从 CBO 的早期形态变成了自适应执行计划加 SQL Plan Management 的体系同一条 SQL 在两边可能生成完全不同的计划。AWR 里你只能看到“升级后这条 SQL 平均耗时从 0.5 秒变成 8 秒”但看不到“为什么”。SQLT 的报告里会明确告诉你计划哈希变了、某个索引从 INDEX RANGE SCAN 变成了 FULL TABLE SCAN、原因是统计信息在 19c 下被自动收集任务刷新了。常见做法是升级前在源库对关键 SQL 跑一次 XTRACT升级后在目标库对同样的 SQL 再跑一次两份报告做 diff。这个流程听起来简单但实际操作里有几个参数必须调对否则报告根本没法对比。2.2 安装 SQLT三个目录权限和一条必须改的配置SQLT 的安装不是sqlplus / as sysdba install.sql就完事的。它需要在目标库创建一个专门的 schema通常叫SQLTXPLAIN并且这个 schema 需要一系列系统权限。下面是我一般会走的步骤。# 1. 解压到独立目录不要放在 ORACLE_HOME 下面 unzip sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip -d /u01/tools/sqlt cd /u01/tools/sqlt # 2. 确认当前 sqlplus 版本与目标库一致 sqlplus -v # 3. 以 sysdba 连接目标库执行安装脚本 sqlplus / as sysdba sqlt/install/sqcreate.sql安装脚本会交互式问你几个问题SQLT schema 的密码、默认表空间、临时表空间。这里有个血泪经验——默认表空间不要用 SYSAUX 或者 USERS单独建一个 200MB 起步的表空间。SQLT 在采集大 SQL 的时候会把执行计划、统计信息、甚至 CBO 的 trace 全部存进自己的表里一条复杂 SQL 的 XPLAIN 报告可能产生几十MB的基表数据。用 USERS 表空间的话赶上业务库空间紧张采集到一半报 ORA-01653 是常有的事。-- 安装前先建好独立表空间避免和业务对象抢空间 CREATE TABLESPACE sqlt_data DATAFILE /u01/oradata/ORCL/sqlt_data01.dbf SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE 2G; -- 确认 SQLT schema 的默认表空间 ALTER USER sqltxplain DEFAULT TABLESPACE sqlt_data;安装完成后用sqltxplain用户登录执行sqlt/install/sqltusers.sql授权给需要跑采集的 DBA 账号。注意不要用 SYS 直接跑 XTRACTSYS 跑出来的报告里很多对象信息会因为权限过高而显示不全反而丢失了普通用户视角下的执行计划细节。用业务 schema 或者一个有SELECT ANY DICTIONARY权限的 DBA 账号跑报告才贴近真实。2.3 XTRACT 的最小命令SQL_ID、连接串和输出目录假设升级前在 10g 源库上你要采集一条问题 SQL 的诊断信息。最简命令是这样的# 在源库执行采集 SQL_ID 为 8k3m2n1p0q 的 SQL sqlplus sqltxplain/password//source_host:1521/ORCL sqlt/run/sqltxtract.sql \ 8k3m2n1p0q \ TAGpre_upgrade \ METHODXTRACT参数说明第一个位置参数是 SQL_ID必须是在v$sql里能查到的。TAG是给这次采集打的标签会体现在输出文件名里跨版本对比时靠它区分。METHODXTRACT表示只做 SQL 级采集不生成完整的 XPLAIN 报告XPLAIN 会额外采集大量对象统计信息耗时更长适合深度分析单条 SQL 时用。输出文件默认落在 SQLT schema 的安装目录下文件名类似sqlt_s12345_8k3m2n1p0q_pre_upgrade.html。这个 HTML 报告是自包含的直接浏览器打开就能看不需要额外依赖。提示如果 SQL 在共享池里已经被 aged outsqltxtract.sql会报错找不到 SQL_ID。这时候要么从 AWR 历史里拿 SQL_ID 配合sqltxecute.sql重放要么直接用sqltxplain.sql贴 SQL 文本重新解析。前者更准后者更快。2.4 跨版本对比把两份 HTML 报告变成可 diff 的结构SQLT 生成的 HTML 报告人眼看没问题但要做版本对比靠肉眼翻两个几十页的 HTML 是不现实的。我的做法是在采集时加FORMATJSON参数12c 以上版本支持把关键信息导出成结构化数据然后用脚本做字段级 diff。# 12c 及以上版本输出 JSON 格式便于程序化对比 sqlplus sqltxplain/password//target_host:1521/ORCL sqlt/run/sqltxtract.sql \ 8k3m2n1p0q \ TAGpost_upgrade \ METHODXTRACT \ FORMATJSONimport json def load_plan(path): with open(path) as f: data json.load(f) # 提取执行计划的核心字段操作类型、对象名、成本、基数 plan [] for step in data.get(execution_plan, []): plan.append({ operation: step.get(operation), object: step.get(object_name), cost: step.get(cost), cardinality: step.get(cardinality), }) return plan pre load_plan(pre_upgrade.json) post load_plan(post_upgrade.json) # 逐行对比标出操作类型或对象发生变化的步骤 for i, (a, b) in enumerate(zip(pre, post)): if a[operation] ! b[operation] or a[object] ! b[object]: print(fStep {i}: {a[operation]}({a[object]}) - {b[operation]}({b[object]}))这段脚本的逻辑很直白把两份 JSON 里的执行计划按步骤序号对齐逐行比较操作类型和访问对象。一旦发现某一步从INDEX RANGE SCAN变成了TABLE ACCESS FULL或者访问的对象从索引变成了表就打印出来。实际使用中计划步骤数可能不一致比如 19c 下多了自适应计划的分支这时候zip会截断需要改成按计划哈希值分组后再对比。参数上cost和cardinality的差异超过 30% 就值得人工介入看低于这个阈值通常是统计信息正常波动。3. 五个版本之间的坑10g 到 19c 的优化器行为差异SQLT 工具本身是跨版本兼容的但你在 10g 上采集的报告拿到 19c 上对比时很多字段的含义已经变了。不理解这些差异报告看得越多越糊涂。3.1 10g 到 11g绑定变量窥探从“偶尔”变成“默认”10g 时代绑定变量窥探bind peeking只在硬解析的第一次执行时发生之后游标复用就不再看了。11g 引入了自适应游标共享ACS同一个 SQL 在不同绑定变量值下可能生成多个子游标每个子游标有自己的执行计划。这意味着你在 10g 上采集到的是“一个 SQL 一个计划”到 11g 上可能变成“一个 SQL 五个计划”。SQLT 报告里体现这个差异的地方是v$sql的CHILD_NUMBER字段。10g 的报告里通常只有 child 011g 以上可能出现 child 0 到 child 4。对比的时候不能只比 child 0要把所有子游标的计划都拉出来看。我一般会在采集命令里加CHILDALL参数强制采集所有子游标。# 采集所有子游标的执行计划避免漏掉 ACS 产生的分支计划 sqlplus sqltxplain/password//source_host:1521/ORCL sqlt/run/sqltxtract.sql \ 8k3m2n1p0q \ TAGpre_upgrade_all_children \ METHODXTRACT \ CHILDALL3.2 12c 的自适应执行计划报告里多出来的那几行是什么12c 开始优化器可以在运行时决定是否把某个子计划切换成自适应形式。SQLT 报告里会多出ADAPTIVE标记的行以及STATISTICS COLLECTOR这样的步骤。很多人在对比 11g 和 12c 的报告时看到计划步骤数对不上就慌了其实大部分是自适应计划的分支结构导致的。处理办法是在 12c 以上版本采集时加ADAPTIVEOFF参数强制关闭自适应特性再采一次得到一份“退化到 11g 行为”的计划。两份计划一对比就能区分哪些差异是版本特性引入的哪些是真正的性能退化。-- 在会话级别关闭自适应执行计划用于生成对比基线 ALTER SESSION SET _optimizer_adaptive_plans FALSE; ALTER SESSION SET _optimizer_adaptive_statistics FALSE;注意这两个下划线参数是隐藏参数只在诊断会话里临时用不要写进生产环境的初始化参数文件。用完记得ALTER SESSION恢复或者直接断开重连。3.3 18c 和 19cSQL Plan Management 的基线会不会被 SQLT 干扰18c 之后 SPMSQL Plan Management成为默认开启的特性一条 SQL 如果被捕获为基线优化器会强制使用基线里的计划。SQLT 在采集时会查询DBA_SQL_PLAN_BASELINES如果发现当前 SQL 有基线报告里会标注SQL_PLAN_BASELINE字段。这里有个容易翻车的地方在目标库跑 XTRACT 时如果 SPM 已经自动捕获了升级后的新计划作为基线你采集到的就是“被基线锁定后的计划”而不是优化器真正想选的计划。对比的时候会误以为计划没变实际上是被 SPM 兜住了。排查方法是先查基线状态-- 查看目标 SQL 是否被 SPM 基线锁定 SELECT sql_handle, plan_name, enabled, accepted, fixed FROM dba_sql_plan_baselines WHERE sql_text LIKE %你的SQL片段%;如果fixedYES说明计划被强制锁定这时候要么临时ALTER SESSION SET _use_sql_plan_baselineFALSE再采一次要么在报告里明确标注“基线已启用”避免误判。3.4 统计信息收集策略的版本差异为什么同一张表两边行数差一倍10g 的统计信息收集依赖GATHER_STATS_JOB默认只在维护窗口跑而且对分区表的增量统计支持很差。12c 之后引入了增量统计incremental statistics分区表的全局统计信息可以靠分区级的 synopsis 快速合并。19c 更是默认开启了实时统计信息收集real-time statistics在 DML 过程中就会更新统计信息。这个差异导致同一个表在 10g 和 19c 上的NUM_ROWS、BLOCKS、AVG_ROW_LEN可能差很多。SQLT 报告里会列出这些统计信息对比时如果发现基数估算差异巨大先别怀疑优化器去查两边的统计信息收集历史-- 查看表的统计信息最后收集时间和收集方式 SELECT table_name, last_analyzed, num_rows, blocks, stale_stats, stattype_locked FROM dba_tab_statistics WHERE table_name YOUR_TABLE;如果 19c 那边last_analyzed是几小时前而 10g 那边是上周那计划差异很可能只是统计信息新鲜度不同造成的不是版本行为差异。这种情况下在 10g 上手动收集一次统计信息再采一次 XTRACT对比才有意义。4. 避坑SQLT 采集过程中最容易翻车的五个地方4.1 现象采集脚本跑了一半报 ORA-04031共享池不足原因XTRACT 在解析 SQL 时会创建大量的临时游标和 PL/SQL 对象如果共享池碎片化严重或者shared_pool_size偏小跑到一半就报共享池不足。10g 和 11g 上尤其常见因为这两个版本的共享池管理不如 12c 之后的自动内存管理灵活。解决采集前先确认共享池剩余空间SELECT * FROM v$sgastat WHERE poolshared pool AND namefree memory;如果剩余低于 200MB先ALTER SYSTEM FLUSH SHARED_POOL低峰期操作或者临时调大shared_pool_size。更稳妥的做法是在采集会话里设置_cursor_sharing_exact减少游标复用带来的干扰。4.2 现象报告里执行计划是空的只有 SQL 文本原因SQL 在共享池里已经被 aged outv$sql_plan里查不到对应的计划行。SQLT 会退而求其次只输出 SQL 文本和统计信息计划部分为空。这种情况在业务高峰期采集时特别容易遇到因为共享池淘汰速度快。解决改用sqltxplain.sql贴 SQL 文本重新解析或者从 AWR 的dba_hist_sql_plan里导出历史计划。如果 SQL 有绑定变量还需要配合dba_hist_sqlstat里的peeked_binds信息还原当时的绑定值否则重新解析出来的计划可能和出问题时的不一样。4.3 现象跨版本对比时两边的执行计划步骤完全对不上原因除了前面说的自适应计划和 ACS 子游标还有一个常见原因是两边的optimizer_features_enable不一致。10g 源库可能是10.2.0.519c 目标库默认是19.1.0优化器特性集不同计划结构自然不同。解决在目标库采集时临时把optimizer_features_enable设成和源库一致采一份“兼容模式”的计划作为基线再采一份默认模式的计划作为实际计划。两份一对比就能看出哪些差异是版本特性导致的哪些是真正的退化。-- 采集兼容模式基线计划 ALTER SESSION SET optimizer_features_enable 10.2.0.5; -- 执行 XTRACT 采集 -- 采集完成后恢复 ALTER SESSION SET optimizer_features_enable 19.1.0;4.4 现象SQLT 安装时报 ORA-00942表或视图不存在原因安装脚本里引用了某些 12c 以上才有的数据字典视图比如DBA_SQL_PLAN_BASELINES在 10g 上执行时这些视图不存在脚本直接报错中断。SQLT 的安装脚本虽然做了版本判断但判断逻辑依赖v$version的字符串匹配某些打了补丁的 10g 版本字符串格式不标准判断会失效。解决在 10g 上安装时手动注释掉安装脚本里引用 12c 视图的段落或者用sqcreate.sql的MINIMAL模式安装跳过可选组件。安装完成后用sqlt/install/sqltusers.sql补授权即可。4.5 现象报告 HTML 打开后中文乱码原因SQLT 生成的 HTML 默认字符集是AL32UTF8如果数据库的NLS_CHARACTERSET是ZHS16GBK报告里的中文对象名或 SQL 注释会乱码。解决采集时加CHARSETZHS16GBK参数或者在浏览器里手动切换编码。更彻底的办法是在 SQLT schema 的会话级别设置NLS_LANG让生成的报告统一用数据库字符集。5. 把 SQLT 嵌进迁移流程一个可复用的采集清单和验证脚本前面讲的都是单点操作真正让 SQLT 发挥价值的是把它变成迁移流程里的固定环节。我的习惯是在迁移前一周、迁移后当天、迁移后一周各跑一次批量采集形成三个时间点的计划快照。5.1 批量采集从 AWR 里捞出 Top SQL 再逐个 XTRACT手工一条条采集不现实我一般用下面这个脚本从 AWR 里捞出过去七天消耗 CPU 最多的 50 条 SQL生成采集命令列表。-- 从 AWR 历史里提取 Top 50 SQL_ID按 CPU 时间排序 SELECT * FROM ( SELECT sql_id, SUM(executions_delta) AS execs, SUM(cpu_time_delta)/1000000 AS cpu_sec, SUM(elapsed_time_delta)/1000000 AS ela_sec FROM dba_hist_sqlstat WHERE snap_id BETWEEN begin_snap AND end_snap AND executions_delta 0 GROUP BY sql_id ORDER BY cpu_sec DESC ) WHERE ROWNUM 50;拿到 SQL_ID 列表后用 shell 循环调用sqltxtract.sql#!/bin/bash # 批量采集脚本输入文件每行一个 SQL_ID while read sql_id; do echo Collecting ${sql_id}... sqlplus -s sqltxplain/password//host:1521/ORCL EOF sqlt/run/sqltxtract.sql ${sql_id} TAGbatch_pre METHODXTRACT CHILDALL EXIT EOF done sql_id_list.txt这个脚本的关键在于-s静默模式和 heredoc 的配合避免 sqlplus 的交互提示卡住循环。TAG参数统一用batch_pre方便后续按标签批量检索输出文件。实际跑的时候建议加个sleep 2避免连续采集把目标库的 CPU 打满。5.2 验证脚本用计划哈希和成本偏差做自动告警采集完三批报告后用下面这段 Python 做自动比对输出偏差超过阈值的 SQL 清单。import json, glob def extract_plan_hash(path): with open(path) as f: data json.load(f) return data.get(plan_hash_value), data.get(cost) pre_files glob.glob(output/*batch_pre*.json) post_files glob.glob(output/*batch_post*.json) # 按 SQL_ID 建立索引 pre_map {f.split(_)[2]: f for f in pre_files} post_map {f.split(_)[2]: f for f in post_files} for sql_id in pre_map: if sql_id not in post_map: print(f{sql_id}: 升级后未采集到需人工确认) continue pre_hash, pre_cost extract_plan_hash(pre_map[sql_id]) post_hash, post_cost extract_plan_hash(post_map[sql_id]) if pre_hash ! post_hash: delta (post_cost - pre_cost) / pre_cost * 100 if pre_cost else 0 print(f{sql_id}: 计划哈希变化 {pre_hash}-{post_hash}, 成本偏差 {delta:.1f}%)这段脚本的逻辑是按 SQL_ID 对齐升级前后的报告比较计划哈希值。哈希变了说明执行路径变了再算成本偏差百分比。偏差超过 50% 的标红人工看低于 20% 的基本可以忽略。参数上plan_hash_value是 Oracle 执行计划的唯一标识同一个计划在不同版本下哈希值可能不同因为内部算法有调整所以哈希变化只能作为线索最终还是要看具体的计划步骤差异。5.3 一个我踩过的坑别在迁移窗口当天跑全量采集最后一次让我长记性的教训是迁移窗口当天业务刚切过去我急着跑全量 XTRACT 验证结果 50 条 SQL 的采集把目标库的 CPU 打到了 95%业务监控直接告警。后来改成迁移后第二天低峰期跑并且把批量采集的并发控制在 2 个会话以内再没出过问题。SQLT 的采集本身不重但CHILDALL加上大 SQL 的统计信息查询累积起来对共享池和 CPU 的消耗不容小觑。现在我习惯在采集脚本里加一行renice或者用nice -n 19降低优先级让采集进程在系统层面就低人一等。希望这个流程能帮你在下一次跨版本迁移里少花点时间在“为什么这条 SQL 变慢了”的玄学排查上。本文还有配套的精品资源点击获取
