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