When are implicit cursors bulk-processed ?
When are they not bulk-processed ?
When they are bulk-processed, what is the bulk-size and what does it depend on ?
exec dbms_monitor.session_trace_enable
begin
for x in (select * from big_table where rownum <= 1e7) loop
if x.somenumbercolumn < 0 then null; end if;
end loop;
end;
/
tkprof shows me
SELECT *
FROM
BIG_TABLE WHERE ROWNUM <= 1E7
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 100001 103.39 104.21 82013 180411 0 10000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 100003 103.39 104.22 82013 180411 0 10000000
so, bulk-size approximately
100 = 10000000 / 100001 ( why 100001 fetches instead of 100000 ?)
create or replace type numbertable
as
table of number
/
create or replace function f(vuntil in int, vsleep in number) return numbertable pipelined is
i number := 1;
begin
loop
exit when i>vuntil;
pipe row(i); i:=i + 1;
dbms_lock.sleep(vsleep);
end loop;
return;
end f;
/
exec dbms_monitor.session_trace_enable
set timi on
declare
start_ number;
begin
start_ := dbms_utility.get_time;
for x in (
select *
from table(f(vuntil => 120, vsleep => 1 / 100))
) loop
dbms_output.put_line(
x.column_value || ': ' ||
(dbms_utility.get_time - start_)
);
end loop;
end;
/
gives the output
1: 111
2: 111
3: 111
4: 111
5: 111
6: 111
...
95: 111
96: 111
97: 111
98: 111
99: 111
100: 111
101: 134
102: 134
103: 134
...
117: 134
118: 134
119: 134
120: 134
So we also fetched 100 rows in the first fetch (sleeping a centisecond for each row, which gives around 1 second in total before the first row is processed), and 20 rows in the second fetch.
tkprof confirms that:
SELECT *
FROM
TABLE(F(VUNTIL => 120, VSLEEP => 1 / 100))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 30 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 1.33 0 0 0 120
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 1.33 0 30 0 120
So, is bulk size in implicit cursors always 100 ?
Implicit cursor for loops array fetch 100 rows at a time by default in 10g and above (in 9i they did not, it was single row).
It can be reduced to 1 by turning down the plsql optimization level during your compilation.
It was 100001 fetches because we needed a last fetch to find out "hey, there is no more data". Reduce your table by 1 row and it would be 100000 fetches since the last fetch of 100 rows would only find 99 and get the "no data found"
The number 100 is fixed and not alterable (except to downgrade it to 1 that is).
Implicit cursors in the form of "select bulk collect" use an array size of "infinity" - they do a single fetch call to retrieve all data. (so be careful, sometimes if you get what you ask for, you get something you didn't really want!)