Re: Why is the same CLOB datablock read multiple times?

From: Jure Bratina <jure.bratina_at_gmail.com>
Date: Wed, 29 Jan 2014 10:00:37 +0100
Message-ID: <CAC08BHK7-Pv7bB93x2wV4Qxt=0AvJrmFstqx2NJCumYcy8zufg_at_mail.gmail.com>

(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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 29 2014 - 10:00:37 CET