在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批量绑定的使用，也许是主要原因。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-742975/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-742975/
142
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
