How many rows does PL/SQL array fetch by default?

I was curious as to how many rows PL/SQL will fetch by default without a BULK COLLECT clause. For those of you that may not know, fetching multiple rows at a time can substantially reduce the run times of programs that deal with a large number of rows.

We set up the obligatory test table…

SQL> create table t0522 as select rownum r from dba_objects;

Table created.

…then enable trace on our session…

SQL> exec dbms_monitor.session_trace_enable(binds=>true,waits=>true);

PL/SQL procedure successfully completed.

…and then retrieve all rows…

SQL> begin
  2    for cur in (select * from t0522) loop
  3      null;
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>

…and after we run our trace file through tkprof, we see that we fetch 100 rows at a time by default. We can determine this by dividing the total number of rows we fetched, 51911, by the number of fetches it took to retrieve them, 520.

SELECT *
FROM
T0522

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.03          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      520      0.30       0.31          5        603          0       51911
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      522      0.32       0.35          5        604          0       51911

As such, if you are using BULK COLLECT in your code, it really isn’t benefiting you any more than a regular cursor loop unless you increase your array size to something (substantially) more than 100.