Antwort: Re: READ wait events when inserting data into a CLOB

From: Martin Klier <Martin.Klier_at_klug-is.de>
Date: Fri, 7 Dec 2012 11:05:03 +0100
Message-ID: <OF37ADEFA3.CF11F439-ONC1257ACD.0035330C-C1257ACD.00376582_at_klug-is.de>

Hi all together,
first of all, there is no flashback logging active, it's Standard Edition.

But I can tell that there was an improvement: Switching the CLOB from NOCACHE to CACHE READS improved the runtime from avg. 250ms to avg. 70ms - at least, that's what I can see when looking into the Oracle tracing. I have no feedback from the application guys regarding their experience yet. But as usual, no news should mean good news on this front. :)

It's a classical write once- read often scenario, so CACHE READS should be the optimum. Give, one knows about those facts/features, which I didn't. :)
I was not able to test CACHE and compare results in this production environment.

Recent trace file extract:
WAIT #139898165487416: nam='db file sequential read' ela= 6277 file#=5 block#˜07713 blocks=1 obj#„422 tim54712793436855 EXEC
#139898165487416:c 00,e„42,p=1,cr=1,cu,mis=0,r=1,dep=0,og=1,plh=0,tim54712793437189 STAT #139898165487416 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL (cr=1 pr=1 pw=0 timeg43 us)' CLOSE #139898165487416:c=0,e,dep=0,type=1,tim54712793438319 BINDS #139898165487416: Matching tkprof summary:
Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total 

Waited

• Waited ----------

  ---

  db file sequential read 21 0.00 0.09 direct path write 12 0.00 0.00 reliable message 11 0.00 0.00

Thank you very much for your support - everybody who guessed, adviced and helped. This list is beyond price... :)
Best regards
--

Mit freundlichem Gruß

Martin Klier
Senior Oracle Database Administrator

Von: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com> An: free <oracle-l_at_freelists.org>, Datum: 05.12.2012 23:02
Betreff: Re: READ wait events when inserting data into a CLOB Gesendet von: oracle-l-bounce_at_freelists.org

Martin,
You'd typically see "direct path" reads and writes for LOB insert operations if the lob is stored out of line or if the lob size exceed 4K. You may be getting "db file sequential read" waitevent for your indexes on the table. You'd also see "db file sequential read" waitevent for lobindex operations.

Another possibility is flashback feature requiring blocks to be read from the disk prior to performing block new operation.

I see below trace data very interesting, why same block is being read with "db file sequentially read" and "direct path read".

WAIT #139690535902720: nam='db file sequential read' ela= 321 file#=5 
block#956449 blocks=1 obj#425 tim54644922208727
WAIT #139690535902720: nam='direct path read' ela= 216 file number=5 first 

dba956449 block cnt=1 obj#425 tim54644922208998

Thanks,
Sai
http://sai-oracle.blogspot.com

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Dec 07 2012 - 11:05:03 CET