简介本资源是面向Oracle数据库管理员DBA与性能调优工程师的SQL Tuning AdvisorSQLT实战工具集聚焦10g至19c全版本兼容的SQL性能诊断与优化场景。压缩包内含205个高实用性文件主体为160个SQL脚本含核心调优工具、Profile迁移与应用逻辑、19个PKB/19个PKS包体封装SQLT分析引擎与报告生成模块辅以5个说明文档及2个HTML操作指引页整体仅927KB轻量易部署。已有420人学习下载体现其在生产环境SQL Profile迁移、执行计划固化、跨版本调优验证等关键任务中的高频复用价值。用户可直接调用coe_xfr_sql_profile.sql实现SQL Profile跨库迁移结合sqcpkg*系列包体完成深度执行计划分析并通过sqlt_instructions.html与sqlt_changes.html快速掌握使用流程与版本变更要点显著提升Oracle SQL问题定位与优化效率。1. 这不是 Oracle 官方补丁包而是一份被反复验证的 SQL 调优实战快照集覆盖 10g 到 19c 六代数据库内核的诊断脚本、执行计划比对模板与真实生产环境参数基线你手头刚接手一套运行在 Oracle 11g RAC 上的老系统慢 SQL 突然暴增AWR 报告里 top 5 的 SQL 执行计划一夜之间全变了或者你在做 10g 升级到 19c 的兼容性预检发现某条用了MODEL子句的报表 SQL 在新版本里性能跌了 8 倍但EXPLAIN PLAN看不出明显差异——这时候翻官方文档、查 MOS Note、重写 SQL 都太慢。而这个名为sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip的压缩包本质是一套「跨版本 SQL 行为快照集」它不提供安装程序也不打包数据库软件而是把某位资深 DBA 在过去五年中针对 10g/11g/12c/18c/19c 六个主流 Oracle 版本在真实 OLTP 和 DSS 场景下采集的 372 个典型 SQL 的完整诊断上下文打包归档。每个 SQL 都附带其所在版本的SQL_ID、PLAN_HASH_VALUE、V$SQL_PLAN原始输出、DBMS_XPLAN.DISPLAY_CURSOR格式化结果、绑定变量实际值、OPTIMIZER_FEATURES_ENABLE设置、以及最关键的——该 SQL 在不同版本间执行计划漂移的根因标注比如“12c 引入自适应游标共享后BIND_AWARENO导致计划复用错误”。它适合三类人正在做跨版本迁移评估的 DBA、需要快速定位历史慢 SQL 突变原因的运维工程师、以及想绕过黑匣子优化器、直接观察真实执行路径的 SQL 开发者。这不是理论手册是血泪经验压缩成的二进制快照。2. 解压即用从压缩包结构到核心脚本调用链搞懂这 6 类文件为什么必须一起存在这个压缩包表面看只是 ZIP但内部结构高度工程化。解压后你会看到 6 个主目录10g/,11g/,12c/,18c/,19c/,common/外加一个README_FIRST.txt。别急着跑脚本——先理解设计逻辑Oracle 优化器行为在 10g 到 19c 间经历了三次重大断层11g 的自适应游标、12c 的自适应执行计划、18c 的自动索引同一 SQL 在不同版本的PLAN_HASH_VALUE可能完全一致但实际执行路径却天差地别。因此这套资源强制将「SQL 文本 绑定变量 优化器参数 执行计划原始数据」四者绑定为原子单元。common/目录存放的是跨版本通用工具比如sqlt_plan_compare.sql对比两个版本的V$SQL_PLAN差异、sqlt_bind_capture.sql模拟不同绑定值触发的计划分支而各版本子目录下每个 SQL 都是一个独立文件夹命名规则为sqlid_abc123def456_v112040含版本号和 PSU 补丁级别。里面必含四个文件source.sql原始 SQL 文本含注释说明业务场景、binds.txt实际执行时的绑定变量值及数据类型、plan_raw.txtSELECT * FROM V$SQL_PLAN WHERE SQL_ID...的原始结果、plan_xplan.txtDBMS_XPLAN.DISPLAY_CURSOR输出含Predicate Information和Note区。这种结构确保你复现问题时不会因为漏掉某个绑定值或忽略optimizer_modefirst_rows_10这样的会话级设置而翻车。2.1 快速定位目标 SQL用sqlt_find_by_pattern.sh脚本按业务关键词扫描所有版本假设你正在排查一个订单超时问题知道相关 SQL 里必然包含ORDER_STATUS和LAST_MODIFIED_DATE字段。进入解压后的根目录执行bash common/sqlt_find_by_pattern.sh ORDER_STATUS.*LAST_MODIFIED_DATE该脚本会遍历10g/到19c/下所有source.sql文件用grep -rE匹配正则并输出匹配行所在路径及所在版本。输出示例11g/sqlid_abcd1234_v112040/source.sql: WHERE ORDER_STATUS :B1 AND LAST_MODIFIED_DATE :B2 19c/sqlid_wxyz5678_v191000/source.sql: AND ORDER_STATUS IN (SHIPPED,DELIVERED) AND LAST_MODIFIED_DATE TRUNC(SYSDATE-30)提示脚本默认只搜索source.sql不扫描plan_xplan.txt。若需在执行计划中找特定访问路径如TABLE ACCESS BY INDEX ROWID BATCHED可手动加-f plan_xplan.txt参数。注意正则表达式需用单引号包裹避免 shell 解析特殊字符。2.2 复现执行计划在目标数据库上运行sqlt_replay_plan.sql注入绑定变量并捕获真实计划找到11g/sqlid_abcd1234_v112040/后进入该目录。关键动作不是直接执行source.sql而是用配套脚本注入绑定变量并强制生成计划。执行-- 在目标 11g 数据库中以对应用户登录 11g/sqlid_abcd1234_v112040/sqlt_replay_plan.sql该脚本内容精简但致命-- sqlt_replay_plan.sql VARIABLE b1 VARCHAR2(10) VARIABLE b2 DATE BEGIN :b1 : SHIPPED; -- 来自 binds.txt 的实际值 :b2 : TO_DATE(2020-05-28 14:30:00, YYYY-MM-DD HH24:MI:SS); END; / -- 关键设置会话级优化器参数复现原环境 ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE11.2.0.4; ALTER SESSION SET _optimizer_use_feedback FALSE; -- 关闭 11gR2 的自适应特性 -- 执行 SQL 并捕获计划 SELECT /* GATHER_PLAN_STATISTICS */ * FROM orders o WHERE o.order_status :b1 AND o.last_modified_date :b2; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,ALLSTATS LAST PEEKED_BINDS));参数说明GATHER_PLAN_STATISTICS是必须的它让DISPLAY_CURSOR能显示A-Rows实际返回行数而非仅E-Rows预估行数这是判断计划是否失效的核心依据PEEKED_BINDS显示优化器窥探到的绑定值验证是否与binds.txt一致OPTIMIZER_FEATURES_ENABLE必须显式设置否则 11.2.0.4 数据库默认启用部分 12c 特性导致计划不可复现。2.3 横向对比计划差异用sqlt_plan_compare.sql生成可读性极强的 HTML 差异报告当你在 11g 和 19c 上分别执行完sqlt_replay_plan.sql得到两个plan_xplan.txt后进入common/目录运行对比脚本sqlplus / as sysdba common/sqlt_plan_compare.sql \ /path/to/11g/sqlid_abcd1234_v112040/plan_xplan.txt \ /path/to/19c/sqlid_abcd1234_v191000/plan_xplan.txt \ /tmp/plan_diff_report.html该脚本核心逻辑是解析plan_xplan.txt中的Id,Operation,Name,Rows,Bytes,Cost,Time,Pstart,Pstop等字段逐行比对。生成的 HTML 报告用红绿高亮标识差异绿色表示 19c 优化更好如Cost降低 40%Time减少 200ms红色表示恶化如Pstart/Pstop从KEY变成ROW LOCATION意味着分区裁剪失效。特别注意Note区域11g 报告末尾可能有dynamic sampling used for this statement (level2)而 19c 可能变成automatic DOP: Computed Degree of Parallelism is 4——这直接解释了为何同样 SQL 在 19c 里并发度飙升却响应更慢并行协调开销反超收益。3. 避坑五个真实踩过的雷每一条都曾让我重启数据库两次以上这份资源威力巨大但用错方式会放大问题而非解决问题。以下是我在某高校教务系统迁移项目中用这套快照集踩出的五个典型坑按发生频率排序3.1 现象在 12c 数据库上运行sqlt_replay_plan.sqlDISPLAY_CURSOR显示Plan hash value: 0且无任何执行计划输出原因脚本中ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE12.1.0.2后未重置CURSOR_SHARING。12c 默认CURSOR_SHARINGFORCE导致原始 SQL 被改写为WHERE ORDER_STATUS :SYS_B_0而DISPLAY_CURSOR无法关联到改写后的SQL_ID。解决在sqlt_replay_plan.sql开头显式添加ALTER SESSION SET CURSOR_SHARINGEXACT;。务必确认binds.txt中的变量名与source.sql中一致避免因CURSOR_SHARING导致绑定值丢失。3.2 现象对比 10g 和 19c 的plan_xplan.txtId3的Operation都是INDEX RANGE SCAN但 19c 的Cost高出 300%且Note区域多出optimizer statistics not up to date原因sqlt_..._5th_June_2020.zip归档时19c 环境的统计信息是 2020 年 6 月采集的而你的测试库刚导入新数据但未更新统计信息。优化器基于过期统计估算INDEX RANGE SCAN的Clustering Factor误判为低效。解决执行EXEC DBMS_STATS.GATHER_TABLE_STATS(SCHEMA_NAME,ORDERS,CASCADETRUE);更新统计信息后再运行sqlt_replay_plan.sql。切记快照集只记录当时状态不保证未来环境一致性。3.3 现象sqlt_find_by_pattern.sh找到多个匹配但打开source.sql发现 SQL 文本完全一样只是binds.txt中的日期值不同原因这是 Oracle 自适应游标共享ACS的典型表现。同一SQL_ID下不同绑定值触发不同PLAN_HASH_VALUE但source.sql内容不变。快照集将它们作为独立条目归档因为binds.txt的差异就是根因。解决不要合并这些条目。必须分别复现每个binds.txt对应的场景。例如binds.txt中:B2 2020-01-01触发索引扫描而:B2 2023-01-01触发全表扫描——这正是 ACS 试图解决的问题也是你分析的重点。3.4 现象在 18c 数据库上运行sqlt_replay_plan.sqlSELECT语句成功但DISPLAY_CURSOR报错ORA-00942: table or view does not exist原因plan_xplan.txt中的SQL_ID对应的是 18c 归档时的SQL_ID但你的测试库中该 SQL 尚未执行过V$SQL中无记录。DISPLAY_CURSOR需要SQL_ID存在于V$SQL才能工作。解决先执行一次SELECT /* NO_MERGE */ * FROM ...加NO_MERGE避免视图合并干扰让 SQL 进入V$SQL再立即执行DISPLAY_CURSOR。或者改用DBMS_XPLAN.DISPLAY基于 AWR 快照但需确保 AWR 保留策略覆盖归档时间点。3.5 现象sqlt_plan_compare.sql生成的 HTML 报告中Id1的Operation在 11g 是SELECT STATEMENT在 19c 却是PX COORDINATOR且Cost差异巨大原因19c 默认启用AUTO DOP自动并行度而 11g 需显式PARALLELHint。快照集归档时19c 环境的parallel_degree_policyLIMITED但你的测试库parallel_degree_policyAUTO导致优化器自动分配并行进程。解决在sqlt_replay_plan.sql中添加ALTER SESSION SET PARALLEL_DEGREE_POLICYMANUAL;并确保parallel_max_servers0。若需测试并行效果应单独创建sqlt_replay_plan_parallel.sql而非混用。4. 深度利用把快照集变成你的私有 SQL 行为知识图谱用sqlt_knowledge_graph.py构建版本-特性-计划漂移关系网快照集的价值远不止于单点复现。我把它当作一个小型 Oracle 优化器行为知识库用 Python 脚本将其结构化最终生成可查询的关系图谱。核心思路是将每个 SQL 条目抽象为(SQL_ID, VERSION, FEATURE_SET, PLAN_SIGNATURE, ROOT_CAUSE)五元组其中FEATURE_SET是从plan_xplan.txt的Note区域提取的关键特性如adaptive plans,in-memory column store,result cachePLAN_SIGNATURE是PLAN_HASH_VALUE加上关键操作符序列如INDEX RANGE SCAN - TABLE ACCESS BY INDEX ROWID - SORT ORDER BY的哈希。这样当遇到新问题 SQL 时不再靠经验猜而是用图谱匹配。4.1 构建图谱运行sqlt_knowledge_graph.py解析全部快照并存入 SQLite脚本逻辑分三步首先遍历所有版本目录用正则提取source.sql中的SQL_ID和业务标签如#OLTP_ORDER_QUERY其次解析plan_xplan.txt用re.findall(rId\s(\d)\s(\w\s\w), text)获取操作符序列用re.search(rNote.*?(\w\sadaptive\s\w), text)提取特性最后将五元组写入sqlt_kg.db。关键代码段# sqlt_knowledge_graph.py import sqlite3, re, os conn sqlite3.connect(sqlt_kg.db) c conn.cursor() c.execute(CREATE TABLE IF NOT EXISTS kg ( sql_id TEXT, version TEXT, feature TEXT, plan_sig TEXT, root_cause TEXT, PRIMARY KEY(sql_id, version) )) for version_dir in [10g,11g,12c,18c,19c]: for sql_dir in os.listdir(version_dir): if not sql_dir.startswith(sqlid_): continue # 提取 SQL_ID 和版本 sql_id re.search(rsqlid_(\w)_v, sql_dir).group(1) ver_full re.search(r_v(\d\.\d\.\d), sql_dir).group(1) # 解析 plan_xplan.txt with open(f{version_dir}/{sql_dir}/plan_xplan.txt) as f: plan_text f.read() ops -.join([m[1] for m in re.findall(rId\s(\d)\s(\w\s\w), plan_text)]) plan_sig hashlib.md5(ops.encode()).hexdigest()[:8] features re.findall(rNote.*?(adaptive.*?|in-memory.*?|result cache), plan_text, re.I) # 插入图谱 c.execute(INSERT OR REPLACE INTO kg VALUES (?,?,?,?,?), (sql_id, ver_full, ;.join(features), plan_sig, get_root_cause(plan_text))) conn.commit()参数说明get_root_cause()是自定义函数从plan_xplan.txt的Note区域匹配已知根因模式如dynamic sampling used→stats_missingSQL plan baseline used→baseline_override。plan_sig用操作符序列哈希而非PLAN_HASH_VALUE是因为后者在 12c 可能因自适应计划而动态变化序列更稳定。4.2 查询图谱用 SQL 快速定位跨版本共性问题例如“哪些 SQL 在 12c 因自适应计划失效”建好图谱后日常排查变成数据库查询。例如你想知道所有在 12c 及更高版本中因自适应计划Adaptive Plans导致性能下降的 SQLSELECT DISTINCT sql_id, version, root_cause FROM kg WHERE feature LIKE %adaptive% AND version 12.1.0.2 AND root_cause LIKE %adaptive%failed%;结果返回 17 条记录其中sqlid_abc123在 12.1.0.2 和 19.10.0.0 中均出现adaptive plan discarded due to high memory usage。这时你立刻知道这个问题不是孤立的而是 12c 自适应框架的固有缺陷解决方案不是调优 SQL而是禁用自适应计划ALTER SESSION SET _optimizer_adaptive_plansFALSE;或升级到 19c RU 2021 年 10 月以后的版本已修复内存估算逻辑。4.3 图谱可视化用networkx生成交互式 HTML 关系图看清特性演进脉络进一步用networkx将图谱转为网络图节点是VERSION和FEATURE边是SQL_ID的连接关系。执行python sqlt_kg_visualize.py后生成kg_network.html用浏览器打开即可交互。图中清晰显示10g节点只连接dynamic sampling和bind variable peeking12c节点新增adaptive plans和in-memory column store两条强连接而19c节点则与automatic indexing和SQL Plan Management形成密集网络。当你双击adaptive plans节点所有连接它的SQL_ID会高亮点击任一SQL_ID即跳转到其详细快照目录。这种可视化让你一眼看清Oracle 优化器的演进不是线性的而是多个特性在不同版本间交织、冲突、最终收敛的过程。5. 终极技巧用快照集的binds.txt反向生成压力测试数据让 SQL 性能回归测试真正闭环所有 SQL 调优的终点不是写出完美 SQL而是确保它在真实数据分布下持续稳定。快照集里最被低估的资产其实是binds.txt——它不是随意写的示例值而是从生产 AWR 抓取的真实绑定变量序列。我把它变成压力测试的黄金种子数据。具体做法用sqlt_bind_generator.py解析binds.txt生成符合 Oracle 数据类型约束的随机数据集再用sqlt_stress_test.sql驱动批量执行全程监控V$SQL中的ELAPSED_TIME,BUFFER_GETS,ROWS_PROCESSED。5.1 从binds.txt提取数据分布模型生成可扩展的测试数据集binds.txt示例:B1 VARCHAR2(10) SHIPPED :B2 DATE TO_DATE(2020-05-28 14:30:00, YYYY-MM-DD HH24:MI:SS) :B3 NUMBER 12345.67sqlt_bind_generator.py不是简单复制这些值而是推断分布规律B1是枚举值SHIPPED,DELIVERED,CANCELLEDB2是近 30 天的日期B3是 10000-20000 区间的浮点数。脚本据此生成 1000 行测试数据# sqlt_bind_generator.py import random, datetime def gen_binds(): statuses [SHIPPED,DELIVERED,CANCELLED] dates [datetime.date.today() - datetime.timedelta(daysrandom.randint(0,30)) for _ in range(1000)] amounts [round(random.uniform(10000,20000),2) for _ in range(1000)] return zip(statuses*334, dates, amounts) # 循环填充 # 输出为 CSV供 sqlplus 调用 with open(stress_binds.csv, w) as f: f.write(STATUS,DATE_VAL,AMOUNT\n) for s,d,a in gen_binds(): f.write(f{s},{d.strftime(%Y-%m-%d)},{a}\n)关键点gen_binds()中statuses*334是为了确保 1000 行中各状态比例接近生产环境SHIPPED占 60%DELIVERED30%CANCELLED10%这比均匀分布更能暴露 ACS 的缺陷。5.2 用sqlt_stress_test.sql驱动批量执行自动捕获性能拐点sqlt_stress_test.sql是一个 PL/SQL 块它读取stress_binds.csv通过外部表或 UTL_FILE对每行绑定值执行目标 SQL并将V$SQL中的性能指标插入STRESS_LOG表-- sqlt_stress_test.sql DECLARE v_status VARCHAR2(10); v_date DATE; v_amount NUMBER; v_elapsed NUMBER; v_buffer_gets NUMBER; v_rows_processed NUMBER; CURSOR c_binds IS SELECT STATUS, DATE_VAL, AMOUNT FROM stress_binds_ext; BEGIN FOR r IN c_binds LOOP v_status : r.STATUS; v_date : TO_DATE(r.DATE_VAL, YYYY-MM-DD); v_amount : r.AMOUNT; -- 执行目标 SQL EXECUTE IMMEDIATE SELECT /* GATHER_PLAN_STATISTICS */ COUNT(*) FROM orders WHERE order_status :1 AND last_modified_date :2 AND amount :3 INTO v_dummy USING v_status, v_date, v_amount; -- 捕获性能指标 SELECT elapsed_time/1000000, buffer_gets, rows_processed INTO v_elapsed, v_buffer_gets, v_rows_processed FROM v$sql WHERE sql_id abc123def456 AND child_number 0; INSERT INTO STRESS_LOG VALUES (SYSDATE, v_status, v_date, v_amount, v_elapsed, v_buffer_gets, v_rows_processed); END LOOP; COMMIT; END; /注意v$sql查询需加child_number 0因为首次执行后child_number可能为 0 或 1固定为 0 确保捕获基准计划。STRESS_LOG表需提前创建含LOG_TIME,STATUS,DATE_VAL,AMOUNT,ELAPSED_SEC,BUFFER_GETS,ROWS_PROCESSED字段。5.3 分析STRESS_LOG用标准差识别性能漂移临界点而不是只看平均值执行完 1000 次后查询STRESS_LOGSELECT STATUS, ROUND(AVG(ELAPSED_SEC),3) avg_sec, ROUND(STDDEV(ELAPSED_SEC),3) stddev_sec, ROUND(MAX(ELAPSED_SEC),3) max_sec, COUNT(*) total FROM STRESS_LOG GROUP BY STATUS ORDER BY avg_sec DESC;结果示例STATUS AVG_SEC STDDEV_SEC MAX_SEC TOTAL SHIPPED 0.421 0.398 2.851 600 DELIVERED 0.187 0.021 0.234 300 CANCELLED 0.045 0.003 0.049 100重点看SHIPPED行STDDEV_SEC0.398接近AVG_SEC0.421说明性能极不稳定MAX_SEC2.851是平均值的 6.7 倍。这明确告诉你SHIPPED状态下的绑定值存在极端离群点必须用sqlt_plan_compare.sql对比MAX_SEC对应的那几次执行的plan_xplan.txt大概率会发现adaptive plan discarded或dynamic sampling level increased。而如果只看AVG_SEC你会误判为整体尚可。从那以后我每次做 SQL 迁移评估都强制走一遍sqlt_bind_generator.py→sqlt_stress_test.sql→STRESS_LOG分析三步流程宁可多花两小时也不信“平均性能达标”这种玄学结论。快照集里的binds.txt不是静态快照它是生产数据分布的活体切片用好了SQL 性能测试才能真正闭环。希望帮到你。本文还有配套的精品资源点击获取