Re: Antwort: Re: READ wait events when inserting data into a CLOB
From: Kevin Jernigan <kevin.jernigan_at_oracle.com>
Date: Fri, 07 Dec 2012 09:59:11 -0800
Message-ID: <50C22E6F.6060205_at_oracle.com>
You should try it with SecureFiles instead of BasicFiles - the core focus of the SecureFiles project was to completely re-write all of the space management for LOBs stored in the database, so you're likely to see improvements if you switch...
Date: Fri, 07 Dec 2012 09:59:11 -0800
Message-ID: <50C22E6F.6060205_at_oracle.com>
You should try it with SecureFiles instead of BasicFiles - the core focus of the SecureFiles project was to completely re-write all of the space management for LOBs stored in the database, so you're likely to see improvements if you switch...
-- Kevin Jernigan Senior Director Product Management Advanced Compression - ACO - Advanced Row Compression - Advanced LOB Compression - Advanced LOB Deduplication - RMAN Backup Compression - Data Pump Export Compression - Data Guard Redo Network Transport Compression - Flashback Data Archive History Table Optimization Hybrid Columnar Compression - HCC Information Lifecycle Management - ILM SecureFiles Database File System - DBFS Database Smart Flash Cache Temporal database (Total Recall etc) Database Resource Manager - DBRM Direct NFS Client - dNFS Continuous Query Notification - CQN Index Organized Tables - IOT OISP (650) 607-0392 (o) (415) 710-8828 (m) On 12/7/12 2:05 AM, Martin Klier wrote:Received on Fri Dec 07 2012 - 18:59:11 CET
> 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
>
>
-- http://www.freelists.org/webpage/oracle-l