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

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
********************************************************************************

SELECT ROWID
FROM
ROBO WHERE OWNER='TEST'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4993 0.70 0.73 60078 69059 0 4992
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4995 0.70 0.73 60078 69059 0 4992

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

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 3.13 3.13
db file sequential read 1 0.00 0.00
db file scattered read 3784 0.00 0.27

性能数据:
-----------------

delete from robo
where
rowid=:1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 4993 0.33 0.31 1 4993 5280 4992
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4994 0.33 0.31 1 4993 5280 4992

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

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

全表扫描次数为一次，但游标提取和删除次数等于结果集数目+1次。执行效率比普通删除来得低

?

2）假设待删除数据量小:

1. 普通删除方法

SQL> delete from robo where owner='SYS';

1909760 rows deleted.

Elapsed: 00:01:20.23

?

性能数据:

-------------
delete from robo
where
owner='SYS'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 31 87 0 0
Execute 1 17.52 78.34 27888 60322 2123625 1909760
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 17.53 78.34 27919 60409 2123625 1909760

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

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2998 0.00 0.02
db file scattered read 2318 0.00 0.07
log file switch completion 17 0.97 14.43
log file switch (checkpoint incomplete) 84 0.97 45.38
log buffer space 4 0.19 0.29
SQL*Net message to client 1 0.00 0.00
********************************************************************************

主要耗时是等待在线日志的切换，过程当中产生大量在线日志，执行效率大。

?

2. 游标处理(每交提取一条)