One more similar reason for using different block
sizes for different LOBs is that a LOB item always allocates space in chunks for
it and a chunk can be a multiple of blocksize, not less. Big blocks mean
space wastage for lots of out of line small LOBs, however small blocks mean
performance degradation due fragmentation and LOB index growth for large
LOBs.
This situation can be relieved by having
tablespaces with different blocksizes for different LOBs.
Tanel.
----- Original Message -----
Sent: Friday, January 30, 2004 1:04
AM
Subject: Re: [Q] wait time /lob def
That is an interesting use of
an alternate block size Jonathan.
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
--
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).
Received on Thu Jan 29 2004 - 19:19:26 CST
