從共享遊標shared cursor角度看forall批量繫結

 

在Oracle PL/SQL程式碼的編寫中，我們一直傾向於利用forall批量繫結來替代傳統的for迴圈語句。這樣做的目的在於提高PL/SQL程式碼的處理效果。

 

那麼，這麼做我們可以獲取到什麼好處呢？從官方文件上的陳述看，forall可以顯著地減少PL/SQL引擎和SQL引擎之間的切換頻率，節省這部分的成本。

 

當我們使用傳統的forall迴圈結構的時候，在迴圈體（loop…end loop）中，我們可以嵌入多條SQL或者PL/SQL程式碼。在Oracle PL/SQL引擎解析處理這部分內容時，每次迴圈for變數的時候，都會進入PL/SQL引擎和SQL引擎的上下文（Context）切換。所謂的上下文（Context）切換，最直觀的理解就是資料型別的轉換和傳輸。

 

但是，在forall迴圈過程中，事情有了很大的不同。Forall語句迴圈體中，只允許我們書寫一句SQL語句，而且對集合變數的使用存在一些限制（11g中取消）。但是，在使用forall語句的時候，PL/SQL和SQL引擎在切換的時候，只進行一次切換過程。也就是說，多條的Forall迴圈體SQL語句是一次性的傳送到SQL引擎進行處理的。

 

Forall批量繫結特定推出後，已經被普遍認為是一個進行PL/SQL程式碼語句優化的策略。那麼，兩種迴圈體結構，在遊標共享cursor sharing上有什麼差別呢？本篇著重從shared cursor角度進行研究分析。

 

1、試驗環境構建

 

我們選擇Oracle 10g環境進行試驗。

 

 

SQL> select * from v$version;

 

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE        10.2.0.1.0         Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

 

 

構建資料表T。

 

 

SQL> create table t as select * from dba_objects where 1=0;

Table created

 

 

為了保證記錄遊標資訊，我們構建兩張資料表，臨時儲存父子游標記錄。

 

 

SQL> create table sqlarea_str as select * from v$sqlarea where 1=0;

Table created

 

SQL> create table sql_str as select * from v$sql where 1=0;

Table created

 

 

下面，我們分別使用兩種迴圈語句，來進行判斷。

 

2、for迴圈體研究

 

我們書寫一個PL/SQL匿名塊，向資料表中插入記錄。

 

 

SQL> alter system flush buffer_cache;

系統已更改。

 

SQL> alter system flush shared_pool;

系統已更改。

 

 

SQL> declare

  2  type t_obj_ids is table of dba_objects.object_id%type index by binary_integer;

  3  type t_obj_names is table of dba_objects.object_name%type index by binary_integer;

  4  t_obj_idinfos t_obj_ids;

  5  t_obj_nameinfos t_obj_names;

  6  vc_sql varchar2(1000);

  7  begin

  8    select object_id, object_name

  9    bulk collect into t_obj_idinfos, t_obj_nameinfos

 10    from dba_objects;

 11 

 12    vc_sql := 'insert /*+ DEMO 1*/into t (object_id, object_name) values (:1, :2)';

 13 

 14    for i in t_obj_idinfos.first..t_obj_nameinfos.last loop

 15       execute immediate vc_sql

 16                         using t_obj_idinfos(i), t_obj_nameinfos(i);

 17    end loop;

 18    commit;

 19 

 20  end;

 21  /

 

PL/SQL procedure successfully completed

 

 

 

插入記錄之後，我們從v$sqlarea和v$sql中抽取特定的父子游標記錄。

 

 

SQL> select count(*) from v$sqlarea where sql_text like 'insert /*+ DEMO 1*/into t%';

 

  COUNT(*)

----------

         1

 

SQL> select count(*) from v$sql where sql_text like 'insert /*+ DEMO 1*/into t%';

 

  COUNT(*)

----------

         1

 

--儲存遊標

SQL> insert into sqlarea_str select * from v$sqlarea where sql_text like 'insert /*+ DEMO 1*/into t%';

1 row inserted

 

SQL> insert into sql_str select * from v$sql where sql_text like 'insert /*+ DEMO 1*/into t%';

1 row inserted

 

SQL> commit;

Commit complete

 

 

下面試驗forall模式。

 

3、forall迴圈體研究

 

相同的資料表，相同的資料量，類似的匿名塊構成。

 

 

SQL> truncate table t;

Table truncated

 

SQL> alter system flush buffer_cache;

系統已更改。

 

SQL> alter system flush shared_pool;

系統已更改。

 

 

