識別高耗資源 SQL 是調優起點。本文系統介紹四大互補診斷法,從輕量級即時查詢到深度事後分析,構建完整 SQL 效能可視化體系。
共享池中已解析 SQL 的聚合統計,適合快速定位高頻/高耗 SQL。
SELECT
sql_id,
executions,
disk_reads,
buffer_gets,
rows_processed,
elapsed_time/1e6 elapsed_sec,
cpu_time/1e6 cpu_sec,
sql_text
FROM v$sqlarea
WHERE parsing_schema_name NOT IN ('SYS','SYSTEM')
AND executions > 0
ORDER BY buffer_gets DESC -- 或 disk_reads, elapsed_time
FETCH FIRST 20 ROWS ONLY;
| 欄位 | 含義 | 異常徵兆 |
|---|---|---|
EXECUTIONS | 執行次數 | 極高且未重用 → 硬解析風暴 |
DISK_READS | 物理讀塊數 | /EXECUTIONS > 1000 → 缺索引或統計過期 |
BUFFER_GETS | 邏輯讀塊數 | /EXECUTIONS > 10000 → 全掃描或計畫劣化 |
SHARABLE_MEM | 佔用 Shared Pool 位元組 | 單條 > 10MB → 可能為大 SQL 或計畫膨脹 |
OPTIMIZER_MODE | 優化器模式 | CHOOSE/RULE → 應遷移至 ALL_ROWS |
SELECT s.sid, s.serial#, s.username, s.status, s.sql_id,
sq.sql_text, sq.elapsed_time/1e6 elapsed_sec
FROM v$session s
JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE s.type = 'USER'
AND s.status = 'ACTIVE'
AND s.last_call_et > 60 -- 執行超過 60 秒
ORDER BY s.last_call_et DESC;
工具:Enterprise Manager、SQL Developer Monitor、oratop、MOATS。
TIMED_STATISTICS=TRUE、USER_DUMP_DEST、MAX_DUMP_FILE_SIZE=UNLIMITED。ALTER SESSION SET SQL_TRACE=TRUE; (或 DBMS_MONITOR.SESSION_TRACE_ENABLE)ALTER SESSION SET SQL_TRACE=FALSE;tkprof trace.trc output.txt EXPLAIN=user/pass SYS=NO SORT=EXEELA,FCHELLA| 指標 | 含義 | 調優方向 |
|---|---|---|
| Parse Count (Hard) | 硬解析次數 | 大 → 增加 Shared Pool、使用繫結變數 |
| Query/Current Gets | 邏輯讀 (一致性/當前模式) | 大 → 缺索引、計畫錯誤、統計過期 |
| Disk Reads | 物理讀 | 大 → 索引缺失、緩衝區太小、資料未在記憶體 |
| Library Cache Misses | Shared Pool 未命中 | >1 → Shared Pool 太小或 Cursor Sharing 問題 |
@$ORACLE_HOME/rdbms/admin/awrrpt.sql,對比兩個快照間 Top SQL、Wait Events、Instance Efficiency。@$ORACLE_HOME/rdbms/admin/ashrpt.sql,秒級採樣,適合間歇性尖峰分析。DBMS_SQLTUNE.REPORT_SQL_MONITOR,即時顯示長跑 SQL 的並行執行細節、I/O、等待分布。四大法互補:V$SQLAREA 廣度、Session 監控即時性、Trace 深度、AWR/ASH 歷史趨勢。建立「日巡 V$SQLAREA、週讀 AWR、事後 Trace」機制,讓 SQL 效能問題無所遁形。