This is a blog note that started life in September 2001 (which was before I started blogging, I think), got drafted as a blog in January 2014 because of a question on the Oracle-L list server, and has finally got to publication following a question on the ODC database forum. (But the comments in the blog don’t look as if they are relevant to the question.)
The question on Oracle-l was: Why is the same CLOB datablock read multiple times?
The answer is basically: client programs tend to set a “sensible” fetch buffer size for LOBs and if the buffer size is less than the size of the LOB, the block(s) the LOB is in have to be visited multiple times; and for nocache LOBs that means the block(s) will be read multiple times.
This can be seen quite easily in SQL*Plus which has two helpful parameters (one dating back to at least v5):
set long N set longchunksize M
The “long” setting tells SQL*Plus how much of the LOB to display on screen, the “longchunksize” tells SQL*Plus how much of the LOB to fetch from the database on each call. The default value is 80 for both settings. So let’s set up some data and do a few simple demonstrations using nothing more subtle than autotrace.
rem
rem Script: lob_fetch_2.sql
rem Author: Jonathan Lewis
rem Dated: July 2012
rem Purpose:
rem
rem Last tested
rem 21.3.0.0
rem 18.3.0.0
rem 12.1.0.2
rem 11.2.0.2
rem
create table t1 (
id number ,
v1 varchar2(60),
c1 clob,
c2 clob
)
lob(c1) store as (enable storage in row cache)
lob(c2) store as (enable storage in row cache)
;
begin
for i in 1..1000 loop
insert into t1 values(i, lpad(i,60), lpad(i,60), empty_clob());
end loop;
commit;
end;
/
execute dbms_stats.gather_table_stats(null,'t1')
I’ve got a table with two CLOB columns, both declared to enable storage in row and cache. I’ve also got a varchar2() column, and I’ve loaded one of the CLOB columns and the varchar2() column with a 60 character string, setting the other CLOB column explicitly to the empty_clob() value – which is not the same as setting it to NULL.
Now I’ll do 3 selects, fetching the id column and one other column, showing only the autotrace statistics:
set long 80 set longchunksize 80 set arraysize 5000 set autotrace traceonly statistics prompt ============== prompt Varchar2 fetch prompt ============== select id, v1 from t1; prompt ================ prompt Small CLOB fetch prompt ================ select id, c1 from t1; prompt ================ prompt Empty CLOB fetch prompt ================ select id, c2 from t1; set autotrace off
I’ve explicitly set long and longchunksize to 80 (the defaults) just to make it clear that that’s what they are for this test; and I’ve set the arraysize to 5000 so that I can get the 1,000 rows of data in the smallest number of fetches. Here are the three sets of autotrace statistics:
==============
Varchar2 fetch
==============
1000 rows selected.
Statistics
----------------------------------------------------------
11 recursive calls
11 db block gets
75 consistent gets
0 physical reads
2040 redo size
70545 bytes sent via SQL*Net to client
624 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
================
Small CLOB fetch
================
1000 rows selected.
Statistics
----------------------------------------------------------
4 recursive calls
5 db block gets
2036 consistent gets
0 physical reads
992 redo size
707454 bytes sent via SQL*Net to client
296624 bytes received via SQL*Net from client
2002 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
================
Enpty CLOB fetch
================
1000 rows selected.
Statistics
----------------------------------------------------------
3 recursive calls
5 db block gets
2036 consistent gets
0 physical reads
1048 redo size
585454 bytes sent via SQL*Net to client
296624 bytes received via SQL*Net from client
2002 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
Key points to note (ignoring minor details like the recursive calls to parse the statements):
- The query for the the varchar2() column did two SQL*Net roundtrips and 75 consistent gets.
- The query for the small, inline, CLOB did 2,002 SQL*Net roundtrips and 2,036 consistent gets
- The query for the empty CLOB did exactly the same number of SQL*Net roundtrips and consistent gets as for the small CLOB
Because we were selecting a LOB column SQL*Plus switched from array fetches to single row fetches. In the first fetch of each pair of fetches it fetched the non-CLOB columns and the “LOB locator”; then in the second fetch of each pair it fetched the CLOB data – and it did this even when the LOB locator could (in principle) have told it that there was no data to fetch. (If you select both clob columns in the same query the number of SQL*Net roundtrips will go up to 3 per row in this test.)
Remember I said there was a difference between empty_clob() and NULL ? If you update the table to set c2 to null before running the query to select c2 you’ll see only 1,000 (and a few) SQL*Net roundtrips – you still get single row processing because you’re selecting a LOB column but when the NULL arrives at SQL*Plus the code will know that there is no LOB data to retrieve.
Now try this:
truncate table t1;
begin
for i in 1..1000 loop
insert into t1 values(i, lpad(i,60), lpad(i,180), empty_clob());
end loop;
commit;
end;
/
execute dbms_stats.gather_table_stats(null,'t1')
set long 170
select id, c1 from t1;
Pause for thought as you work out what the stats will look like
.
.
.
.
.
The longchunksize (still at the default of 80) is now too small to collect all the data that should be displayed in one SQL*Net roundtrip – it’s going to take 3 roundtrips to get 170 bytes, so we might expect to see about 4,000 SQL*Net roundtrips and a similar number of consistent gets. This is what the stats look like:
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
4030 consistent gets
0 physical reads
0 redo size
1485454 bytes sent via SQL*Net to client
866624 bytes received via SQL*Net from client
4002 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
Sure enough we do single row fetches, one SQL*Net roundtrip for each row with its LOB locator, then three more roundtrips for each LOB for a total of 4,000 round trips and (approximately) 4,000 consistent gets.
One last test – let’s change the c1 clob to nocache and disable storage in row then repeat the last experiment where the long setting is larger than the longchunksize setting.
alter table t1 move lob(c1) store as (disable storage in row nocache);
execute dbms_stats.gather_table_stats(null,'t1')
select id, c1 from t1;
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
4001 consistent gets
6000 physical reads
0 redo size
1485454 bytes sent via SQL*Net to client
866624 bytes received via SQL*Net from client
4002 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
Now there’s a surprise! I was expecting to see 3000 physical reads as each “out of row, nocache” LOB was accessed three times to pick up the three pieces of longchunksize using direct path reads. So why have I got 6,000 physical reads? Time to enable extended tracing (event 10046, level 8) and repeat.
Here’s a sample from the trace file – this trace is from 18.3, but the LOBREAD lines are a feature that appeared some time around 11.2.0.2.
FETCH #140355181475400:c=58,e=57,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=3617692013,tim=5288719901 WAIT #140355181475400: nam='SQL*Net message from client' ela= 78 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288720015 WAIT #0: nam='direct path read' ela= 12 file number=2 first dba=2867 block cnt=1 obj#=132008 tim=5288720198 WAIT #0: nam='direct path read' ela= 8 file number=2 first dba=2867 block cnt=1 obj#=132008 tim=5288720288 WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288720326 LOBREAD: type=PERSISTENT LOB,bytes=80,c=247,e=246,p=2,cr=1,cu=0,tim=5288720350 WAIT #0: nam='SQL*Net message from client' ela= 82 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288720483 WAIT #0: nam='direct path read' ela= 12 file number=2 first dba=2867 block cnt=1 obj#=132008 tim=5288720733 WAIT #0: nam='direct path read' ela= 8 file number=2 first dba=2867 block cnt=1 obj#=132008 tim=5288720836 WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288720873 LOBREAD: type=PERSISTENT LOB,bytes=80,c=0,e=261,p=2,cr=1,cu=0,tim=5288720898 WAIT #0: nam='SQL*Net message from client' ela= 121 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288721071 WAIT #0: nam='direct path read' ela= 11 file number=2 first dba=2867 block cnt=1 obj#=132008 tim=5288721216 WAIT #0: nam='direct path read' ela= 8 file number=2 first dba=2867 block cnt=1 obj#=132008 tim=5288721300 WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288721335 LOBREAD: type=PERSISTENT LOB,bytes=20,c=0,e=236,p=2,cr=1,cu=0,tim=5288721359 WAIT #0: nam='SQL*Net message from client' ela= 73 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288721506 WAIT #140355181475400: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288721664 FETCH #140355181475400:c=51,e=51,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=3617692013,tim=5288721695 WAIT #140355181475400: nam='SQL*Net message from client' ela= 74 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288721801 WAIT #0: nam='direct path read' ela= 12 file number=2 first dba=2868 block cnt=1 obj#=132008 tim=5288721939 WAIT #0: nam='direct path read' ela= 47 file number=2 first dba=2868 block cnt=1 obj#=132008 tim=5288722065 WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288722122 LOBREAD: type=PERSISTENT LOB,bytes=80,c=357,e=357,p=2,cr=1,cu=0,tim=5288722204 WAIT #0: nam='SQL*Net message from client' ela= 81 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288722351 WAIT #0: nam='direct path read' ela= 10 file number=2 first dba=2868 block cnt=1 obj#=132008 tim=5288722489 WAIT #0: nam='direct path read' ela= 11 file number=2 first dba=2868 block cnt=1 obj#=132008 tim=5288722671 WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288722720 LOBREAD: type=PERSISTENT LOB,bytes=80,c=0,e=349,p=2,cr=1,cu=0,tim=5288722746 WAIT #0: nam='SQL*Net message from client' ela= 76 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288722874 WAIT #0: nam='direct path read' ela= 11 file number=2 first dba=2868 block cnt=1 obj#=132008 tim=5288723013 WAIT #0: nam='direct path read' ela= 10 file number=2 first dba=2868 block cnt=1 obj#=132008 tim=5288723160 WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288723199 LOBREAD: type=PERSISTENT LOB,bytes=20,c=0,e=302,p=2,cr=1,cu=0,tim=5288723224
As you can see, the client seems to FETCH one row then do two direct path reads to read 80 bytes from a LOB, then another two direct path reads for the next 80 bytes, then another two direct path reads for the last 20 bytes. So the 6,000 reads is (strangely) doubling the 3,000 I was expecting to see and the trace file seems to say it’s really happening that way.
Unfortunately I decided to take snapshots of the session stats at this point – and that made things even worse (claiming, for example, 4,000 “consistent gets” but 7,000 “no work consistent gets”), the stats seem to be rather messed up and the whole thing looked very different when I ran this particular test on 12.1.0.2 (which claimed only one direct path read per CLOB rather than the three I was expecting).
Bottom line, though: notwithstanding any oddities of reporting, when your client progam is fetching LOBs from the database, you probably have to put up with:
single row fetches – that get the LOB locator then multiple fetches for each LOB, with the number of LOBREAD calls dependent on the buffer size the client allocates for pulling LOB data from the database.
So when handling LOBs make sure you know how the client can be configured to minimise SQL*Net roundtrips, and check a few simple examples looking at the the trace files, session stats and session events before you start loading data at production volumes.
Update (Sept 2021)
There have been some significant changes of mechanism (or maybe of counting) by the time we get to 21c. We still see single row processing taking place in SQL*Plus if there’s a LOB in the select list, and LOB data being fetched through extra visits to the server; but:
- the SQL*Net roundtrips due to the LOB fetching may not be counted
- multiple “small enough” LOBs for a single row may be fetched in the same LOB read (to be tested),
- there’s an SQL*Plus setting “set lobprefetch N” which defaults to 0 but can be set as high as 32767 to allow “small enough” LOBs to be fetched in the same round trip as the row itself (though it’s still single row fetches). If you “set markup csv on”, the lobprefetch is one of the setting that is automatically changed at the same time.
- you really need to remember that with “max_string_size = extended”, declarations of strings in excess of the old 4,000 byte limit will be stored internally as LOBs; and a “create table as select” may give you a load of LOB columns that you don’t really realize you’ve got. (And 3rd party “database-agnostic” tools may create some “text” columns that are also “inivisibly” created as LOBs.)
Comments and related questions are welcome.