Antwort: Re: READ wait events when inserting data into a CLOB
- From: Martin Klier <Martin.Klier@xxxxxxxxxx>
- To: saibabu_d@xxxxxxxxx
- Date: Fri, 7 Dec 2012 11:05:03 +0100
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,e42,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@xxxxxxxxx>
An: free <oracle-l@xxxxxxxxxxxxx>,
Datum: 05.12.2012 23:02
Betreff: Re: READ wait events when inserting data into a CLOB
Gesendet von: oracle-l-bounce@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
