簡介面向Oracle DBA與數(shù)據(jù)庫性能調(diào)優(yōu)開發(fā)者的SQLT工具腳本合集覆蓋10g、11g、12c、18c、19c多個版本可用于分析執(zhí)行計劃不佳的SQL語句、生成并遷移SQL Profile從而縮短執(zhí)行時間、降低資源消耗。壓縮包共205個文件以160個sql腳本為主體輔以19個pkb包體、19個pks包規(guī)范及少量txt說明與html文檔整體約927KB體積輕便卻結(jié)構(gòu)完整便于按版本與功能模塊檢索調(diào)用。其中coe_xfr_sql_profile一類腳本可用于在不同環(huán)境間轉(zhuǎn)移SQL Profile配合SQL Tuning Advisor的建議落地優(yōu)化策略。已有422人學(xué)習(xí)下載適合需要排查SQL性能瓶頸、復(fù)用調(diào)優(yōu)腳本的中高級DBA參考使用。1. 一個壓縮包名暴露了 Oracle 版本遷移最臟的那段路看到sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip這個名字做過 Oracle 升級的人大概都會心一笑SQLTSQLTXPLAIN工具覆蓋 10g 到 19c 五個大版本打包日期 2020 年 6 月。這不是一個普通的工具包它幾乎就是一條時間線——從 10g 到 19cOracle 優(yōu)化器換了三代SQL 執(zhí)行計劃能翻車的姿勢也翻了三代。真正讓人頭疼的從來不是升級本身而是升級之后某條 SQL 突然變慢而你手里沒有升級前的執(zhí)行計劃做對比。SQLT 就是干這個的把一條 SQL 的執(zhí)行計劃、統(tǒng)計信息、優(yōu)化器參數(shù)、綁定變量、等待事件全部抓下來生成一份可對比的診斷報告。這篇文章講的就是怎么用這個工具包在跨版本遷移場景里把 SQL 性能問題從玄學(xué)變成可復(fù)現(xiàn)的排查流程。2. SQLT 到底抓了什么先搞懂 XPLAIN 和 XTRACT 的分工很多人拿到 SQLT 壓縮包解壓完看到一堆腳本就懵了。其實核心入口只有兩個sqltxtract.sql和sqltxplain.sql。前者針對單條 SQL 做深度診斷后者針對整個 SQL 集合做批量采集。搞清這兩個腳本的定位后面的操作才不會亂。2.1 SQLT 的采集模型為什么它比 AWR 更適合單條 SQL 對比AWR 是實例級的采樣粒度是快照間隔一條 SQL 在兩次快照之間跑了多少次、每次的執(zhí)行計劃是什么AWR 只能給你一個聚合后的近似值。SQLT 不一樣它是 SQL 級的精確采集你給它一個 SQL_ID 或者直接貼 SQL 文本它會把這條 SQL 在共享池里的游標信息、執(zhí)行計劃哈希值、優(yōu)化器環(huán)境optimizer_features_enable、optimizer_mode、系統(tǒng)統(tǒng)計信息、對象統(tǒng)計信息、綁定變量捕獲值全部落盤。這個差異在跨版本遷移里是致命的。10g 升 19c優(yōu)化器從 CBO 的早期形態(tài)變成了自適應(yīng)執(zhí)行計劃加 SQL Plan Management 的體系同一條 SQL 在兩邊可能生成完全不同的計劃。AWR 里你只能看到“升級后這條 SQL 平均耗時從 0.5 秒變成 8 秒”但看不到“為什么”。SQLT 的報告里會明確告訴你計劃哈希變了、某個索引從 INDEX RANGE SCAN 變成了 FULL TABLE SCAN、原因是統(tǒng)計信息在 19c 下被自動收集任務(wù)刷新了。常見做法是升級前在源庫對關(guān)鍵 SQL 跑一次 XTRACT升級后在目標庫對同樣的 SQL 再跑一次兩份報告做 diff。這個流程聽起來簡單但實際操作里有幾個參數(shù)必須調(diào)對否則報告根本沒法對比。2.2 安裝 SQLT三個目錄權(quán)限和一條必須改的配置SQLT 的安裝不是sqlplus / as sysdba install.sql就完事的。它需要在目標庫創(chuàng)建一個專門的 schema通常叫SQLTXPLAIN并且這個 schema 需要一系列系統(tǒng)權(quán)限。下面是我一般會走的步驟。# 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 連接目標庫執(zhí)行安裝腳本 sqlplus / as sysdba sqlt/install/sqcreate.sql安裝腳本會交互式問你幾個問題SQLT schema 的密碼、默認表空間、臨時表空間。這里有個血淚經(jīng)驗——默認表空間不要用 SYSAUX 或者 USERS單獨建一個 200MB 起步的表空間。SQLT 在采集大 SQL 的時候會把執(zhí)行計劃、統(tǒng)計信息、甚至 CBO 的 trace 全部存進自己的表里一條復(fù)雜 SQL 的 XPLAIN 報告可能產(chǎn)生幾十MB的基表數(shù)據(jù)。用 USERS 表空間的話趕上業(yè)務(wù)庫空間緊張采集到一半報 ORA-01653 是常有的事。-- 安裝前先建好獨立表空間避免和業(yè)務(wù)對象搶空間 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用戶登錄執(zhí)行sqlt/install/sqltusers.sql授權(quán)給需要跑采集的 DBA 賬號。注意不要用 SYS 直接跑 XTRACTSYS 跑出來的報告里很多對象信息會因為權(quán)限過高而顯示不全反而丟失了普通用戶視角下的執(zhí)行計劃細節(jié)。用業(yè)務(wù) schema 或者一個有SELECT ANY DICTIONARY權(quán)限的 DBA 賬號跑報告才貼近真實。2.3 XTRACT 的最小命令SQL_ID、連接串和輸出目錄假設(shè)升級前在 10g 源庫上你要采集一條問題 SQL 的診斷信息。最簡命令是這樣的# 在源庫執(zhí)行采集 SQL_ID 為 8k3m2n1p0q 的 SQL sqlplus sqltxplain/password//source_host:1521/ORCL sqlt/run/sqltxtract.sql \ 8k3m2n1p0q \ TAGpre_upgrade \ METHODXTRACT參數(shù)說明第一個位置參數(shù)是 SQL_ID必須是在v$sql里能查到的。TAG是給這次采集打的標簽會體現(xiàn)在輸出文件名里跨版本對比時靠它區(qū)分。METHODXTRACT表示只做 SQL 級采集不生成完整的 XPLAIN 報告XPLAIN 會額外采集大量對象統(tǒng)計信息耗時更長適合深度分析單條 SQL 時用。輸出文件默認落在 SQLT schema 的安裝目錄下文件名類似sqlt_s12345_8k3m2n1p0q_pre_upgrade.html。這個 HTML 報告是自包含的直接瀏覽器打開就能看不需要額外依賴。提示如果 SQL 在共享池里已經(jīng)被 aged outsqltxtract.sql會報錯找不到 SQL_ID。這時候要么從 AWR 歷史里拿 SQL_ID 配合sqltxecute.sql重放要么直接用sqltxplain.sql貼 SQL 文本重新解析。前者更準后者更快。2.4 跨版本對比把兩份 HTML 報告變成可 diff 的結(jié)構(gòu)SQLT 生成的 HTML 報告人眼看沒問題但要做版本對比靠肉眼翻兩個幾十頁的 HTML 是不現(xiàn)實的。我的做法是在采集時加FORMATJSON參數(shù)12c 以上版本支持把關(guān)鍵信息導(dǎo)出成結(jié)構(gòu)化數(shù)據(jù)然后用腳本做字段級 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) # 提取執(zhí)行計劃的核心字段操作類型、對象名、成本、基數(shù) 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) # 逐行對比標出操作類型或?qū)ο蟀l(fā)生變化的步驟 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 里的執(zhí)行計劃按步驟序號對齊逐行比較操作類型和訪問對象。一旦發(fā)現(xiàn)某一步從INDEX RANGE SCAN變成了TABLE ACCESS FULL或者訪問的對象從索引變成了表就打印出來。實際使用中計劃步驟數(shù)可能不一致比如 19c 下多了自適應(yīng)計劃的分支這時候zip會截斷需要改成按計劃哈希值分組后再對比。參數(shù)上cost和cardinality的差異超過 30% 就值得人工介入看低于這個閾值通常是統(tǒng)計信息正常波動。3. 五個版本之間的坑10g 到 19c 的優(yōu)化器行為差異SQLT 工具本身是跨版本兼容的但你在 10g 上采集的報告拿到 19c 上對比時很多字段的含義已經(jīng)變了。不理解這些差異報告看得越多越糊涂。3.1 10g 到 11g綁定變量窺探從“偶爾”變成“默認”10g 時代綁定變量窺探bind peeking只在硬解析的第一次執(zhí)行時發(fā)生之后游標復(fù)用就不再看了。11g 引入了自適應(yīng)游標共享ACS同一個 SQL 在不同綁定變量值下可能生成多個子游標每個子游標有自己的執(zhí)行計劃。這意味著你在 10g 上采集到的是“一個 SQL 一個計劃”到 11g 上可能變成“一個 SQL 五個計劃”。SQLT 報告里體現(xiàn)這個差異的地方是v$sql的CHILD_NUMBER字段。10g 的報告里通常只有 child 011g 以上可能出現(xiàn) child 0 到 child 4。對比的時候不能只比 child 0要把所有子游標的計劃都拉出來看。我一般會在采集命令里加CHILDALL參數(shù)強制采集所有子游標。# 采集所有子游標的執(zhí)行計劃避免漏掉 ACS 產(chǎn)生的分支計劃 sqlplus sqltxplain/password//source_host:1521/ORCL sqlt/run/sqltxtract.sql \ 8k3m2n1p0q \ TAGpre_upgrade_all_children \ METHODXTRACT \ CHILDALL3.2 12c 的自適應(yīng)執(zhí)行計劃報告里多出來的那幾行是什么12c 開始優(yōu)化器可以在運行時決定是否把某個子計劃切換成自適應(yīng)形式。SQLT 報告里會多出ADAPTIVE標記的行以及STATISTICS COLLECTOR這樣的步驟。很多人在對比 11g 和 12c 的報告時看到計劃步驟數(shù)對不上就慌了其實大部分是自適應(yīng)計劃的分支結(jié)構(gòu)導(dǎo)致的。處理辦法是在 12c 以上版本采集時加ADAPTIVEOFF參數(shù)強制關(guān)閉自適應(yīng)特性再采一次得到一份“退化到 11g 行為”的計劃。兩份計劃一對比就能區(qū)分哪些差異是版本特性引入的哪些是真正的性能退化。-- 在會話級別關(guān)閉自適應(yīng)執(zhí)行計劃用于生成對比基線 ALTER SESSION SET _optimizer_adaptive_plans FALSE; ALTER SESSION SET _optimizer_adaptive_statistics FALSE;注意這兩個下劃線參數(shù)是隱藏參數(shù)只在診斷會話里臨時用不要寫進生產(chǎn)環(huán)境的初始化參數(shù)文件。用完記得ALTER SESSION恢復(fù)或者直接斷開重連。3.3 18c 和 19cSQL Plan Management 的基線會不會被 SQLT 干擾18c 之后 SPMSQL Plan Management成為默認開啟的特性一條 SQL 如果被捕獲為基線優(yōu)化器會強制使用基線里的計劃。SQLT 在采集時會查詢DBA_SQL_PLAN_BASELINES如果發(fā)現(xiàn)當前 SQL 有基線報告里會標注SQL_PLAN_BASELINE字段。這里有個容易翻車的地方在目標庫跑 XTRACT 時如果 SPM 已經(jīng)自動捕獲了升級后的新計劃作為基線你采集到的就是“被基線鎖定后的計劃”而不是優(yōu)化器真正想選的計劃。對比的時候會誤以為計劃沒變實際上是被 SPM 兜住了。排查方法是先查基線狀態(tài)-- 查看目標 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 統(tǒng)計信息收集策略的版本差異為什么同一張表兩邊行數(shù)差一倍10g 的統(tǒng)計信息收集依賴GATHER_STATS_JOB默認只在維護窗口跑而且對分區(qū)表的增量統(tǒng)計支持很差。12c 之后引入了增量統(tǒng)計incremental statistics分區(qū)表的全局統(tǒng)計信息可以靠分區(qū)級的 synopsis 快速合并。19c 更是默認開啟了實時統(tǒng)計信息收集real-time statistics在 DML 過程中就會更新統(tǒng)計信息。這個差異導(dǎo)致同一個表在 10g 和 19c 上的NUM_ROWS、BLOCKS、AVG_ROW_LEN可能差很多。SQLT 報告里會列出這些統(tǒng)計信息對比時如果發(fā)現(xiàn)基數(shù)估算差異巨大先別懷疑優(yōu)化器去查兩邊的統(tǒng)計信息收集歷史-- 查看表的統(tǒng)計信息最后收集時間和收集方式 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 那邊是上周那計劃差異很可能只是統(tǒng)計信息新鮮度不同造成的不是版本行為差異。這種情況下在 10g 上手動收集一次統(tǒng)計信息再采一次 XTRACT對比才有意義。4. 避坑SQLT 采集過程中最容易翻車的五個地方4.1 現(xiàn)象采集腳本跑了一半報 ORA-04031共享池不足原因XTRACT 在解析 SQL 時會創(chuàng)建大量的臨時游標和 PL/SQL 對象如果共享池碎片化嚴重或者shared_pool_size偏小跑到一半就報共享池不足。10g 和 11g 上尤其常見因為這兩個版本的共享池管理不如 12c 之后的自動內(nèi)存管理靈活。解決采集前先確認共享池剩余空間SELECT * FROM v$sgastat WHERE poolshared pool AND namefree memory;如果剩余低于 200MB先ALTER SYSTEM FLUSH SHARED_POOL低峰期操作或者臨時調(diào)大shared_pool_size。更穩(wěn)妥的做法是在采集會話里設(shè)置_cursor_sharing_exact減少游標復(fù)用帶來的干擾。4.2 現(xiàn)象報告里執(zhí)行計劃是空的只有 SQL 文本原因SQL 在共享池里已經(jīng)被 aged outv$sql_plan里查不到對應(yīng)的計劃行。SQLT 會退而求其次只輸出 SQL 文本和統(tǒng)計信息計劃部分為空。這種情況在業(yè)務(wù)高峰期采集時特別容易遇到因為共享池淘汰速度快。解決改用sqltxplain.sql貼 SQL 文本重新解析或者從 AWR 的dba_hist_sql_plan里導(dǎo)出歷史計劃。如果 SQL 有綁定變量還需要配合dba_hist_sqlstat里的peeked_binds信息還原當時的綁定值否則重新解析出來的計劃可能和出問題時的不一樣。4.3 現(xiàn)象跨版本對比時兩邊的執(zhí)行計劃步驟完全對不上原因除了前面說的自適應(yīng)計劃和 ACS 子游標還有一個常見原因是兩邊的optimizer_features_enable不一致。10g 源庫可能是10.2.0.519c 目標庫默認是19.1.0優(yōu)化器特性集不同計劃結(jié)構(gòu)自然不同。解決在目標庫采集時臨時把optimizer_features_enable設(shè)成和源庫一致采一份“兼容模式”的計劃作為基線再采一份默認模式的計劃作為實際計劃。兩份一對比就能看出哪些差異是版本特性導(dǎo)致的哪些是真正的退化。-- 采集兼容模式基線計劃 ALTER SESSION SET optimizer_features_enable 10.2.0.5; -- 執(zhí)行 XTRACT 采集 -- 采集完成后恢復(fù) ALTER SESSION SET optimizer_features_enable 19.1.0;4.4 現(xiàn)象SQLT 安裝時報 ORA-00942表或視圖不存在原因安裝腳本里引用了某些 12c 以上才有的數(shù)據(jù)字典視圖比如DBA_SQL_PLAN_BASELINES在 10g 上執(zhí)行時這些視圖不存在腳本直接報錯中斷。SQLT 的安裝腳本雖然做了版本判斷但判斷邏輯依賴v$version的字符串匹配某些打了補丁的 10g 版本字符串格式不標準判斷會失效。解決在 10g 上安裝時手動注釋掉安裝腳本里引用 12c 視圖的段落或者用sqcreate.sql的MINIMAL模式安裝跳過可選組件。安裝完成后用sqlt/install/sqltusers.sql補授權(quán)即可。4.5 現(xiàn)象報告 HTML 打開后中文亂碼原因SQLT 生成的 HTML 默認字符集是AL32UTF8如果數(shù)據(jù)庫的NLS_CHARACTERSET是ZHS16GBK報告里的中文對象名或 SQL 注釋會亂碼。解決采集時加CHARSETZHS16GBK參數(shù)或者在瀏覽器里手動切換編碼。更徹底的辦法是在 SQLT schema 的會話級別設(shè)置NLS_LANG讓生成的報告統(tǒng)一用數(shù)據(jù)庫字符集。5. 把 SQLT 嵌進遷移流程一個可復(fù)用的采集清單和驗證腳本前面講的都是單點操作真正讓 SQLT 發(fā)揮價值的是把它變成遷移流程里的固定環(huán)節(jié)。我的習(xí)慣是在遷移前一周、遷移后當天、遷移后一周各跑一次批量采集形成三個時間點的計劃快照。5.1 批量采集從 AWR 里撈出 Top SQL 再逐個 XTRACT手工一條條采集不現(xiàn)實我一般用下面這個腳本從 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 循環(huán)調(diào)用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這個腳本的關(guān)鍵在于-s靜默模式和 heredoc 的配合避免 sqlplus 的交互提示卡住循環(huán)。TAG參數(shù)統(tǒng)一用batch_pre方便后續(xù)按標簽批量檢索輸出文件。實際跑的時候建議加個sleep 2避免連續(xù)采集把目標庫的 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 對齊升級前后的報告比較計劃哈希值。哈希變了說明執(zhí)行路徑變了再算成本偏差百分比。偏差超過 50% 的標紅人工看低于 20% 的基本可以忽略。參數(shù)上plan_hash_value是 Oracle 執(zhí)行計劃的唯一標識同一個計劃在不同版本下哈希值可能不同因為內(nèi)部算法有調(diào)整所以哈希變化只能作為線索最終還是要看具體的計劃步驟差異。5.3 一個我踩過的坑別在遷移窗口當天跑全量采集最后一次讓我長記性的教訓(xùn)是遷移窗口當天業(yè)務(wù)剛切過去我急著跑全量 XTRACT 驗證結(jié)果 50 條 SQL 的采集把目標庫的 CPU 打到了 95%業(yè)務(wù)監(jiān)控直接告警。后來改成遷移后第二天低峰期跑并且把批量采集的并發(fā)控制在 2 個會話以內(nèi)再沒出過問題。SQLT 的采集本身不重但CHILDALL加上大 SQL 的統(tǒng)計信息查詢累積起來對共享池和 CPU 的消耗不容小覷?，F(xiàn)在我習(xí)慣在采集腳本里加一行renice或者用nice -n 19降低優(yōu)先級讓采集進程在系統(tǒng)層面就低人一等。希望這個流程能幫你在下一次跨版本遷移里少花點時間在“為什么這條 SQL 變慢了”的玄學(xué)排查上。本文還有配套的精品資源點擊獲取