大型 Oracle 系統優化非單點突破，而是四級調優 (OS → RDBMS → 設計 → SQL) 的系統工程。本文從九大維度給出落地方案，覆蓋架構佈局、記憶體、模型設計、物理結構、索引、並行、資源管控、應用連接、網路流量，構建全鏈路優化體系。
一、採用 OFA (Optimal Flexible Architecture) 邏輯佈局
將邏輯對象按「使用方式、物理特徵」分類隔離：
- 系統數據 / 用戶數據 分離
- 表數據 / 索引數據 分離
- 高活躍表 / 低活躍表 分離
- 歸檔/歷史 / 當前數據 分離
五大準則： 同類段聚合、標準化命名、例外區隔離、最小化 Tablespace 衝突、Data Dictionary 獨立。
效益： 維護簡化、I/O 均衡、恢復粒度細、擴容無痛。
二、極致利用 SGA (System Global Area)
SGA 核心四組件 + 進階池：
| 組件 | 佔比建議 | 關鍵參數 | 調優焦點 |
|---|
| DB Buffer Cache | 1-2% 資料庫大小 | DB_CACHE_SIZE / DB_BLOCK_BUFFERS | 命中率 > 95%、LRU 鎖競爭 |
| Dictionary Cache | Shared Pool 內 | SHARED_POOL_SIZE | 避免 Recursive Call、GETS/GETMISSES |
| Redo Log Buffer | MB 級 | LOG_BUFFER | 避免 Log Buffer Space 等待 |
| Shared Pool (Library Cache) | 依 SQL 量 | SHARED_POOL_SIZE | 硬解析率 < 1%、SQL 共用率 |
| Large Pool | RMAN/PQ/MTS | LARGE_POOL_SIZE | 隔離大操作不衝擊 Shared Pool |
| Java Pool | 10MB+ | JAVA_POOL_SIZE | Java Stored Procedure 應用 |
| Multiple Buffer Pools | KEEP/RECYCLE/DEFAULT | BUFFER_POOL_KEEP/RECYCLE | 熱表進 KEEP、全掃描表進 RECYCLE |
原則： 足夠大的記憶體讓熱數據常駐，物理 I/O 降至最低。
三、範式化與反範式化平衡設計
- 1NF/2NF/3NF 範式化： 消除冗余、節省空間、加快 DML、但查詢需多表 Join。
- 反範式化技術： 增加冗余列、派生列、合併表 (預聚合/物化視圖)。
- 決策矩陣：
| 數據特徵 | 設計策略 |
|---|
| 高頻查詢、低頻修改 | 物理反範式化 (寬表、冗余列、物化視圖) |
| 高頻修改、低頻查詢 | 物理範式化 (標準 3NF) |
| 混合負載 | 邏輯 3NF 基礎上，物理選擇性反範式化 |
核心：以存取模式驅動物理模型，邏輯保持範式純粹，物理務實取捨。
四、表設計與管理精要
- 分區表： 範圍/列表/雜湊/複合分區 → 分區裁剪、分區維護、I/O 分散、生命週期管理。
- 避免行遷移/鏈接： 正確設定
PCTFREE/PCTUSED (或 ASSM)，預留更新空間。
- 碎片控制： 靜態/動態對象分 Tablespace、統一 Extent 大小 (LMT Uniform)、監控
DBA_SEGMENTS.EXTENTS > 10。
- 別名優化： 查詢中表/列別名 (單字母) 可減少解析開銷、提升 1.5x 速度 (舊版觀察值)。
- 回滾段/Undo 交替： 多 Undo Tablespace +
UNDO_TABLESPACE 動態切換，均勻磁碟負載。
五、索引優化設計
- 索引組織表 (IOT) / 局部分區索引： 大表分區後建局部索引，避免全局索引膨脹擠佔 Buffer Cache。
- 叢集： 共同鍵值多表共塊存儲，Join 變單塊讀取。
- 索引生效鐵律： WHERE 條件含複合索引首列、列不參與運算/函數/隱式轉換、選擇率 < 10-15%。
- 監控：
INDEX_STATS、V$OBJECT_USAGE (11g+)、DBA_INDEXES.CLUSTERING_FACTOR。
六、多 CPU 與並行查詢 (PQO) 榨乾硬體
- 多 CPU 並行：
PARALLEL_DEGREE_POLICY=AUTO、PARALLEL_MAX_SERVERS、PARALLEL_MIN_SERVERS。
- PQO 效能： 全表掃描、大索引範圍掃描、大量數據裝載、分區表操作自動並行化。
- SQL*Loader Direct Path： 繞過 Buffer Cache、Redo、Undo，直接格式化寫入資料檔，裝載速度極快。
七、Database Resource Manager (DRM) 實施資源配額
- 定義 Consumer Group (OLTP_GROUP、BATCH_GROUP、REPORT_GROUP)。
- 分配 CPU 權重：OLTP 70%、BATCH 20%、REPORT 10%。
- 多級計畫：白天/夜間/維護窗口自動切換。
- 限制並行度：
PARALLEL_DEGREE_LIMIT_P1 等指令防止惡意 SQL 佔滿 PQ Slaves。
八、最優連接與 SQL 優化
- OLE DB 直連： 繞過 ODBC 轉換層，降低延遲。
- Connection Pool (IIS/中間件/OCI Session Pool/DRCP)： 複用物理連接，消除連接建立開銷 (通常 50-200ms/次)。
- SQL 設計七條：
- 減少 Round-trip (批量、快照、物化視圖)。
- SQL 文本統一 (綁定變數) 共用 Cursor。
- 限制動態 SQL (Hard Parse 杀手)。
- 杜絕無條件全表掃描。
- 約束下沉 DDL (Check/Not Null/FK) 而非應用碼。
- 關閉 Auto-Commit，批量提交。
- 大結果集設定 Row Prefetch / Fetch Size。
九、後台處理減少網路流量
- 臨時表/視圖/物化視圖： 複雜多表 Join 預計算落地，前端單表查詢。
- PL/SQL 包封裝： 業務邏輯下沉資料庫，單次 CALL 替代多次 SQL 往返。
- 資料複製/快照/遠程程序調用： 就近訪問、異步同步、減少跨網路 SQL 呼叫。
總結：優化是系統整體觀
無銀彈，有體系。從 OFA 佈局、SGA 記憶體、模型範式、物理分區、索引策略、並行硬體、資源配額、連接復用、後台計算九大支柱，配合 AWR/ASH 持續觀測、基線管理、變更控制，方能構建高效、穩定、可擴展的大型 Oracle 系統。
