Oracle SQL 調優核心四大法則：消除全表掃描、優化索引、最佳連接與排序調優

SQL 調優黃金目標：四大核心法則

1. 消除不必要的大表全表掃描
2. 小表全表掃描納入 KEEP 池快取
3. 確保最優索引使用
4. 確保最優連接方法

這四大法則覆蓋 90% 的 SQL 調優工作量，且無需精通內核細節。

法則一：消除不必要的大表全表掃描

判斷標準

• 有序表：查詢回傳 < 40% 行數 → 應用索引
• 無序表：查詢回傳 < 7% 行數 → 應用索引

手段

• 新增標準 B-Tree 索引、Bitmap 索引、Function-based 索引
• 對比 Index Range Scan + Table Access by Rowid vs Full Table Scan 的 I/O 成本
• 必要時用 /*+ INDEX(t idx_name) */ Hint 強制走索引

法則二：小表全表掃描快取策略

• Oracle 7：ALTER TABLE small_table CACHE
• Oracle 8+：將小表放入 DB_KEEP_CACHE_SIZE（KEEP 池）
• 確保專用資料緩衝區作為行快取，避免頻繁物理讀

法則三：最優索引選擇

• 多索引可選時，檢查各索引選擇性、聚簇因子、欄位順序
• Bitmap 索引適合低基數欄位（性別、狀態）且多條件 AND/OR 組合
• Function-based 索引解決 WHERE UPPER(name) = 'SMITH' 無法用索引問題
• 定期 ANALYZE INDEX ... VALIDATE STRUCTURE 檢查高度、葉塊、聚簇因子

法則四：最優連接方法

排序操作調優專題

觸發排序的 SQL 構造

• ORDER BY、GROUP BY、DISTINCT
• CREATE INDEX、UNION/INTERSECT/MINUS
• 連接時索引不足導致 Optimizer 選擇 Merge Sort

排序記憶體架構

• 專用連接：SORT_AREA_SIZE 於 PGA 分配
• 共用伺服器：於 LARGE_POOL 分配
• 所有 Session 共用同一 SORT_AREA_SIZE，無法單獨放大

磁碟排序災難：慢 14,000 倍

• 超出 SORT_AREA_SIZE → 溢寫 TEMP 表空間
• 消耗臨時段、佔用緩衝區塊、引發 free buffer waits
• 監控 V$SYSSTAT 中 sorts (disk) vs sorts (memory)

調優對策

1. 適度調大 SORT_AREA_SIZE（權衡記憶體總量）
2. 利用 SORT_AREA_RETAINED_SIZE 保留排序記憶體
3. 避免不必要的 ORDER BY（應用層排序）
4. 大量排序作業考慮 PARALLEL 排序分散負載
5. 啟用 WORKAREA_SIZE_POLICY = AUTO（9i+）讓 Oracle 自動管理 PGA 排序區

實戰調優流程

1. 收集執行計劃：EXPLAIN PLAN / DBMS_XPLAN.DISPLAY
2. 識別 Full Table Scan、Cartesian Product、高 Cost 操作
3. 檢查統計資訊新鮮度：DBMS_STATS.GATHER_TABLE_STATS
4. 嘗試 Hint/改寫 SQL/新增索引/調整參數
5. 驗證邏輯讀、物理讀、執行時間改善

SQL 調優是「數據驅動的實驗科學」，建立基線、單一變數變更、量化對比，才是通往最優執行計劃的正道。

1. 上一篇文章： Oracle 專家調優秘笈：深度解析 Freelist、PCTFREE/PCTUSED 與空間管理競爭優化
2. 下一篇文章： Oracle Row Reséquencing（行重排序）技術：透過 CTAS 重組表消除磁碟 I/O 瓶頸

Oracle 資料庫基礎

Oracle ALTER TABLE 完整操作指南：重命名、增刪欄位、修改型別、約束管理與最佳實務

基本語法結構 ALTER TABLE table_name

OpenServer 5.0.7 安裝 Oracle 7.3.4 完整步驟（含核心參數與 rootpre.sh）

1. 調整 SCO 核心參數 # /etc/conf/bin

Windows 7 開啟 Fireworks 8 導致 Aero 關閉的修復方法

在 Windows 7 環境下啟動 Adobe Firewo

Oracle 多表關聯更新多欄位：標準語法與條件下推實戰

核心語法：SET 子句使用子查詢元組賦值 UPDATE st