Oracle SQL 高效撰寫三大支柱：優化器選擇、存取路徑與共享游標

SQL 效能好壞，三成在寫法，七成在架構。掌握優化器模式、表存取路徑、共享池機制，寫出高效 SQL 將事半功倍。

一、選對優化器模式：拒絕 CHOOSE

最佳實務：ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS; 或實例級鎖定 CBO，並建立 DBMS_STATS 定時收集作業。

二、表存取兩大路徑

1. Full Table Scan (全表掃描)：循序讀取所有塊，多塊一次 I/O (DB_FILE_MULTIBLOCK_READ_COUNT)。適用：小表、大比例存取、無合適索引。
2. ROWID Access (ROWID 存取)：透過索引取得 ROWID → 直接定位塊/行。適用：高選擇性查詢、索引覆蓋查詢。
   索引建立在 WHERE/JOIN/ORDER BY 常用欄位上。

三、共享游標機制與寫法規範

• 首次解析後，SQL 文本、解析樹、執行計畫存入 Library Cache (Shared Pool)。
• 後續相同 SQL (含大小寫、空白、換行完全一致) 直接重用，省去解析開銷。
• 限制：僅支援單表查詢快取；多表連接不快取 (舊版)。
• 關鍵參數：SHARED_POOL_SIZE 決定快取容量；越大重用機率越高。

四、強制共用最佳實務

1. 所有 SQL 使用繫結變數 (Bind Variables)，避免字面常數導致硬解析暴增。
2. 統一 SQL 格式化風格 (大寫關鍵字、固定縮排)。
3. 應用端啟用 SESSION_CACHED_CURSORS / CURSOR_SHARING=FORCE/SIMILAR (謹慎評估副作用)。
4. 熱門 PL/SQL 套件用 DBMS_SHARED_POOL.KEEP 釘住記憶體。