Re: 'write complete waits' with unknown SQLID?
Date: Tue, 11 Mar 2014 11:23:26 +1100
Message-ID: <CALn1tDva0NXrzBJ4rpqS06e2d9DV8mgJmCz2HnRFxJ_32jQw=A_at_mail.gmail.com>
Funnily enough I've just seen as similar thing but mine was across a whole host of sessions once I killed a data pump job that was running too slow.
Could be related to a large transaction rollback.
On Tue, Mar 11, 2014 at 9:52 AM, David Fitzjarrell <oratune_at_yahoo.com>wrote:
> 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
>
>
>
-- Stojan www.stojanveselinovski.com/blog www.stojanveselinovski.com -- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 11 2014 - 01:23:26 CET