Oracle磁碟I/O優化全攻略：檔案分佈、競爭消除、Checkpoint與Latch調優

一、啟用效能統計

-- init.ora
TIMED_STATISTICS = TRUE

-- Session層
ALTER SESSION SET TIMED_STATISTICS = TRUE;

-- 統計腳本 (在正常負載期間執行)
@$ORACLE_HOME/rdbms/admin/utlbstat.sql  -- 開始
-- 等待代表性負載期
@$ORACLE_HOME/rdbms/admin/utlestat.sql  -- 結束，產生report.txt

二、I/O分佈策略 (核心原則)

Striping技術

• 大表跨多磁碟條帶化 (RAID 0/10/ASM)
• 表空間多資料檔分散不同實體裝置

三、避免動態空間擴展

-- 檢查遞歸呼叫 (動態擴展指標)
SELECT name, value FROM v$sysstat WHERE name = 'recursive calls';

• 建表/索引/回滾段時指定足夠INITIAL/NEXT，一次配置足夠空間
• 回滾段：OLTP用小段，長查詢/大交易指派大段 (SET TRANSACTION USE ROLLBACK SEGMENT)
• 啟用ASSM (自動段空間管理) 消除自由列表競爭

四、消除遷移與鏈結行

1. ANALYZE TABLE ... LIST CHAINED ROWS 收集資訊至CHAINED_ROWS
2. 建立中間表 → 刪除遷移行 → 重新插入 → 刪除中間表
3. 再次ANALYZE，剩餘為真鏈結行 (單行超過區塊)，只能增大DB_BLOCK_SIZE解決

五、排序優化 (參考前文)

• 監控V$SYSSTAT sorts(disk)/sorts(memory) < 5%
• 增大PGA_AGGREGATE_TARGET 或 SORT_AREA_SIZE
• 資料預排序時用CREATE INDEX ... NOSORT

六、Checkpoint調優

• 頻繁Checkpoint → 恢復快但I/O多、效能低
• 檢查ALTER.LOG 是否有 checkpoint not complete
• 監控 V$SYSTEM_EVENT log file switch (checkpoint incomplete)
• 調整 LOG_CHECKPOINT_INTERVAL、LOG_CHECKPOINT_TIMEOUT、FAST_START_MTTR_TARGET (9i+)

七、LGWR/DBWn I/O調優

八、各類競爭診斷與解決

1. 調度器競爭 (共用伺服器)

-- 忙碌率 > 50% 增加調度器
SELECT network, SUM(busy)/(SUM(busy)+SUM(idle)) "TOTAL BUSY RATE"
FROM v$dispatcher GROUP BY network;

-- 平均等待時間
SELECT network, DECODE(SUM(totalq),0,'NO RESPONSES',
       SUM(wait)/SUM(totalq)||' HUNDREDTHS OF SECONDS') "AVG WAIT"
FROM v$queue q, v$dispatcher d
WHERE q.type='DISPATCHER' AND q.paddr=d.paddr
GROUP BY network;

2. 共用伺服器進程競爭

SELECT DECODE(totalq,0,'No Requests', wait/totalq||' hundredths of seconds') "AVG WAIT"
FROM v$queue WHERE type='COMMON';

3. Redo Log Buffer Latch競爭

-- Allocation Retries 持續增長 → 增大 LOG_BUFFER
SELECT value FROM v$sysstat WHERE name = 'redo buffer allocation retries';

-- Latch 統計
SELECT ln.name, gets, misses, immediate_gets, immediate_misses
FROM v$latch l, v$latchname ln
WHERE ln.name IN ('redo allocation', 'redo copy') AND ln.latch#=l.latch#;

-- Allocation Latch 爭用 → 降低 LOG_SMALL_ENTRY_MAX_SIZE
-- Copy Latch 爭用 → 增加 LOG_SIMULTANEOUS_COPIES (最高 2×CPU)

4. Free List競爭

1. V$WAITSTAT 確認 DATA BLOCK 競爭
2. V$SYSTEM_EVENT 確認 BUFFER BUSY WAITS
3. V$SESSION_WAIT 定位 FILE/BLOCK
4. DBA_EXTENTS 反查 SEGMENT
5. DBA_SEGMENTS 確認 FREELISTS 數量
6. 重建表指定 FREELISTS N (或用ASSM徹底解決)

九、資料庫檔案佈局方針速查

• 表/索引分不同表空間 → 不同磁碟
• Redo Log/回滾段 → 不同磁碟
• Oracle軟體/資料檔 → 不同磁碟
• 最熱點表/索引 → 最快磁碟
• 資料磁碟禁止安裝第三方軟體

十、I/O分佈監控查詢

SELECT name, phyrds, phywrts, (phyrds+phywrts) total_io
FROM v$datafile df, v$filestat fs
WHERE df.file# = fs.file#
ORDER BY total_io DESC;

依總I/O量調整檔案分佈，將高I/O檔案移至獨立/更快磁碟。

1. 上一篇文章： 在Oracle中使用Java編寫存儲過程：從Hello World到實戰擴展
2. 下一篇文章： Oracle僅更新兩表對應資料的兩種高效寫法與WHERE陷阱

Oracle 資料庫基礎

DB2 與 Oracle 異構數據庫雙向復制技術全解析：基於 WebSphere II 聯邦架構

背景與動機 企業數據常分散於 DB2、Oracle、SQL

Oracle TO_DATE 與 TO_CHAR 日期轉換完全指南：格式模型、常見錯誤與最佳實踐

核心函數語法 TO_DATE(string, format_

Oracle 與 SQL Server 資料類型對照表完整版

主流關聯式資料庫資料類型差異快速查閱 Oracle 與 SQ

Oracle 檢查點 (Checkpoint) 機制深度剖析：增量檢查點、CKPT 進程與實例恢復

檢查點核心目的 以**最小影響**業務的方式刷新髒塊 保障