以前研究过INSERT和UPDATE的FORALL语句,这里看一下DELETE语句的执行情况。
FOR ALL语法浅析:http://yangtingkun.itpub.net/post/468/198828
FORALL执行UPDATE语句:http://yangtingkun.itpub.net/post/468/518933
首先建立一个测试表:
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:oraclediagrdbmstest112test112trace
E:oraclediagrdbmstest112test112trace>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语句,批量绑定数组,而是的执行次数大大减少,减小了系统维护的开销,也是的操作的效率更高。