您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

效能診斷黃金法則：用擴展 SQL 跟蹤 (10046) 量化「到底花時間在哪裡」

編輯：Oracle 資料庫基礎

核心隱喻：開車遲到怎麼找原因？

錯誤做法：檢查車身剮蹭、輪胎定位、引擎馬力 (對應：檢查緩存命中率、全表掃描、排序區大小)。
正確做法：問「什麼事讓我多花了 20 分鐘？」→ 交通堵塞、迷路、出發太晚 (對應：跟蹤等待事件、SQL 執行計畫、應用邏輯)。

三步驟科學診斷流程

測量：對目標程式開啟擴展 SQL 跟蹤 (Level 12)，獲取逐步耗時記錄。
剖析：產生 資源剖析表，將響應時間按類別 (CPU、db file sequential read、log file sync、SQL*Net message from client… ) 降序排列。
響應：優先處理佔比最大的類別 — 減少調用次數 (改寫 SQL、調整應用、加索引) 或 降低單次耗時 (優化 I/O 路徑、減少鎖持有)。

開啟跟蹤的四種方式

場景	方法	關鍵設定
自有程式碼	SQL*Plus / 應用內	`ALTER SESSION SET TIMED_STATISTICS=TRUE; MAX_DUMP_FILE_SIZE=UNLIMITED; EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'`
自有程式碼 (推薦)	PL/SQL	`DBMS_SUPPORT.START_TRACE(WAITS=>TRUE, BINDS=>TRUE); ... STOP_TRACE();`
他人 Session (已知 SID/SERIAL#)	DBA	`DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION(sid, serial#, 'timed_statistics', TRUE); DBMS_SUPPORT.START_TRACE_IN_SESSION(sid, serial#, WAITS=>TRUE, BINDS=>TRUE);`
10g+ 業務標籤追蹤	DBA	`DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name=>'APP', module_name=>'PAYROLL', action_name=>'PYUGEN', WAITS=>TRUE, BINDS=>TRUE);`

資源剖析表示例

Response Time Component          Duration   %
------------------------------------------  -------  ---
db file scattered read (全表掃描)      28.3 min  59%
log file sync (Commit 等待)             7.2 min  15%
SQL*Net message from client (閒待)      5.2 min  11%
CPU (服務時間)                          4.0 min   8%
Other                                   3.1 min   6%
Total                                  47.8 min 100%

結論：優化全表掃描 (加索引/分區/改寫) 效益最大；買更快 CPU 僅能改善 8%。

工具輔助

tkprof：快速聚合，但會丟失原始跟蹤中的等待參數、Bind 變數值、遞迴呼叫細節。
trcanlzr / 自製解析器：可完整還原資源剖析表。
原則：跑 tkprof 前先看原始 .trc，關鍵細節往往在原始檔。

常見等待事件對應處置

等待事件	典型成因	處置方向
db file sequential/scattered read	索引/全表掃描 I/O	加索引、分區、調整 clustering factor、檢查統計資訊
log file sync	Commit 過於頻繁、Redo Log 檔案配置不當	批次 Commit、Redo Log 多路徑/固態硬碟
enq: TX - row lock contention	熱點行並發更新	縮短交易、應用層樂觀鎖、分區/分表
latch: cache buffers chains	熱點 Block 並發存取	增加 _db_block_hash_buckets (隱含參數)、改寫 SQL
SQL*Net message from client	應用邏輯頻繁往返、網路延遲	Array Fetch/批次處理、就近部署中介層