Oracle SQL 語句優化核心原則與實務技巧

共用池與 SQL 共享

Oracle 透過 Shared Pool 快取已剖析的 SQL 游標。只有 文字完全相同（大小寫、空白、綁定變數名稱、權限、路徑）的 SQL 才能共享執行計畫。

強制共享原則

• 統一 SQL 大小寫與格式規範。
• 使用綁定變數（Bind Variables）取代常值。
• 設定 CURSOR_SHARING = FORCE/SIMILAR（需評估副作用）。

Shared Pool 記憶體管理

• SHARED_POOL_RESERVED_SIZE：保留 10% SHARED_POOL_SIZE 供大型連續記憶體請求，減少碎片。
• DBMS_SHARED_POOL.KEEP：將熱門 PL/SQL 套件、觸發器、游標鎖定在共用池，防止被淘汰。

  EXEC DBMS_SHARED_POOL.KEEP('SCHEMA.PACKAGE_NAME', 'P'); -- P=Procedure, C=Cursor, R=Trigger, Q=Sequence

索引特性與使用時機

• 由 Oracle 自動維護，獨立於資料表儲存。
• 僅查詢被索引欄位時才會被使用。
• 以 B*Tree 結構取代全表掃描，減少 I/O。
• 可指定 PCTFREE、TABLESPACE 等儲存參數。

優化器模式與統計資訊

收集統計資訊：

ANALYZE TABLE emp COMPUTE STATISTICS;
ANALYZE TABLE emp ESTIMATE STATISTICS SAMPLE 20 PERCENT;
EXEC DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT', 'ESTIMATE', 20);

SQL 追蹤與 TKPROF 分析流程

1. 啟動 SQL_TRACE = TRUE 或 ALTER SESSION SET SQL_TRACE = TRUE。
2. 執行目標程式。
3. 在 USER_DUMP_DEST 找到追蹤檔（.trc）。
4. 執行 tkprof trace.trc output.txt EXPLAIN=user/pass SYS=NO 產生可讀報表。
5. 依報表中 CPU、ELAPSED、ROWS、MISSES 等指標優化 SQL。

執行計畫與 AUTOTRACE

• 建立 PLAN_TABLE：@?/rdbms/admin/utlxplan.sql。
• 使用 EXPLAIN PLAN FOR ... 或 SET AUTOTRACE ON EXPLAIN STATISTICS 即時查看計畫與統計。