您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

DBA 日常必備實用 SQL 腳本精選：Trace 檔路徑、鎖定 SQL、隱藏參數、PID 關聯查詢

編輯：關於SqlServer

前言

整理自實戰環境的 4 個高頻診斷腳本，涵蓋追蹤檔定位、鎖爭用分析、隱藏參數巡檢、進程關聯 SQL 追蹤，可直接收藏至工具箱。

1. 取得當前 Session 的 Trace 檔完整路徑

用於快速定位 user_dump_dest 下的 .trc 檔案進行效能分析。

SELECT 
    d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' || p.spid || '.trc' AS trace_file_name
FROM 
    (SELECT p.spid FROM v$mystat m, v$session s, v$process p 
     WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
    (SELECT t.INSTANCE FROM v$thread t, v$parameter v 
     WHERE v.NAME = 'thread' AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,
    (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;

說明：Oracle 專用視圖 (v$)，拼接實例名、OS 進程 ID (spid) 生成標準 trace 檔名格式。

2. 顯示產生鎖定的 SQL 語句 (即時鎖分析)

快速鎖定「誰鎖了誰、執行什麼 SQL」。

SELECT /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */
    a.username, a.machine, a.sid, a.serial#, a.last_call_et AS "Seconds",
    b.id1, c.sql_text AS "SQL"
FROM v$session a, v$lock b, v$sqltext c
WHERE a.username IS NOT NULL
  AND a.lockwait = b.kaddr
  AND c.hash_value = a.sql_hash_value
ORDER BY a.sid, c.piece;

關鍵點：

NO_MERGE Hint 防止視圖合併導致計劃失真。
lockwait = kaddr 連接等待鎖的 Session 與鎖資源。
sql_hash_value 關聯 v$sqltext 還原完整 SQL 文本 (按 piece 排序)。

3. 查看 Oracle 所有隱藏參數 (含描述、是否預設、修改狀態)

隱藏參數 (以下劃線開頭) 影響核心行為，巡檢/調優必查。

SELECT 
    name, value, 
    DECODE(isdefault, 'TRUE', 'Y', 'N') AS "Default",
    DECODE(isses_modifiable, 'TRUE', 'Y', 'N') AS "SesMod",
    DECODE(issys_modifiable, 'IMMEDIATE', 'I', 'DEFERRED', 'D', 'FALSE', 'N') AS "SysMod",
    DECODE(ismodified, 'MODIFIED', 'U', 'SYS_MODIFIED', 'S', 'N') AS "Modified",
    DECODE(isadjusted, 'TRUE', 'Y', 'N') AS "Adjusted",
    description
FROM (
    SELECT x.inst_id AS instance, x.indx + 1, ksppinm AS name, ksppity, 
           ksppstvl AS value, ksppstdf AS isdefault,
           DECODE(BITAND(ksppiflg/256,1),1,'TRUE','FALSE') AS isses_modifiable,
           DECODE(BITAND(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED','FALSE') AS issys_modifiable,
           DECODE(BITAND(ksppstvf,7),1,'MODIFIED','FALSE') AS ismodified,
           DECODE(BITAND(ksppstvf,2),2,'TRUE','FALSE') AS isadjusted,
           ksppdesc AS description
    FROM x$ksppi x, x$ksppsv y 
    WHERE x.indx = y.indx 
      AND SUBSTR(ksppinm,1,1) = '_'  -- 只取隱藏參數
      AND x.inst_id = USERENV('Instance')
) ORDER BY name;

輸出欄位含義：Default 是否預設值、SesMod Session 級可修改、SysMod 系統級修改生效方式 (I=即時/D=延遲/N=不可改)、Modified U=用戶修改/S=系統修改。

4. 根據 OS 進程 PID 反查正在執行的 SQL

當 OS 層面 (top/ps) 發現高 CPU 進程 (PID)，需快速定位其執行的 SQL。

SELECT /*+ ORDERED */ sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
    SELECT 
        DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
        DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
    FROM v$session b
    WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '&pid')
)
ORDER BY piece ASC;

使用方法：在 SQL*Plus/SQL Developer 中執行，輸入目標 PID (如 12345)，自動替換 &pid 變數。
邏輯：v$process.spid → v$session.paddr → v$session.sql_hash_value/prev_hash_value → v$sqltext。DECODE 處理當前/上一條 SQL 切換。