生產環境 Oracle 數據庫 CPU 飆升至 100%，業務響應嚴重延遲。本文記錄一次真實排查案例，演示從操作系統層面定位高消耗進程，層層關聯至具體 SQL 文本的標準化排查鏈路，適用於 Oracle 10g/11g/12c/19c 等主流版本。
top/pidstat 找出高 CPU 進程 PID（如 1217）。V$PROCESS 以 SPID = PID 查得 ADDR（進程地址）。V$SESSION 以 PADDR = ADDR 查得 SID、SERIAL#、SQL_HASH_VALUE。V$SQLTEXT/V$SQL/V$SQLAREA 以 HASH_VALUE 取得完整 SQL_TEXT。-- 保存為 find_top_sql.sql，SQL*Plus 執行 @find_top_sql <OS_PID>
SET LINESIZE 300 PAGESIZE 1000 LONG 1000000
COL SQL_TEXT FOR A120
VAR v_pid NUMBER
EXEC :v_pid := &1;
SELECT s.SID, s.SERIAL#, s.USERNAME, s.OSUSER, s.MACHINE, s.PROGRAM,
s.PROCESS, s.LOGON_TIME, s.SQL_HASH_VALUE,
sq.SQL_TEXT
FROM V$SESSION s
JOIN V$PROCESS p ON s.PADDR = p.ADDR
JOIN V$SQL sq ON s.SQL_HASH_VALUE = sq.HASH_VALUE
WHERE p.SPID = :v_pid
AND s.STATUS = 'ACTIVE';$ top -bn1 | head -20
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
6799 oracle 20 0 1.200g 1.100g 1.000g R 99.7 12.3 12:34.56 oracle
$ sqlplus / as sysdba @find_top_sql 6799
SID SERIAL# USERNAME OSUSER MACHINE PROGRAM PROCESS LOGON_TIME SQL_HASH_VALUE
---- ------- -------- ------ --------- ----------------- -------- --------------- --------------
483 1224 PORTAL www16 bj_wap03 JDBC Thin Client 6799 2024-01-15 02:21 3141592653
SQL_TEXT
--------------------------------------------------------------------------------
SELECT * FROM SPINFO WHERE SPID = :1| 可能原因 | 驗證方法 | 優化方向 |
|---|---|---|
| 缺少索引/全表掃描 | EXPLAIN PLAN FOR ...; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); | 在 SPID 列建立索引 |
| 綁定變量窺探導致計劃失效 | V$SQL_CS_HISTOGRAM 檢查多計劃 | 使用 /*+ BIND_AWARE */ 或修正統計信息 |
| 高頻調用（如循環單行查詢） | V$SQLAREA.EXECUTIONS 對比 ELAPSED_TIME | 應用層批量化、緩存、改為 JOIN |
| 統計信息過期 | DBA_TAB_STATISTICS.LAST_ANALYZED | DBMS_STATS.GATHER_TABLE_STATS |
DBMS_SCHEDULER 定時任務，每 5 分鐘採集 V$SESSION_WAIT、V$SQLAREA Top 10 CPU SQL，寫入歷史表。CPU_TIME > 1000000 (微秒) 或 EXECUTIONS > 10000/分鐘 觸發郵件/釘釘通知 DBA。ASH（Active Session History）報告：DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT 事後根因分析。| 工具/視圖 | 用途 | 典型命令 |
|---|---|---|
| top / pidstat | OS 級進程 CPU 佔用 | pidstat -p <PID> 1 10 |
| V$PROCESS | Oracle 後台/前台進程映射 | SELECT ADDR, SPID FROM V$PROCESS WHERE SPID=1217 |
| V$SESSION | 用戶會話狀態、當前 SQL | SELECT SID, SQL_HASH_VALUE FROM V$SESSION WHERE PADDR=... |
| V$SQL / V$SQLAREA | SQL 文本、執行計劃、統計 | SELECT SQL_FULLTEXT FROM V$SQL WHERE HASH_VALUE=... |
| DBMS_XPLAN | 執行計劃格式化顯示 | SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id)) |
| SQL Trace / TKPROF | 會話級詳細追踪 | ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12' |
「OS PID → V$PROCESS → V$SESSION → V$SQL」四步關聯法是 Oracle 性能排查的基礎功夫。關鍵在於熟練掌握視圖關聯鍵（SPID→ADDR→PADDR→HASH_VALUE），並建立自動化採集機制，將「事後排查」轉化為「事前預警」。
