Oracle 性能檢測核心 SQL 語句詳解 (7 大維度)

1. 實例等待事件監控

SELECT EVENT,
       SUM(DECODE(WAIT_TIME,0,0,1)) "Prev",
       SUM(DECODE(WAIT_TIME,0,1,0)) "Curr",
       COUNT(*) "Tot"
FROM V$SESSION_WAIT
GROUP BY EVENT
ORDER BY 4;

解讀：Curr 為當前正在等待會話數，Tot 為總會話數。重點關注 db file scattered read (全表掃描)、log file sync (提交等待)、latch free (鎖爭用) 等事件。

2. 回滾段爭用分析

SELECT NAME, WAITS, GETS, WAITS/GETS "Ratio"
FROM V$ROLLSTAT A, V$ROLLNAME B
WHERE A.USN = B.USN;

指標：Ratio > 0.01 (1%) 表示爭用嚴重，需增加回滾段或調整 TRANSACTIONS_PER_ROLLBACK_SEGMENT。

3. 表空間 I/O 分佈

SELECT DF.TABLESPACE_NAME NAME, DF.FILE_NAME "File",
       F.PHYRDS Pyr, F.PHYBLKRD Pbr,
       F.PHYWRTS Pyw, F.PHYBLKWRT Pbw
FROM V$FILESTAT F, DBA_DATA_FILES DF
WHERE F.FILE# = DF.FILE_ID
ORDER BY DF.TABLESPACE_NAME;

用途：識別熱點表空間，指導數據文件佈局 (分離數據/索引/臨時/Undo 到不同磁盤)。

4. 數據文件級 I/O

SELECT SUBSTR(A.FILE#,1,2) "#", SUBSTR(A.NAME,1,30) "Name",
       A.STATUS, A.BYTES, B.PHYRDS, B.PHYWRTS
FROM V$DATAFILE A, V$FILESTAT B
WHERE A.FILE# = B.FILE#;

5. 用戶下所有索引結構

SELECT UI.TABLE_NAME, UI.INDEX_NAME, UI.UNIQUENESS, UIC.COLUMN_NAME
FROM USER_IND_COLUMNS UIC, USER_INDEXES UI
WHERE UIC.INDEX_NAME = UI.INDEX_NAME
  AND UIC.TABLE_NAME = UI.TABLE_NAME
ORDER BY UI.TABLE_TYPE, UI.TABLE_NAME, UI.INDEX_NAME, UIC.COLUMN_POSITION;

6. SGA 緩衝區命中率

SELECT A.VALUE + B.VALUE "Logical_Reads", C.VALUE "Phys_Reads",
       ROUND(100 * ((A.VALUE+B.VALUE)-C.VALUE) / (A.VALUE+B.VALUE)) "BUFFER_HIT_RATIO"
FROM V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C
WHERE A.STATISTIC# = 38   -- db block gets
  AND B.STATISTIC# = 39   -- consistent gets
  AND C.STATISTIC# = 40;  -- physical reads

標準：OLTP > 95%，DSS > 90%。偏低時考慮增大 DB_CACHE_SIZE 或優化高邏輯讀 SQL。

7. 字典緩衝區命中率

SELECT PARAMETER, GETS, GETMISSES,
       GETMISSES/(GETS+GETMISSES)*100 "Miss_Ratio",
       (1-(SUM(GETMISSES)/(SUM(GETS)+SUM(GETMISSES))))*100 "Hit_Ratio"
FROM V$ROWCACHE
WHERE GETS+GETMISSES <> 0
GROUP BY PARAMETER, GETS, GETMISSES;

關注：dc_users、dc_tablespaces、dc_segments 等核心字典 Miss Ratio 應 < 2%。

使用建議

• 定時任務采集上述指標建立基線
• 結合 AWR/ASH 報告綜合分析
• 單一指標異常不可直接下結論，需關聯業務負載變化