Re: Why is the same CLOB datablock read multiple times?
- From: Jure Bratina <jure.bratina@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 29 Jan 2014 10:00:37 +0100
(I'm reposting the findings in case it is useful for anyone. The reply I
posted yesterday isn't visible in the archives, so I guess the problem was
that I didn't remove lines quoted from previous messages).
> From your SQL session what have you done with set long and set
longchunksize ?
I didn't modify them, I only tried with different values for arraysize but
there wasn't any significant difference in the number of network roundtrips
and logical reads. Now that you mentioned 'set long' and 'set
longchunksize' I noticed that arraysize doesn't have any effect on LOB
retireval (from "High 'SQL*Net message from client' when querying LOB
tables (Doc ID 1590389.1)": Increasing the SDU and/or increasing the number
of fetched rows (in sqlplus 'set arraysize') will not make any difference.)
Those are the statistics with default sqlplus settings:
Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
39 consistent gets
11 physical reads
0 redo size
9530 bytes sent via SQL*Net to client
4144 bytes received via SQL*Net from client
24 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
Those are the statistics when setting long to 1000000 and longchunksize to
100 (I hope this will simulate the small LOB buffer size you mentioned):
Statistics
----------------------------------------------------
0 recursive calls
0 db block gets
116149 consistent gets
32009 physical reads
0 redo size
12627332 bytes sent via SQL*Net to client
10155448 bytes received via SQL*Net from client
31648 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
So the number of logical reads and network roundtrips increased
tremendously. The 10046 trace looks very similar to what I observed when
tracing the session from nhibernate, so the problem seems to be what you
suggested, i.e. a nhibernate's small LOB buffer size. Honestly I didn't
know that arraysize doesn't have any effect on the number of logical reads
when using LOBs and that you have to use longchunksize to control that.
Is there any particular reason why the same data block is re-read from disk
multiple times (if that's not an error in the trace file as you suggested)?
That's even more interesting because the CLOB is NOCACHE and direct path
reads were used to read it into the PGA. Since the PGA is private to the
session (and if I remember correctly, even latching buffers in the sense of
"cache buffers chains" latches isn't necessary) I don't understand why
re-reading the block from disk is necessary? I mean, doesn't the datablock
stay cached in the PGA even between FETCH calls within the execution of the
same SQL statement?
> Do you happen to have a 16KB block size for the LOB ? Or a 16KB chunk
size ?
Just for completeness, block size is 8KB, chunk size is also 8KB.
Thank you very much for your help.
Regards,
Jure Bratina
Other related posts: