一、游標生命週期七階段
- OPEN:在 UGA 分配記憶體,尚未關聯 SQL。
- PARSE:SQL 與游標關聯,語法/語義檢查、最佳化、載入 Library Cache。UGA 存指向 Shared Cursor 的指標。
- DEFINE:定義輸出變數(查詢/RETURNING 子句必須)。
- BIND:提供輸入綁定變數值(不做型別檢查,執行期報錯)。
- EXECUTE:執行 SQL。查詢類實際處理常延遲至 FETCH。
- FETCH:取回資料。查詢大部分工作在這階段。可提前關閉游標。
- CLOSE:釋放 UGA 資源,Shared Cursor 留在 Library Cache 供下次軟解析重用。
二、DBMS_SQL 動態 SQL 效能剖析實測
CREATE OR REPLACE PROCEDURE cursor_test AS
l_ename emp.ename%TYPE := 'SCOTT';
l_empno DBMS_SQL.NUMBER_TABLE;
l_cursor INTEGER; l_retval INTEGER;
cnt INTEGER := 1; indx INTEGER := 1; res VARCHAR2(4000);
BEGIN
FOR i IN 1..1000 LOOP
l_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(l_cursor,
'SELECT empno FROM emp WHERE ename <> :ename AND 0 <> '||i, DBMS_SQL.NATIVE);
l_empno.DELETE;
DBMS_SQL.DEFINE_ARRAY(l_cursor, 1, l_empno, cnt, indx);
DBMS_SQL.BIND_VARIABLE(l_cursor, ':ename', TO_CHAR(i));
l_retval := DBMS_SQL.EXECUTE(l_cursor);
WHILE DBMS_SQL.FETCH_ROWS(l_cursor) > 0 LOOP
DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_empno);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(l_cursor);
END LOOP;
END;
Profiler 統計 (關鍵步驟耗時佔比)
| 步驟 | 總耗時(秒) | 佔比 | 優化方向 |
| OPEN_CURSOR | 0.12 | 2% | 游標重用 |
| PARSE | 2.45 | 45% | **消除硬解析** |
| BIND_VARIABLE | 0.30 | 5% | |
| EXECUTE | 0.80 | 15% | |
| FETCH_ROWS | 1.20 | 22% | 陣列抓取 |
| CLOSE_CURSOR | 0.60 | 11% | 游標重用 |
三、優化策略:消除 PARSE/OPEN/CLOSE 開銷
-- 方案 1:游標變數重用 (PL/SQL 靜態游標最佳)
CURSOR c_data(p_ename VARCHAR2) IS
SELECT empno FROM emp WHERE ename <> p_ename;
-- 方案 2:DBMS_SQL 重用游標句柄
l_cursor := DBMS_SQL.OPEN_CURSOR;
FOR i IN 1..1000 LOOP
DBMS_SQL.PARSE(l_cursor, 'SELECT empno FROM emp WHERE ename <> :b1 AND 0 <> :b2', DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(l_cursor, ':b1', 'SCOTT');
DBMS_SQL.BIND_VARIABLE(l_cursor, ':b2', i);
l_retval := DBMS_SQL.EXECUTE(l_cursor);
-- FETCH...
END LOOP;
DBMS_SQL.CLOSE_CURSOR(l_cursor); -- 僅 OPEN/CLOSE 一次
四、關鍵優化原則
- 靜態 SQL 優於動態 SQL:編譯期解析,零執行期 PARSE 開銷。
- 綁定變數:實現軟解析重用 Shared Cursor。
- 陣列抓取:
DBMS_SQL.DEFINE_ARRAY / BULK COLLECT 減少網路往返。
- 游標快取:
SESSION_CACHED_CURSORS、PLSQL_OPTIMIZE_LEVEL 自動快取熱點游標。
- 避免迴圈內 OPEN/PARSE/CLOSE:移至迴圈外,或改用隱性游標
FOR rec IN (SELECT ...)。