效能調優核心原則與評估指標
核心原則：以最少磁碟 I/O 獲取所需資料。評估指標包含：響應時間與吞吐量平衡、資料庫可用性、緩衝區命中率、記憶體使用效率。
兩大優化階段
階段一：設計開發階段（主動優化，成本最低）
2.1 應用架構選型
- C/S 兩層 vs B/W/D 三層架構，資源需求差異大
- 三層架構利於連線池管理，減少資料庫連線壓力
2.2 進階功能善用
- 分區表：適用資料快速增長的業務表
- 索引策略：高頻查詢欄位建立索引
- 歷史歸檔：完成業務流程的靜態資料移至歷史表，減少主表掃描量
2.3 SQL 撰寫規範
- 善用繫結變數，提升 SQL 重用率，減少硬解析
- 合理使用索引，避免全表掃描
- 推薦使用 Oracle Optimizer 與行級鎖管理器
2.4 磁碟 I/O 佈局規劃
開發前完成，核心原則：分離與均衡
- 表與索引分離不同表空間/磁碟
- 使用者表空間與 SYSTEM 表空間分離
- 回滾段專用表空間，避免空間競爭
- 建立暫存表空間專供排序作業，減少碎片
2.5 區塊大小與儲存參數確定
- DB_BLOCK_SIZE 建庫後不可變更，需依樣本資料測試決定
- 儲存參數（INITIAL, NEXT, PCTINCREASE）依業務增長趨勢預估
- 目標：減少資料鏈結與列遷移，提升磁碟利用率
階段二：運行維護階段
3.1 作業系統層級調優
目標：減少記憶體交換、控制分頁、使 SGA 駐留記憶體
監控工具
vmstat 5：每 5 秒顯示程序、虛擬記憶體、磁碟、分頁、CPU 活動sar -p 10 10：每 10 秒顯示 10 次分頁活動摘要
優化措施
- 停止不必要的 daemon 進程
- 在不影響命中率前提下減少 DB 緩衝區
- 減少 UNIX 檔案緩衝區（裸裝置尤重要）
- 安裝更多實體記憶體
- 調整核心參數保持 SGA 在單一共享記憶體段
3.2 資料庫層級調優
SGA 核心組件效能調優
共用池
- 庫快取命中率：
SELECT gethitratio FROM v$librarycache 應 > 90%
- 重載率：
SELECT SUM(reloads)/SUM(pins) FROM v$librarycache 應 < 1%
- 資料字典快取：
SELECT SUM(getmisses)/SUM(gets) FROM v$rowcache 應 < 15%
- 大型匿名區塊轉為預存程序提升重用
- 熱門物件使用
DBMS_SHARED_POOL.KEEP 釘住記憶體
資料區塊緩衝區
- 命中率查詢：
SELECT 1-(phy.value/(cur.value+con.value)) FROM v$sysstat cur, v$sysstat con, v$sysstat phy WHERE cur.name='db block gets' AND con.name='consistent gets' AND phy.name='physical reads'
- 命中率 < 85% 需增加緩衝區大小
- LRU 演算法管理淘汰機制
重做日誌緩衝區
- 過小導致等待，需適度調大 LOG_BUFFER 參數
I/O 與資源競爭優化
- 採用本地管理表空間（LMT），減少資料字典競爭
- 預先分配足夠空間，避免動態擴展影響效能
- 監控低效 SQL：缺少索引或未有效使用索引
- 調整初始化參數：
db_block_lru_latches、dml_locks 減少鎖競爭
常用工具與腳本
utlbstat.sql / utlestat.sql：時段效能快照- 動態效能視圖（V$ 系列）與資料字典視圖
- Oracle Enterprise Manager 圖形化監控
結語
效能調優是系統工程，需綜合運用上述規律，從建庫伊始規劃表空間、儲存參數、記憶體配置。持續分析運行過程中出現的效能問題，在大量實踐中積累經驗，方能保障 Oracle 高效穩定運行。
