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

Question

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?

Answer 1

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

Answer 2

This seems to be a quirk of SQL*Plus and arraysize rather than pipelined functions - the following demonstrates the same effect:

create or replace function pause return integer as
begin
  dbms_lock.sleep(2);
  return 1;
end;
/

select pause from dual connect by level<10;

You can (sort of) workaround the issue by selecting the rows twice and discarding every second one:

select /*+ ordered first_rows(1) */ *
from table(tail_f) cross join (select level from dual connect by level<=2);