在 Oracle 資料庫日常維護與效能調校中,掌握幾條核心 SQL 查詢能事半功倍。本文整理四個高頻使用的實用語句,涵蓋追蹤檔案路徑獲取、鎖定 SQL 定位、隱藏參數檢視,以及依據作業系統 PID 反查 SQL 文本,助你快速診斷問題。
開啟 10046 事件追蹤後,快速定位生成的 .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;
說明:結合 v$mystat、v$session、v$process 取得 Server Process ID (SPID),再配合 user_dump_dest 參數與實例名拼湊完整路徑。
當資料庫出現阻塞時,快速找出持有鎖的 Session 正在執行的 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;
關鍵點:使用 NO_MERGE 提示避免視圖合併導致計畫偏差;lockwait = kaddr 精準匹配等待鎖的 Session。
隱藏參數以下劃線 _ 開頭,透過底層 X$ 表可完整列出:
SELECT name,
value,
DECODE(isdefault, 'TRUE', 'Y', 'N') AS "Default",
DECODE(ISSEM, 'TRUE', 'Y', 'N') AS SesMod,
DECODE(ISYM, 'IMMEDIATE', 'I', 'DEFERRED', 'D', 'FALSE', 'N') AS SysMod,
DECODE(IMOD, 'MODIFIED', 'U', 'SYS_MODIFIED', 'S', 'N') AS Modified,
DECODE(IADJ, '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 ISSEM,
DECODE(BITAND(ksppiflg / 65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 'FALSE') AS ISYM,
DECODE(BITAND(ksppstvf, 7), 1, 'MODIFIED', 'FALSE') AS IMOD,
DECODE(BITAND(ksppstvf, 2), 2, 'TRUE', 'FALSE') AS IADJ,
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;
欄位解讀:
當 OS 層面 (如 top、ps) 發現某 PID 佔用高 CPU,可用此語句定位其 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;
使用方式:將 &pid 替換為實際的作業系統 Process ID (如 Linux 下的 ps -ef | grep oracle 所見 SPID)。
這四條 SQL 涵蓋了追蹤、鎖定、參數、PID 定位四大核心場景,建議收藏備查。實際環境中請注意權限需求 (需 SELECT_CATALOG_ROLE 或 DBA 權限) 以及 RAC 環境下 inst_id 的處理。