Re: [Q] wait time /lob def

There is a note in one of the manuals about nologging lobs (or nocache lob, I forget which) that points out that the "unrecoverable SCN" for file that holds the LOB has to be updated in the control file whenever the LOB is updated.

If you actually have a performance problem because of this - i.e. if lots of people/processes are running slowly because they are waiting on control file writes - then you might want to make the LOB cache/loggong. But control file writes are not inherently a bad thing to be blocked. Of course, if the LOBs are quite large, then the time taken to write the LOB may be far greater than the time taken to update the controlfile - which would make any concerns about the controlfile update irrelevant. So there is no 'obvious' correct answer to your question, without examing your current activity.

The note (which I think Steve Adams' also has on his website) mentions an event that can be set to stop the controlfile update when the LOB is updated. This may not be a good idea, though, as it may affect some aspects of recoverability.

If you do make the LOB 'cached', then remember that all reads and writes go through the db_block_buffer, which could affect all the other I/O activity adversely, so you might want to consider putting the LOBs into a tablespace with a non-standard block size so that the LOB activity doesn't affect the rest of the cache. (You do also have the option in more recent versions of refining the caching properties so the LOB can be readcache only, writecache only or read/write cache or nocache, I believe).

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person   who can answer the questions, but the   person who can question the answers -- T. Schick Jr

Next public appearances:
 Jan 29th 2004 UKOUG Unix SIG - v$ and x$  March 2004 Hotsos Symposium - The Burden of Proof  March 2004 Charlotte NC OUG - CBO Tutorial  April 2004 Iceland

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February
____UK___June

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, January 29, 2004 5: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!)
>
> 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.
>
> Should I change the lobs to cache/logging across the
> board?
>
> Thanks for any insight.
> Barb
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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).