SQL> declare

  2  type t_obj_ids is table of dba_objects.object_id%type index by binary_integer;

  3  type t_obj_names is table of dba_objects.object_name%type index by binary_integer;

  4  t_obj_idinfos t_obj_ids;

  5  t_obj_nameinfos t_obj_names;

  6  vc_sql varchar2(1000);

  7  begin

  8    select object_id, object_name

  9    bulk collect into t_obj_idinfos, t_obj_nameinfos

 10    from dba_objects;

 11 

 12    vc_sql := 'insert /*+ DEMO 2*/into t (object_id, object_name) values (:1, :2)';

 13 

 14    forall i in t_obj_idinfos.first..t_obj_nameinfos.last

 15       execute immediate vc_sql

 16                         using t_obj_idinfos(i), t_obj_nameinfos(i);

 17 

 18    commit;

 19 

 20  end;

 21  /

 

PL/SQL procedure successfully completed

 

 

定位父子游標記錄。

 

 

SQL> select count(*) from v$sqlarea where sql_text like 'insert /*+ DEMO 2*/into t%';

 

  COUNT(*)

----------

         1

 

SQL> select count(*) from v$sql where sql_text like 'insert /*+ DEMO 2*/into t%';

 

  COUNT(*)

----------

         1

 

SQL> insert into sqlarea_str select * from v$sqlarea where sql_text like 'insert /*+ DEMO 2*/into t%';

1 row inserted

 

SQL> insert into sql_str select * from v$sql where sql_text like 'insert /*+ DEMO 2*/into t%';

1 row inserted

 

SQL> commit;

Commit complete

 

 

4、遊標分析

 

從儲存的記錄中，我們可以比較方便的進行分析父子共享遊標的情況。

 

父遊標共享對比：

 

 

SQL> col sql_text for a20;

 

SQL> select sql_text, sql_id from sql_str;

 

SQL_TEXT                                                     SQL_ID

------------------------------------------------------------ -------------

insert /*+ DEMO 1*/into t (object_id, object_name) values (: 8cg3g8kwctcuk

1, :2)                                                      

 

insert /*+ DEMO 2*/into t (object_id, object_name) values (: 9z2a0hzqs5jws

1, :2)       

 

 

兩個實驗語句對應不同的sql_id，各種差異指標如下：

 

 

 

SQL> select sql_id, executions, END_OF_FETCH_COUNT, PARSE_CALLS, DISK_READS, BUFFER_GETS, USER_IO_WAIT_TIME, CPU_TIME, ELAPSED_TIME  from sqlarea_str;

 

SQL_ID        EXECUTIONS END_OF_FETCH_COUNT PARSE_CALLS DISK_READS BUFFER_GETS USER_IO_WAIT_TIME   CPU_TIME ELAPSED_TIME

------------- ---------- ------------------ ----------- ---------- ----------- ----------------- ---------- ------------

8cg3g8kwctcuk      50432              50432           1         28       56561            220007     563580       782689

9z2a0hzqs5jws          1                  1           1          2        3669             11499      31462        56070

 

 

由於篇幅原因，筆者整理好特定表格進行展現：

 

#	指標	DEMO-1 (sql_id=8cg3g8kwctcuk)	DEMO-2 (sql_id=9z2a0hzqs5jws)
	Executions	50432	1
	END_OF_FETCH_COUNT	50432	1
	PARSE_CALLS	1	1
	DISK_READS	28	2
	BUFFER_GETS	56561	3669
	USER_IO_WAIT_TIME	220007	11499
	CPU_TIME	563580	31462
	ELAPSED_TIME	782689	56070

 

從上面的圖示中，我們可以看到父遊標指標的差異。由於子游標內容和父遊標相同，筆者就不單獨進行闡述了。

 

在上面對比中，我們重點關注幾個指標，可以體現出Forall批量繫結的特點。

 

ü  Executions數值：兩個例子中，均是逐條插入了記錄，且記錄條數相同。但是，for語句迴圈體表示執行次數是50423，而forall只有1次。這個是一個非常大的差異。這個讓我們可以看出，Oracle在處理forall bulk insert的時候，是採用了一次性將所有數值一次性執行插入的過程。而不是簡單的在SQL引擎中逐條執行；

ü  遊標生命週期fetch次數差異，Fetch次數上，forall要明顯小於forall；

ü  其他各種響應時間、讀取資料量的差異。在處理時間和處理資料量上，forall也要明顯好於for迴圈；

 

5、結論

 

在實際工作中，我們要注意由於使用forall而帶來的效能問題“盲點”。在shared cursor機制中，我們是不能夠看到哪個語句是採用forall的。那麼，當我們使用自定義效能分析指令碼或者AWR報告分析時，可能就會發現一條執行一次（Execution=1）的語句，消耗了很多的資源和時間。但是當我們自己執行這個語句時候，又發現不了什麼問題。此時，我們可能會想到鎖表等等。其實，forall批量繫結的使用，也許是主要原因。