Search My Oracle Blog

Custom Search

14 April, 2013

Single Row Fetch from a LOB

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

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.

.
.
.

No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com