Oracle 邏輯結構深度解析（二）：熱點區塊、Latch 爭用與 SQL 關聯定位完整腳本庫

核心視圖：X$BH — 緩衝區標頭快照

X$BH 記錄每個緩衝區在 Buffer Cache 中的狀態，關鍵欄位：TCH (Touch Count) 訪問次數，**TCH 越高 = 熱點區塊競爭越嚴重**。

腳本 1：Top 10 熱點緩衝區

SELECT * FROM (
  SELECT addr, ts#, file#, dbarfil, dbablk, tch
  FROM x$bh
  ORDER BY tch DESC
) WHERE ROWNUM < 11;

腳本 2：熱點區塊對應段對象 (結合 DBA_EXTENTS)

SELECT e.owner, e.segment_name, e.segment_type
FROM dba_extents e,
     (SELECT * FROM (
        SELECT addr, ts#, file#, dbarfil, dbablk, tch
        FROM x$bh ORDER BY tch DESC
      ) WHERE ROWNUM < 11) b
WHERE e.relative_fno = b.dbarfil
  AND e.block_id <= b.dbablk
  AND e.block_id + e.blocks > b.dbablk;

腳本 3：Cache Buffers Chains Latch 子閂睡眠 Top 10

SELECT * FROM (
  SELECT addr, child#, gets, misses, sleeps,
         immediate_gets igets, immediate_misses imiss, spin_gets sgets
  FROM v$latch_children
  WHERE name = 'cache buffers chains'
  ORDER BY sleeps DESC
) WHERE ROWNUM < 11;

腳本 4：最熱區塊持有的 Latch 與 Buffer 詳細資訊

SELECT b.addr, a.ts#, a.dbarfil, a.dbablk, a.tch,
       b.gets, b.misses, b.sleeps
FROM (SELECT * FROM (
        SELECT addr, ts#, file#, dbarfil, dbablk, tch, hladdr
        FROM x$bh ORDER BY tch DESC
      ) WHERE ROWNUM < 11) a,
     (SELECT addr, gets, misses, sleeps
      FROM v$latch_children
      WHERE name = 'cache buffers chains') b
WHERE a.hladdr = b.addr;

腳本 5：熱點區塊對應對象去重 (同腳本 2 別名調整)

SELECT DISTINCT e.owner, e.segment_name, e.segment_type
FROM dba_extents e,
     (SELECT * FROM (
        SELECT addr, ts#, file#, dbarfil, dbablk, tch
        FROM x$bh ORDER BY tch DESC
      ) WHERE ROWNUM < 11) b
WHERE e.relative_fno = b.dbarfil
  AND e.block_id <= b.dbablk
  AND e.block_id + e.blocks > b.dbablk;

腳本 6：熱點對象關聯 SQL 文本 (結合 V$SQLTEXT)

BREAK ON HASH_VALUE SKIP 1
SELECT /*+ RULE */ hash_value, sql_text
FROM v$sqltext
WHERE (hash_value, address) IN (
  SELECT a.hash_value, a.address
  FROM v$sqltext a,
       (SELECT DISTINCT a.owner, a.segment_name, a.segment_type
        FROM dba_extents a,
             (SELECT dbarfil, dbablk
              FROM (SELECT dbarfil, dbablk
                    FROM x$bh ORDER BY tch DESC
                   ) WHERE ROWNUM < 11) b
        WHERE a.relative_fno = b.dbarfil
          AND a.block_id <= b.dbablk
          AND a.block_id + a.blocks > b.dbablk) b
  WHERE a.sql_text LIKE '%' || b.segment_name || '%'
    AND b.segment_type = 'TABLE'
)
ORDER BY hash_value, address, piece;

後續優化路徑

1. 拿到高頻 SQL → 執行計畫分析 → 索引/改寫/提示降低訪問量。
2. 若為 CONNECT BY、PL/SQL 迴圈 等易引發熱點邏輯 → 改寫算法或調整 _DB_BLOCK_HASH_BUCKETS (隱含參數) 擴大雜湊桶減少鏈長。
3. 參考《熱點區塊競爭與解決》進階篇。