萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

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

一、游標生命週期七階段

  1. OPEN:在 UGA 分配記憶體,尚未關聯 SQL。
  2. PARSE:SQL 與游標關聯,語法/語義檢查、最佳化、載入 Library Cache。UGA 存指向 Shared Cursor 的指標。
  3. DEFINE:定義輸出變數(查詢/RETURNING 子句必須)。
  4. BIND:提供輸入綁定變數值(不做型別檢查,執行期報錯)。
  5. EXECUTE:執行 SQL。查詢類實際處理常延遲至 FETCH。
  6. FETCH:取回資料。查詢大部分工作在這階段。可提前關閉游標。
  7. 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_CURSOR0.122%游標重用
PARSE2.4545%**消除硬解析**
BIND_VARIABLE0.305%
EXECUTE0.8015%
FETCH_ROWS1.2022%陣列抓取
CLOSE_CURSOR0.6011%游標重用

三、優化策略:消除 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 ...)。
copyright © 萬盛學電腦網 all rights reserved