Every once in a while I want SQL*Plus to fetch one row at a time, so I set ARRAYSIZE to 1.
But:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SQL> set autotrace traceonly statisticsSQL> set arraysize 1SQL> select * from user1.cf1 where rownum <= 100;Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 102 consistent gets 0 physical reads 0 redo size 258194 bytes sent via SQL*Net to client 1091 bytes received via SQL*Net from client 51 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 100 rows processed |
We only did 51 network round trips to fetch 100 rows. This is because SQL*Plus is using OCI and the default for prefetch (OCI_ATTR_PREFETCH_ROWS) is set to 1. So we end up with 2 rows per fetch call (note that with prefetch 1, the first round trip already returns 1 row).
Now, I really, really want to fetch just one row at a time. Starting with Oracle 12.1 you can create an OCI configuration file to specify various settings, one of them being prefetch. See documentation for more details.
vi ${TNS_ADMIN}/oraaccess.xml
1 2 3 4 5 6 7 8 | <?xml version="1.0"?> <oraaccess xmlns="http://xmlns.oracle.com/oci/oraaccess" xmlns:oci="http://xmlns.oracle.com/oci/oraaccess" schemaLocation="http://xmlns.oracle.com/oci/oraaccess http://xmlns.oracle.com/oci/oraaccess.xsd"> <default_parameters> <prefetch> <rows>0</rows> </prefetch> </default_parameters></oraaccess> |
With this configuration in place we should see 100+ network round trips.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SQL> set autotrace traceonly statisticsSQL> set arraysize 1SQL> select * from user1.cf1 where rownum <= 100;Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 102 consistent gets 0 physical reads 0 redo size 267988 bytes sent via SQL*Net to client 1652 bytes received via SQL*Net from client 102 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 100 rows processed |
Go play fetch 