詳解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 )

效率有提高了很多.

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