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