Why is the same CLOB datablock read multiple times?

From: Jure Bratina <jure.bratina_at_gmail.com>
Date: Tue, 28 Jan 2014 20:57:59 +0100
Message-ID: <CAC08BHLMwkoqjha=prBOJ2fwDO5-+eFX5Vd6im9htw+BF=iD3g_at_mail.gmail.com>



Hi,

I have a question about the high number of reads on a LOB on a 10.2.0.4.0 64bit instance. The problem I'm facing is that selecting a CLOB column (from nhibernate) causes many reads from the same data block, e.g. :

WAIT #0: nam='direct path read' ela= 442 file number=5 first dba=59461 block cnt=1 obj#=199184 tim=5566091808
WAIT #0: nam='SQL*Net message from client' ela= 4034 driver id=1413697536
#bytes=1 p3=0 obj#=199184 tim=5566095873
WAIT #0: nam='direct path read' ela= 152 file number=5 first dba=59461 block cnt=1 obj#=199184 tim=5566096110
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536
#bytes=1 p3=0 obj#=199184 tim=5566096127
WAIT #0: nam='SQL*Net message from client' ela= 3942 driver id=1413697536
#bytes=1 p3=0 obj#=199184 tim=5566100094
WAIT #0: nam='direct path read' ela= 173 file number=5 first dba=59461 block cnt=1 obj#=199184 tim=5566100334
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1413697536
#bytes=1 p3=0 obj#=199184 tim=5566100351
WAIT #0: nam='SQL*Net message from client' ela= 5277 driver id=1413697536
#bytes=1 p3=0 obj#=199184 tim=5566105655
..
[the set of three wait events 'direct path read', 'SQL*Net message to client' and 'SQL*Net message from client' repeats 14 more times for the same dba=59461]

So in total the datablock [file_id=5, dba=59461] was read 17 times. The same happens to other LOB's data blocks. The CLOB is NOCACHE, if I set it to CACHE the datablock is read only once using 'db file sequential read' but the SQLNet roundtrips are still present so I guess the difference is that the first read is from disk, all subsequent reads are served from the buffer cache and thus no wait event is reported. The data block in question is a LOB segment block:

Start dump data blocks tsn: 6 file#: 5 minblk 59461 maxblk 59461 buffer tsn: 6 rdba: 0x0140e845 (5/59461) scn: 0x0000.2be1ebfd seq: 0x02 flg: 0x04 tail: 0xebfd2802 frmt: 0x02 chkval: 0xbad9 type: 0x28=PAGETABLE MANAGED LOB BLOCK

On the other hand if I execute the same query from sqlplus, the LOB's datablocks are read only once, so the wait events are like this:

WAIT #1: nam='SQL*Net message from client' ela= 146 driver id=1111838976
#bytes=1 p3=0 obj#=199184 tim=6160998955
WAIT #0: nam='direct path read' ela= 11114 file number=5 first dba=59512 block cnt=1 obj#=199184 tim=6161011019
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1111838976
#bytes=1 p3=0 obj#=199184 tim=6161012052
WAIT #0: nam='SQL*Net message from client' ela= 3499 driver id=1111838976
#bytes=1 p3=0 obj#=199184 tim=6161016400
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1111838976
#bytes=1 p3=0 obj#=199184 tim=6161017406
FETCH #1:c=0,e=878,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=6161018232 WAIT #1: nam='SQL*Net message from client' ela= 93 driver id=1111838976
#bytes=1 p3=0 obj#=199184 tim=6161019249
WAIT #0: nam='direct path read' ela= 8232 file number=5 first dba=59435 block cnt=1 obj#=199184 tim=6161028564
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1111838976
#bytes=1 p3=0 obj#=199184 tim=6161029587
WAIT #0: nam='SQL*Net message from client' ela= 3989 driver id=1111838976
#bytes=1 p3=0 obj#=199184 tim=6161034417
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1111838976
#bytes=1 p3=0 obj#=199184 tim=6161035376
FETCH #1:c=0,e=860,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=6161036189 ......

I still get a direct path read for every row the SQL returns, but the same block doesn't get read multiple times and what's even more important, that way I avoid waiting for the network roundtrips (SQL*Net message from/to client) which actually constitute the biggest part of this SQL's response time. Is there any apparent reason why there are multiple reads on the same data block?

Thank you in advance for any suggestions

Regards,
Jure Bratina

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jan 28 2014 - 20:57:59 CET

Original text of this message