萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

Oracle 實用 SQL 查詢語句整理:追蹤檔案、鎖定分析、隱藏參數與 PID 定位

前言

在 Oracle 資料庫日常維護與效能調校中,掌握幾條核心 SQL 查詢能事半功倍。本文整理四個高頻使用的實用語句,涵蓋追蹤檔案路徑獲取、鎖定 SQL 定位、隱藏參數檢視,以及依據作業系統 PID 反查 SQL 文本,助你快速診斷問題。

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

開啟 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 參數與實例名拼湊完整路徑。

2. 顯示產生鎖定的 SQL 語句

當資料庫出現阻塞時,快速找出持有鎖的 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。

3. 查看 Oracle 隱藏參數

隱藏參數以下劃線 _ 開頭,透過底層 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;

欄位解讀:

  • Default:是否為預設值
  • SesMod:Session 層級是否可修改
  • SysMod:System 層級修改方式 (IMMEDIATE/DEFERRED)
  • Modified:是否被修改過 (MODIFIED/SYS_MODIFIED)
  • Adjusted:是經過調整的非預設值

4. 依據作業系統 PID 反查正在執行的 SQL

當 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 的處理。

copyright © 萬盛學電腦網 all rights reserved