詳解ORACLE中游標的生命周期

 在網絡或者書籍中，我們可以非常容易的了解到ORACLE中游標的生命周期包括如下部分：

1，打開游標-- open cursor，此步驟在 UGA 裡申請一塊內存給游標使用，這個時候游標還沒有與sql語句關聯。
2，解析游標-- sql與游標關聯起來，解析sql的內容（包括執行計劃），解析後的內容會被加載到共享池中（share pool-- library cache）。在UGA申請的內存用來保存指向這個共享游標（share cursor）在library cache中的位置。
3，定義輸出變量-- 如果sql語句返回數據，必須先定義接收數據的變量。這一點不僅對查詢語句很重要，對於使用returning 自居的delete、insert和update 語句也很重要。
4，綁定輸入變量-- 如果sql語句使用了綁定變量，必須提供他們的值。綁定的過程是不做什麼檢查。如果指定了無效的數據，執行的過程中會爆出一個運行時錯誤。
5，執行游標-- 執行跟游標關聯的sql。注意 數據庫並非總是在這一步做重要的事情。事實上，對於很多類型的查詢語句來說，真正的處理過程通常會被推遲到fetch數據階段。
6，獲取游標-- 如果sql語句返回數據，這一步會接受這些數據。特別是在查詢語句中，大部分的處理工作都是在這一步進行的。在查詢語句中，可能只會讀取部分記錄，換句話講，游標有可能在取到所有記錄前被關閉。
7，關閉游標-- 釋放UGA中與這個游標有關的資源，從而這些資源可供其他的游標使用。在library cache中的share cursor不會被清除，它會繼續保留在library cache 中，等待被重用（軟解析重用）。

重復的內容，我們不做過多介紹，今天我們來看一下游標生命周期中各個部分所扮演的角色，以及如何利用它們來優化我們的程序。

借鑒《oracle性能診斷藝術》中的代碼片段，我們來研究一下游標的生命周期；

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 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;     res :='';     for j in 1 .. l_empno.count() loop         res := res || L_EMPNO(j);      end loop;      DBMS_OUTPUT.PUT_LINE(res);     dbms_sql.close_cursor(l_cursor);   end loop; end; 

通過plsql profiler跟蹤各行代碼的執行效率如下:

可以看出,游標生命周期中的各個部分均會占有執行時間,因此,如果可以消除一些執行步驟顯然會提高性能. 那些操作可以消除那?參加下圖

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 --                      -----------   --                    | open_cursor |   --                      -----------   --                           |   --                           |   --                           v   --                         -----   --          ------------>| parse |   --         |               -----   --         |                 |   --         |                 |---------   --         |                 v         |   --         |           --------------  |   --         |-------->| bind_variable | |   --         |     ^     -------------   |   --         |     |           |         |   --         |      -----------|         |   --         |                 |<--------   --         |                 v   --         |               query?---------- yes ---------   --         |                 |                           |   --         |                no                           |   --         |                 |                           |   --         |                 v                           v   --         |              -------                  -------------   --         |----------->| execute |            ->| define_column |   --         |              -------             |    -------------   --         |                 |------------    |          |   --         |                 |            |    ----------|   --         |                 v            |              v   --         |           --------------     |           -------   --         |       ->| variable_value |   |  ------>| execute |   --         |      |    --------------     | |         -------   --         |      |          |            | |            |   --         |       ----------|            | |            |   --         |                 |            | |            v   --         |                 |            | |        ----------   --         |                 |<-----------  |----->| fetch_rows |   --         |                 |              |        ----------   --         |                 |              |            |   --         |                 |              |            v   --         |                 |              |    --------------------   --         |                 |              |  | column_value         |   --         |                 |              |  | variable_value       |   --         |                 |              |    ---------------------   --         |                 |              |            |   --         |                 |<--------------------------   --         |                 |   --          -----------------|   --                           |   --                           v   --                      ------------   --                    | close_cursor |   --                      ------------   --

首先,將游標的打開和關閉修改為只執行一次,效果如下

再來將解析消除,首先清空共享池,看看游標解析是否消耗大量資源

可以看出,解析還是消耗資源的,尤其是硬解析時,同時也可以看到open_cursor消耗的時間劇增,因此不到萬不得已,千萬不要清空共享池.如果要消除解析,那麼每次執行的語句必須是一致的,可以通過綁帶變量的方式來實現,修改程序代碼如下

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 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   l_cursor := DBMS_SQL.open_cursor;   DBMS_SQL.parse(l_cursor,                    'select empno from emp where ename <> :ename' , DBMS_SQL.native);   for i in 1 .. 1000 loop     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;     res :='';     for j in 1 .. l_empno.count() loop         res := res || L_EMPNO(j);      end loop;      DBMS_OUTPUT.PUT_LINE(res);   end loop;    dbms_sql.close_cursor(l_cursor); end;

執行效率如下:

下面我們來優化最耗時的部分,通過增加cnt的取值,(類似於SQLPLUS中的set arraysize )

效率有提高了很多.

總結:理解游標的執行過程,盡可能多的重復各個子環節可以有效提高效率.