Oracle 以高可靠、高安全、高相容性廣泛應用於金融、電信、製造、航太等關鍵領域。隨著資料量爆發,效能惡化常表現為「回應時間過長」。根因多元:作業系統資源爭用、參數設定不當、應用 SQL 劣化。DBA 需具備全棧視野,敏銳定位瓶頸並系統性優化。
| 組件 | 關鍵參數 | 調優重點 |
| Shared Pool | SHARED_POOL_SIZE | Library Cache 命中率 > 99%,Dictionary Cache > 90% |
| Buffer Cache | DB_CACHE_SIZE / DB_BLOCK_BUFFERS | 緩衝區命中率 > 95%(OLTP),大表全掃描考慮 KEEP/RECYCLE 池 |
| Large Pool | LARGE_POOL_SIZE | RMAN、Shared Server、Parallel Query 分配 |
| Java Pool | JAVA_POOL_SIZE | Java Stored Procedure 使用 |
| Streams Pool | STREAMS_POOL_SIZE | Streams/GoldenGate/CDC |
1.3 作業系統層面
- 檔案系統:ext4/xfs +
noatime,nodiratime、ASM 原始裝置
- I/O 排程器:
deadline 或 noop (SSD/NVMe)
- HugePages:預留足夠 HugePages 給 SGA,
vm.nr_hugepages
- 網路:Jumbo Frames (MTU 9000)、TCP 調優
二、參數優化
2.1 自動記憶體管理 (AMM/ASMM)
-- 11g+ 建議:ASMM (自動共享記憶體管理)
MEMORY_TARGET = 0 -- 關閉 AMM
SGA_TARGET = 16G -- 目標 SGA
PGA_AGGREGATE_TARGET = 8G -- 目標 PGA
手動管理僅適用極特殊負載,現代版本強烈建議 ASMM。
2.2 優化器統計資訊
-- 定期收集(含直方圖)
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME', METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');
-- 表級鎖定統計(穩定計劃)
EXEC DBMS_STATS.LOCK_TABLE_STATS('SCHEMA', 'TABLE');
2.3 關鍵參數清單
| 參數 | 建議 | 說明 |
| OPTIMIZER_MODE | ALL_ROWS | OLTP 首選 FIRST_ROWS_n,倉儲 ALL_ROWS |
| OPTIMIZER_INDEX_COST_ADJ | 10-50 | 降低索引成本權重,避免全掃 |
| OPTIMIZER_INDEX_CACHING | 80-90 | 假設索引快取比例 |
| CURSOR_SHARING | EXACT | FORCE/SIMILAR 易產生計劃不穩,謹慎用 |
| DB_FILE_MULTIBLOCK_READ_COUNT | 128/256 | 全掃描多區塊讀取,依存儲調整 |
| PARALLEL_DEGREE_POLICY | AUTO | 自動平行度 |
三、應用優化(最高 ROI)
3.1 SQL 改寫核心原則
- 避免
SELECT *,明確列名
- WHERE 條件用索引欄位,避免函數/運算導致索引失效
- EXISTS 替代 IN (子查詢),NOT EXISTS 替代 NOT IN
- UNION ALL 替代 UNION(無去重需求)
- 分頁查詢用
OFFSET ... FETCH (12c+) 或 ROWNUM 技巧
- 綁定變數!杜絕硬解析風暴
3.2 索引策略
- 選擇度高 (>10%) 建 B-tree,低選擇度位圖索引 (DW)
- 複合索引遵循「最左前綴」,高選擇度欄位前置
- 避免過度索引:每表 5-8 支上限,DML 開銷大
- 定期
DBMS_STATS.GATHER_INDEX_STATS、ALTER INDEX REBUILD ONLINE 整理碎片
3.3 架構層面
- 連線池:HikariCP / UCP / DRCP,避免頻繁連線建立
- 快取:Redis 做熱點資料 L2 Cache,減輕 DB 壓力
- 讀寫分離:Data Guard Active Standby / GoldenGate 複寫至報表庫
- 分區表:歷史資料按時間分區,Partition Pruning 消除掃描
- 壓縮:Advanced Row Compression / Hybrid Columnar Compression (Exadata)
四、預見性容量規劃
- 建立效能基線(AWR/ASH/Statspack 快照)
- 趨勢分析:資料增長率、交易量增長、CPU/記憶體/IO 使用率曲線
- 提前 3-6 個月規劃擴容(硬體採購、架構重構、分庫分表)
結語
Oracle 優化無銀彈,唯有「度量→分析→優化→驗證」閉環,配合對業務增長的預判,才能持續交付穩定效能。環境是基石,參數是引擎,應用是方向盤——三者缺一不可。
copyright ©
萬盛學電腦網 all rights reserved