Oracle 會話與效能分析 23 條核心 SQL：等待事件、鎖、緩存命中率、CPU、熱點物件

Oracle 會話與效能分析 23 條核心 SQL：等待事件、鎖、緩存命中率、CPU、熱點物件

DBA 日常排查效能瓶頸、死鎖、高 CPU、緩存命中率低等問題，需快速從動態效能視圖 (V$/GV$) 獲取關鍵指標。本文整理 23 條實戰高頻 SQL，涵蓋 RAC 全域等待、鎖阻塞、緩存融合、共享池、PGA 排序、資源限制、Session CPU、熱點區塊等核心維度，可直接建立監控腳本或納入自動化巡檢。

核心 SQL 清單

1. 全域緩存等待：global cache busy、buffer busy global cache、buffer busy global cr 事件的檔案/區塊/等待時間。
2. 全域鎖持有者：GV$GES_ENQUEUE 結合 GV$SESSION_WAIT 找出阻塞 Session 與等待時間。
3. 緩存融合寫入比率：DBWR fusion writes / physical writes 評估 RAC 節點間資料傳輸效率。
4. 叢集互連網路：GV$CLUSTER_INTERCONNECTS 確認私有網路介面與 IP。
5. 一致性讀區塊請求延遲：gc cr block receive time / gc cr blocks received 計算平均延遲 (ms)。
6. 資源限制水位：GV$RESOURCE_LIMIT 檢查 processes、sessions、enqueues 等是否逼近上限。
7. 共享池內部結構：V$SGASTAT WHERE NAME LIKE 'g%' 觀察 Granule、KGL、KQR 等佔用。
8. 平行查詢統計：GV$PQ_SYSSTAT WHERE VALUE > 0 掌握 DFO、Slave 分配情況。
9. 回滾段進行中交易：V$ROLLNAME/ROLLSTAT/SESSION/SQLTEXT/TRANSACTION 關聯查詢正在執行的 DML 與 SQL 文本。
10. Undo 交易表：V$TRANSACTION 檢視 XIDUSN, XIDSLOT, XIDSQN, UBABLK, UBAFIL, UBAREC。
11. OS 進程對應 Session：V$PROCESS/V$SESSION 依 SPID 反查 SID、Serial#、User、Program。
12. Session 正在執行 SQL：V$SQL/V$SESSION 依 SQL_ADDRESS/HASH_VALUE 取得完整 SQL_TEXT。
13. CPU 核心數：V$PARAMETER WHERE NAME='cpu_count'。
14. Library Cache 命中率：SUM(PINS)/(SUM(PINS)+SUM(RELOADS))*100 FROM V$LIBRARYCACHE。
15. Data Dictionary Cache 命中率：(1-SUM(GETMISSES)/SUM(GETS))*100 FROM V$ROWCACHE。
16. PGA 排序記憶體命中率：Memory Sorts / (Disk Sorts + Memory Sorts) * 100。
17. Buffer Cache 命中率：(1 - physical reads / (db block gets + consistent gets)) * 100。
18. Top 10 Buffer Gets SQL：RANK() OVER (ORDER BY BUFFER_GETS DESC) 取前 10 佔比。
19. 髒區塊物件：V$BH WHERE DIRTY='Y' GROUP BY OBJD ORDER BY COUNT(*) 找出頻繁修改物件。
20. 前景進程清單：V$PROCESS WHERE BACKGROUND IS NULL。
21. 前景進程對應 SQL：V$SQL WHERE SQL_ID IN (SELECT SQL_ID FROM GV$SESSION WHERE PADDR IN (SELECT ADDR FROM GV$PROCESS WHERE PROGRAM='ORACLE.EXE (SHAD)'))。
22. Session CPU 耗用排名：V$SESSSTAT/V$SESSION/V$STATNAME WHERE NAME='CPU used by this session'。
23. 等待事件 + SQL 綜合檢視：V$SESSION_WAIT/V$SESSION/V$PROCESS/V$SQLAREA 關聯輸出 SID、Event、Wait_Time、SQL_TEXT。

使用建議

• 將上述 SQL 封裝為 Shell/Python 巡檢腳本，每 5 分鐘採集寫入歷史表，支援趨勢分析。
• RAC 環境優先使用 GV$ 視圖，單實例用 V$ 即可。
• 結合 ASH (Active Session History) 與 AWR 做深度根因分析，上述 SQL 適合即時排查。

收藏這 23 條 SQL，下次效能告警來臨時能在最短時間定位問題 Session、鎖、計劃與資源瓶頸。