Re: [Q] wait time /lob def

Comments below.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, January 29, 2004 7:04 PM

> Jonathan / Tanel:
> I, however, AM having this problem. Didn't know where
> to look till I saw this message. (I love this list!)

So do I ;)

>
> I've yet to capture the sid (and therefore track back
> to the table) where the 'direct path write' occurs.
> Definitions for the tables were supplied by the
> vendor. When I look at at the lobs, the definitions
> are mixed. Most are nocache, logging yes. some are
> no/no
>
> I don't see much on metalink about this -- just a
> couple of generic articles on lob storage.

If you are up to attending RMOUG Training days in few weeks, then I'll be speaking about LOB performance tuning there, you're most welcome ;)

>
> Should I change the lobs to cache/logging across the
> board?

Well, if you set all your LOBs to CACHE (cached LOBs are always logged), then you'll be generating more redo for current nocache nologging LOBs as well. Also, your buffer cache gets hit, especially if your LOBs are big.

One possibility is setting event 10359 at level 1 to what Jonathan also referred, then your direct writes won't cause controlfile updates anymore. But when you have set this event, you can't rely on UNRECOVERABLE_CHANGE# in your V$DATAFILE anymore, for determining whether a datafile should be backed up due nologging operations in it.

If you should ever need to restore a file containing NOLOGGING NOCACHE LOBs, then any "inconsistent" blocks will be marked corrupt (block sqn=0xFF) since there was no redo information logged for them. In that case you have update the corrupt LOBs to nulls for example to reclaim the space in LOB segment. Note that having few corrupt LOB items in a LOB segment won't affect other, healthy LOB items, you just have to have some kind of error handling mechanism implemented when you hit a corrupt item.

Note that updates to LOB indexes are always cached and logged, despite the NOLOGGING setting.

Tanel.

>
> Thanks for any insight.
> Barb
>
> Table
> Name Cach LOG
> ---------------- ---- ---
> CONTACTGROUP NO YES
> CONTENT NO YES
> IMAGEBINARY NO YES
> IMAGEHEADER NO YES
> IMAGEROW NO NO
> IMPORTIN NO NO
> TF_NEW_CONTENT NO YES
> XMLS_DICTIONARY NO YES
> XMLS_SALESSUPPORT NO NO
> XMLS_STYLEPART YES YES
>
> --- Tanel Poder <tanel.poder.003_at_mail.ee> wrote:
> > Hi!
> >
> > > Do you have any LOBs defined with
> > > the nocache nologging attribute ?
> >
> > This was what I initially thought would be the
> > problem, but then I thought,
> > in case of nocache lobs you should see direct path
> > writes (lob) a lot...
> >
> > Tanel.
> >
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.net
> > --
> > Author: Tanel Poder
> > INET: tanel.poder.003_at_mail.ee
> >
> > Fat City Network Services -- 858-538-5051
> > http://www.fatcity.com
> > San Diego, California -- Mailing list and web
> > hosting services
> >
> ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free web site building tool. Try it!
> http://webhosting.yahoo.com/ps/sb/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Barbara Baker
> INET: barbarabbaker_at_yahoo.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).