Re: 'write complete waits' with unknown SQLID?
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Mon, 10 Mar 2014 15:52:34 -0700 (PDT)
Message-ID: <1394491954.35794.YahooMailNeo_at_web124703.mail.ne1.yahoo.com>
LOB writes are a bit different from 'standard' writes (read that as 'non-LOB'). Before images of LOB data are stored in the LOB itself, and Oracle reserves PCTVERSION of the LOB storage for LOB before images.� The PCTVERSION setting reserves that percentage of the total number of chunks of LOB data which have been allocated during the update process.� I expect that these 'write complete waits' are because Oracle is updating a LOB and is reading before images from the LOB and writing updates to the LOB.� This may be happening after the active update has completed (I believe we could consider this 'background processing' for the LOB) which may be why there is no SQL_ID available. My two cents. � David Fitzjarrell Primary author, "Oracle Exadata Survival Guide" On Monday, March 10, 2014 4:29 PM, Rich <richa03_at_gmail.com> wrote: Hi List, Oracle 11.2.0.3 on RHEL 6.3 w/ASM. We see 'write complete waits' from a single session with no SQLID from OEM... I see the definition for this wait is at http://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_wait_bottlenecks_write_complete_waits_pct.html (at least for 10.2 - I don't see any Oracle documentation on this in the 11.2 set). Looking at v$session, I see that the file# is a file in a TS which only has a [securefile] LOB. How do we determine what is happening with this session and why? TIA, Rich
Date: Mon, 10 Mar 2014 15:52:34 -0700 (PDT)
Message-ID: <1394491954.35794.YahooMailNeo_at_web124703.mail.ne1.yahoo.com>
LOB writes are a bit different from 'standard' writes (read that as 'non-LOB'). Before images of LOB data are stored in the LOB itself, and Oracle reserves PCTVERSION of the LOB storage for LOB before images.� The PCTVERSION setting reserves that percentage of the total number of chunks of LOB data which have been allocated during the update process.� I expect that these 'write complete waits' are because Oracle is updating a LOB and is reading before images from the LOB and writing updates to the LOB.� This may be happening after the active update has completed (I believe we could consider this 'background processing' for the LOB) which may be why there is no SQL_ID available. My two cents. � David Fitzjarrell Primary author, "Oracle Exadata Survival Guide" On Monday, March 10, 2014 4:29 PM, Rich <richa03_at_gmail.com> wrote: Hi List, Oracle 11.2.0.3 on RHEL 6.3 w/ASM. We see 'write complete waits' from a single session with no SQLID from OEM... I see the definition for this wait is at http://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_wait_bottlenecks_write_complete_waits_pct.html (at least for 10.2 - I don't see any Oracle documentation on this in the 11.2 set). Looking at v$session, I see that the file# is a file in a TS which only has a [securefile] LOB. How do we determine what is happening with this session and why? TIA, Rich
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 10 2014 - 23:52:34 CET