FORALL执行DELETE语句 [转帖]_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 4296 | 回复: 0   主题: FORALL执行DELETE语句 [转帖]        下一篇 
wayne
注册用户
等级:中校
经验:1690
发帖:221
精华:0
注册:2011-7-21
状态:离线
发送短消息息给wayne 加好友    发送短消息息给wayne 发消息
发表于: IP:您无权察看 2011-8-3 14:50:56 | [全部帖] [楼主帖] 楼主

以前研究过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语句,批量绑定数组,而是的执行次数大大减少,减小了系统维护的开销,也是的操作的效率更高。




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论