Single Row Fetch from a LOB

UPDATE  21-Jul-15 :  The example in this blog post is with SQLPLUS.
Stefan Koehler points out that JDBC can behave differently.
The documentation link for 11.2 JDBC is here
He has also published an example here

When dealing with a LOB column, Oracle does not use array fetches but always does a single-row fetch.

Oracle may use single-row fetches when dealing with a LOB column

For example, for a CLOB, one row may have a 20-character string but the next row may have a 2billion character string.  Each FETCH is restricted to 1 row.  The SQLPlus command SET ARRAYSIZE is ignore.

Here is a simple test case : Firs create the test table with 100 rows

create table MY_VC_CLOB
(id_col  number,
 vc_col  varchar2(200),
 clob_col clob)
/


insert into my_vc_clob
select rownum,
'Row :  ' || rownum ||' :  This is the VARCHAR2 column ',
'Row :  ' || rownum ||' :  This is the CLOB column '
from dual
connect by level < 101
/

Fetch from the VARCHAR2 column with SET ARRAYSIZE 20 and view the results of the trace.

set arraysize 20

alter session set tracefile_identifier='Fetch_VC';

exec dbms_session.session_trace_enable;

select id_col, vc_col
from my_vc_clob
order by id_col
/


select id_col, vc_col
from my_vc_clob
order by id_col

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0          7          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.00       0.00          0          8          0         100

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 353
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
       100        100        100  SORT ORDER BY (cr=7 pr=0 pw=0 time=140 us cost=4 size=11500 card=100)
       100        100        100   TABLE ACCESS FULL MY_VC_CLOB (cr=7 pr=0 pw=0 time=133 us cost=3 size=11500 card=100)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       6        0.00          0.00
  SQL*Net message from client                     6        2.81          2.83
********************************************************************************

So, there were 5 (+1 extra always) FETCHes and 5 (+1) SQLNet round-trips.  Ignore the time for the SQL*Net message from client -- I had a 2second extra wait when executing the SQL.

 Fetch from the CLOB Column with SET ARRAYSIZE 20 and view the results of the trace :

set arraysize 20

alter session set tracefile_identifier='Fetch_CLOB';

exec dbms_session.session_trace_enable;

select id_col, clob_col
from my_vc_clob
order by id_col
/


select id_col, clob_col
from my_vc_clob
order by id_col

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      101      0.00       0.00          0          7          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      103      0.00       0.00          0          8          0         100

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 353
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
       100        100        100  SORT ORDER BY (cr=7 pr=0 pw=0 time=231 us cost=48 size=201500 card=100)
       100        100        100   TABLE ACCESS FULL MY_VC_CLOB (cr=7 pr=0 pw=0 time=256 us cost=3 size=201500 card=100)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     102        0.00          0.00
  SQL*Net message from client                   102        1.29          1.34
********************************************************************************

So, there were 100 (+1 extra always) FETCHEs and 100 SQLNet round-trips.

Notice that the number of blocks read (consistent-reads) is 7 and the COST is 3 in both cases.  So, "consistent gets" and COST are not always the true explanation of the COST and elapsed time for a query.

.
.
.