您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫相關知識

Oracle 資料庫與應用程式效能優化全攻略：從設計階段到運行期調校

編輯：Oracle 資料庫相關知識

優化核心原則

「以最少的磁碟 I/O 取得所需資料」

評估指標：回應時間 vs 吞吐量、可用性、緩衝區命中率、記憶體使用效率。

階段一：系統設計開發階段優化

1. 應用架構選型

C/S 兩層：Client 直連 DB，連接數多、網路壓力大
B/S 三層 (Web/App/DB)：連接池集中管理、可水平擴展 App Server，**推薦**

2. 表空間與資料檔佈局

原則	說明	效益
表與索引分離	不同表空間、不同磁碟	並行 I/O、減少爭用
用戶表空間與 SYSTEM 分離	建立專用 USERS、DATA、INDEX 表空間	避免系統物件爭用
回滾段專用表空間	UNDO 表空間獨立	防止長交易擠佔空間
臨時表空間獨立	TEMP 表空間專供排序/Hash	減少永久表空間碎片
資料檔分散於多磁碟	同一表空間多檔案跨磁碟	I/O 負載均衡

3. 重做日誌 (Redo Log) 設計

檔案大小：控制 Checkpoint 頻率。過小 → 頻繁切換 → Checkpoint 風暴；過大 → 恢復時間長。建議切換間隔 15~30 分鐘。
Group 數量：Archive 模式下建議 4~6 組，減少歸檔壓力。
歸檔目的地：放置於最快儲存裝置 (SSD/NVMe)，啟用多路歸檔 (LOG_ARCHIVE_DEST_n)。

4. Undo (回滾段) 設計

段數量：依並發交易數估算，TRANSACTIONS / TRANSACTIONS_PER_ROLLBACK_SEGMENT (手動模式) 或啟用 UNDO_MANAGEMENT=AUTO。
段大小：容納最大交易的 Undo 量，避免 ORA-01555 Snapshot too old。

5. 記憶體結構 (SGA/PGA) 初始規劃

Shared Pool：SQL/PLSQL 緩存、Data Dictionary Cache。預留 20~30% 給保留區 (SHARED_POOL_RESERVED_SIZE)。
Buffer Cache：依資料庫大小與熱點資料比例，目標命中率 > 95%。
Redo Log Buffer：避免 log file sync 等待，通常 1~10MB 足夠。
Large Pool：MTS、RMAN、Parallel Query 使用。
PGA：排序、Hash Join、Bitmap 建立。PGA_AGGREGATE_TARGET (10g+) 自動管理。

6. SQL 撰寫規範 (開發規範)

綁定變數：所有條件值使用 :b1，啟用 Cursor Sharing，減少 Hard Parse。
明確列名：禁用 SELECT *，減少網路傳輸與 Sort 區記憶體。
索引欄位順序：複合索引 (A,B)，查詢需符合最左前綴原則。
避免索引失效寫法：
- 索引列用函數：UPPER(name)='ABC' → 改用 Function-Based Index 或 name LIKE 'abc%'
- 索引列運算：salary*12 > 100000 → 改寫 salary > 100000/12
- 隱含轉換：WHERE num_col = '123' → 字串轉數字導致索引失效
- IS NULL、NOT IN、!=、LIKE '%abc' 等

階段二：資料庫運行階段調校

1. 作業系統層面

減少 Swapping：vmstat 5, sar -p 10 10 監控。鎖定 SGA 記憶體 (LOCK_SGA=TRUE、HugePages)。
控制 Paging：安裝更多實體記憶體、調整核心參數、隔離大型工作負載。

2. SGA 分配與命中率監控

元件	關鍵指標	目標值	查詢 SQL
Library Cache	Get Hit Ratio	> 90%	`SELECT gethitratio FROM v$librarycache;`
	Reloads/Pins	< 1%	`SELECT SUM(reloads)/SUM(pins) FROM v$librarycache;`
Dictionary Cache	Getmisses/Gets	< 15%	`SELECT SUM(getmisses)/SUM(gets) FROM v$rowcache;`
Buffer Cache	Hit Ratio	> 95%	`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';`

3. 釘住熱門物件防止淘汰

EXEC DBMS_SHARED_POOL.KEEP('PACKAGE_NAME');
EXEC DBMS_SHARED_POOL.KEEP('PROCEDURE_NAME');
-- 查詢大型匿名區塊轉為 Procedure
SELECT * FROM v$sqlarea WHERE command_type=47 AND LENGTH(sql_text) > 500;

4. I/O 與資源爭用調優

避免動態擴展：預先分配足夠空間，關閉 AUTOEXTEND 或設定 MAXSIZE。
本地管理表空間 (LMT)：EXTENT MANAGEMENT LOCAL AUTOALLOCATE，減少 Data Dictionary 爭用。
鎖與 Latch 參數：DB_BLOCK_LRU_LATCHES, DML_LOCKS 依實際爭用調整。
SQL 監控與索引補強：V$SQL_MONITOR, DBA_ADVISOR_RECOMMENDATIONS (SQL Access Advisor)。