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

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 SQLPlus.

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?

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

StackExchange Database Administrators Q#15994, answer score: 9