Oracle 游標生命週期七階段深度剖析與效能優化實戰

游標生命週期包含 7 個階段，每階段皆消耗 CPU/記憶體資源。理解各階段角色，可針對性消除冗餘操作，大幅提升 PL/SQL 效能。

七階段標準流程

1. OPEN_CURSOR：在 UGA 分配記憶體，尚未關聯 SQL。
2. PARSE：SQL 文本解析、語義檢查、最佳化產生執行計畫，載入 Library Cache (Shared Cursor)。UGA 存指標指向 Shared Cursor。
3. DEFINE_COLUMN：定義查詢回傳欄位的接收變數/陣列。
4. BIND_VARIABLE：綁定輸入變數值 (不做型別檢查，執行期才報錯)。
5. EXECUTE：執行 SQL。對查詢語句，真正資料存取常延遲至 FETCH 階段。
6. FETCH_ROWS：取回資料。查詢語句大部分工作 (邏輯讀、排序、過濾) 發生在這裡。可僅取部分列後關閉。
7. CLOSE_CURSOR：釋放 UGA 資源。Shared Cursor 保留在 Library Cache 供軟解析重用。

效能優化三部曲 (以 DBMS_SQL 為例)

步驟 1：提升游標到迴圈外 (消除重複 OPEN/PARSE/CLOSE)

-- 原寫法：迴圈內 OPEN/PARSE/CLOSE
FOR i IN 1..1000 LOOP
  l_cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(...);
  ...
  DBMS_SQL.CLOSE_CURSOR(l_cursor);
END LOOP;

-- 優化：移出迴圈
l_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(l_cursor, 'SELECT ... WHERE ename != :b1', DBMS_SQL.NATIVE);
FOR i IN 1..1000 LOOP
  DBMS_SQL.BIND_VARIABLE(l_cursor, ':b1', TO_CHAR(i));
  l_retval := DBMS_SQL.EXECUTE(l_cursor);
  WHILE DBMS_SQL.FETCH_ROWS(l_cursor) > 0 LOOP ... END LOOP;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(l_cursor);

效果：OPEN_CURSOR 耗時歸零，PARSE 耗時大幅下降。

步驟 2：使用繫結變數消除硬解析

SQL 文本固定，僅綁定變數值改變 → 軟解析，Library Cache 直接重用 Shared Cursor。若動態拼接 SQL 字串 ('... != ' || i) 則每次硬解析，極度消耗 Shared Pool Latch。

步驟 3：調大陣列提取大小 (對應 ARRAYSIZE)

DBMS_SQL.DEFINE_ARRAY(..., cnt => 100, ...) 一次取 100 列，減少 FETCH 呼叫次數與網路往返。實測 cnt=1 → cnt=100 總耗時再降 30%+。

總結

• 重用游標：OPEN/PARSE/CLOSE 移出迴圈。
• 繫結變數：固定 SQL 文本，避免硬解析。
• 批量提取：調大 DEFINE_ARRAY count / BULK COLLECT LIMIT。
• 定期監控 V$SQLAREA.PARSE_CALLS vs EXECUTIONS，比值接近 1 表示硬解析過多。