首先建立一个测试表：

SQL> EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(TRUE, TRUE);

PL/SQL 过程已成功完成。

SQL> DECLARE
2 TYPE T_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 V_ID T_ID;
4 BEGIN
5 SELECT ID
6 BULK COLLECT INTO V_ID
7 FROM T
8 WHERE ROWNUM < 100;
9 FORALL I IN V_ID.FIRST..V_ID.LAST
10 DELETE T WHERE ID = V_ID(I);
11 END;
12 /

PL/SQL 过程已成功完成。

SQL> EXEC DBMS_SESSION.SESSION_TRACE_DISABLE;

PL/SQL 过程已成功完成。

运行FORALL删除后，找到当前会话的trace文件：

SQL> SELECT SPID
2 FROM V$SESSION S, V$PROCESS P
3 WHERE S.PADDR = P.ADDR
4 AND SID = USERENV('SID');

SPID
------------------------
2956

由于详细TRACE格式太长，不易阅读，这里利用TKPROF来分析trace是最佳选择：

E:\>cd E:\oracle\diag\rdbms\test112\test112\trace

E:\oracle\diag\rdbms\test112\test112\trace>tkprof test112_ora_2956 output.txt

TKPROF: Release 11.2.0.1.0 - Development on 星期五 6月 24 08:50:59 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

对于格式化的trace，我们只取有关的内容：

TKPROF: Release 11.2.0.1.0 - Development on 星期五 6月 24 08:50:59 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Trace file: test112_ora_2956.trc
Sort options: default

*******************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
*******************************************************************************

SQL ID: 2xyvdw1vnhac8
Plan Hash: 0
BEGIN DBMS_SESSION.SESSION_TRACE_ENABLE(TRUE, TRUE); END;

call    count       cpu    elapsed       disk      query    current       rows
------- -----  -------- ---------- ---------- ---------- ----------  ---------
Parse       0      0.00       0.00          0          0          0          0
Execute     1      0.00       0.01          0          0          0          1
Fetch       0      0.00       0.00          0          0          0          0
------- ------  -------- ---------- ---------- ---------- ----------  ---------
total       1      0.00       0.01          0          0          0          1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       21.45         21.45
*******************************************************************************

DECLARE
 TYPE T_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 V_ID T_ID;
BEGIN
 SELECT ID
 BULK COLLECT INTO V_ID
 FROM T
 WHERE ROWNUM < 100;
 FORALL I IN V_ID.FIRST..V_ID.LAST
  DELETE T WHERE ID = V_ID(I);
END;

call    count       cpu    elapsed       disk      query    current       rows
------- -----  -------- ---------- ---------- ---------- ----------  ---------
Parse       1      0.00       0.01          0          0          0          0
Execute     1      0.00       0.00          0          0          0          1
Fetch       0      0.00       0.00          0          0          0          0
------- -----  -------- ---------- ---------- ---------- ----------  ---------
total       2      0.00       0.01          0          0          0          1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       13.51         13.51
*******************************************************************************

.
.
.
*******************************************************************************

SQL ID: avyzsyqmd74r4
Plan Hash: 508354683
SELECT ID
FROM
 T WHERE ROWNUM < 100

call    count       cpu    elapsed       disk      query    current       rows
------- -----  -------- ---------- ---------- ---------- ----------  ---------
Parse       1      0.00       0.00          0          2          0          0
Execute     1      0.00       0.00          0          0          0          0
Fetch       1      0.00       0.00          0          4          0         99
------- -----  -------- ---------- ---------- ---------- ----------  ---------
total       3      0.00       0.00          0          6          0         99

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
     99  COUNT STOPKEY (cr=4 pr=0 pw=0 time=294 us)
     99   TABLE ACCESS FULL T (cr=4 pr=0 pw=0 time=98 us cost=2 size=757874 card=58298)

*******************************************************************************

SQL ID: 998sf5pg15v3q
Plan Hash: 3335594643
DELETE T
WHERE
 ID = :B1

call    count       cpu    elapsed       disk      query    current       rows
------- -----  -------- ---------- ---------- ---------- ----------  ---------
Parse       1      0.00       0.00          0          0          0          0
Execute     1      0.21       0.47         19      13465        101         99
Fetch       0      0.00       0.00          0          0          0          0
------- -----  -------- ---------- ---------- ---------- ----------  ---------
total       2      0.21       0.47         19      13465        101         99

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  T (cr=13464 pr=19 pw=0 time=0 us)
     99   TABLE ACCESS FULL T (cr=13464 pr=19 pw=0 time=0 us cost=54 size=26 card=2)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         6        0.00          0.00
  db file scattered read                          5        0.00          0.00
*******************************************************************************

.
.
.
*******************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call    count       cpu    elapsed       disk      query    current       rows
------- -----  -------- ---------- ---------- ---------- ----------  ---------
Parse       2      0.00       0.01          0          0          0          0
Execute     3      0.00       0.02          0          0          0          3
Fetch       0      0.00       0.00          0          0          0          0
------- -----  -------- ---------- ---------- ---------- ----------  ---------
total       5      0.00       0.04          0          0          0          3

Misses in library cache during parse: 2

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2       21.45         34.97
.
.
.
*******************************************************************************
Trace file: test112_ora_2956.trc
Trace file compatibility: 11.1.0.7
Sort options: default

       1  session in tracefile.
       8  user  SQL statements in trace file.
       7  internal SQL statements in trace file.
      15  SQL statements in trace file.
      13  unique SQL statements in trace file.
     982  lines in trace file.
      35  elapsed seconds in trace file.

可以明显的看到，对于DELETE语句，仅仅执行了一次，而处理的行数是99行，同样，前面的SELECT BULK COLLECT INTO语句也仅FETCH了一次，而处理了99行。

对于FORALL语句，批量绑定数组，而是的执行次数大大减少，减小了系统维护的开销，也是的操作的效率更高。