If I SET ARRAYSIZE 1 in SQL*Plus, the records are still fetched in pairs. Why

oraclesqlplus

I thought about writing a simple tail -f like utility to "trace" the progress of some figures within the database:

create or replace function tail_f return varchar2_tab pipelined as
   n number;
begin

    loop
      exit when ...

      select count(*) into n from ... where ...;

      pipe row(sysdate || ' n= ' || n);
      dbms_lock.sleep(60);
    end loop;

    return;
end tail_f;

And then I'd like to select * from table(tail_f) in SQL*Plus.

In order to fetch the rows one by one, I SET ARRAYSIZE 1. Yet, the records (except the first one) are fetched in pairs.

Is there an explanation for this and how can I get the records as soon as one is piped?

Best Answer

Metalink bug 9103343 states:

This is expected behaviour. SQL*Plus is written in oci and oci has a default prefetch value of 1 row. However prefetch upon a fetch (as opposed to upon an execute) only takes place when you are not performing an array fetch, so when arraysize is 1. Regardless of arraysize the first fetch in the trace is always 1 row as 1 row is prefetched on the execute. Then it either performs a scalar fetch, so one requested row plus one prefetched row, or it performs an array fetch so you see eg : a) arraysize = 1, fetches are: 1, 2, 2, ... b) arraysize = 2, fetches are: 1, 2, 2, ... c) arraysize = 5, fetches are: 1, 5, 5, ...

Also see Metalink doc 1265916.1

Related Question