Re: flashback buf free by RVWR waits and LOB caching
- From: Laimutis.Nedzinskas@xxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 28 Feb 2011 10:25:17 +0200
Thank you vary much, Randolf
I was not fully aware of bug "6168063 smaller non-cached logging LOBs
potentially generate a lot more redo". Thank you for pointing that again.
And yes, our LOB's are just "bigger" varchar2's (e.g. average size is a few
kb's.) They are not youtube clips. In that respect they've allways been
good candidates for caching and probably even inline storage (at least some
of them.)
The situation is that LOB's are used in transactional (!) tables which
immediately brings one thought only: as soon as Oracle or whoever opens
doors, developers start using the features assuming they must perform like
anything else. They do not care to distinguish LOB from varchar2 - they are
just "bigger" varchar2 for them.
There is another question: apart from the before mentioned bug, does direct
writes mean Oracle has to flush flashback buffer (and redo buffer) ? There
seems to be one possibility to avoid the flush - it's the fact that LOB's
has the "redo/undo" implemented in the LOB segment and [must be] directly
written too ( I referer to Oracle's description of PCTVERSION "When a LOB
is modified, a new version of the LOB page is produced in order to support
consistent read of prior versions of the LOB value.") That would be enough
to crash-recover.
Only that the actual recovery would happen when LOB is first accessed.
Which brings some interesting "hows" how Oracle performs LOB version of
"block cleanout" but again, that is doable provided enough data is stored
for crash recovery. The only questions are where that data is stored and
how it affects performance.
Best regards, Laimis N
---------------------------------------------------------------------------------
Please consider the environment before printing this e-mail
From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: 2011.02.27 21:21
Subject: Re: flashback buf free by RVWR waits and LOB caching
> Looks like all I tried to say can be validated by looking into oracle
> stats and traces, etc, etc, but apart from hacking I am more interested
if
> there are some Oracle(am I naive?) articles explaining the connection
> between LOB's, direct writes, flashback and log buffer?
Sorry, a bit late to the party, but since you haven't received any reply
(at least visible to the list) yet, and I only just got now my posting
privilege re-granted, just a few notes on this:
As you've pointed out, there is a significant difference between cached and
non-cached LOBs.
One important point is that depending on your current cache usage the
additional pressure on cache due to the LOBs now being cached could mean
that you've addressed one issue and introduced another one due to other
activities requiring now more physical I/O.
If that seems to happen, you should think about either:
- moving the LOB segments to a non-default block-size tablespace with its
own cache defined
- or define a recycle/keep cache and assign the LOB segments to that
separate cache
For your particular issue, one potential explanation - if you're not
hitting a bug (e.g. 6168063 - High "Flashback buf free by RVWR" waits [ID
6168063.8]) - could be the fact that smaller non-cached logging LOBs
potentially generate a lot more redo (and therefore also more flashback)
than cached LOBs - since they simply always write full chunks to the data
blocks and redo stream. If you're looking for an official description of
that you can find more details in MOS document "LOBS - Storage, Redo and
Performance Issues [ID 66431.1]". Note this potential wastage/overhead
should only be relevant if the LOBs are fairly small. The effect should be
negligible if you write e.g. multi-MB LOBs.
It would probably be interesting to compare AWR / Statspack reports from
before and after your change of caching - see if you can spot a significant
difference in the amount of redo generated per second.
So if non-cached LOBs are not that large two settings of the LOBs should be
reviewed:
- DISABLE/ENABLE STORAGE IN ROW
- CHUNK size
The chunk size plays a very important role for the amount of redo generated
for smaller non-cached logging LOBs, since they always generate at least
"chunk" size of data / redo resp. rounded up to the "chunk" size. The
"chunk" size is by default and (always at least I think) "block size" (not
sure about default and non-default block size tablespaces) but can be
declared up to 32K. So you might want to check the "chunk" size declared
for your LOBs.
Note that if you handle smaller LOBs then storing them inline (for data
sized less than 4K) gives you also the redo and caching behaviour of
"normal" table segments even with a "non-cached" LOB declaration since the
data is simply stored in the table segment, of course with the trade-off
that the table segment itself will become larger. So you might want to
check if your lobs are declared with storage disabled or enabled in row.
There is also a little interesting detail that in-line lobs maintain their
12 first out-of-line chunks differently from out-of-line lobs - the
pointers to the 12 first chunks are stored inline and don't use the LOB
index whereas the out-of-line lob stores all pointers in the LOB index
beginning right from the first chunk.
So in a worst case scenario - if you write a LOB with just a few hundred
bytes, but it has been declared as:
DISABLE STORAGE IN ROW NOCACHE LOGGING CHUNK 32768
then this generates a massive overhead in terms of data written by direct
writes, redo and flashback.
And just to give you an idea that there are more options - a non-cached LOB
could be declared as NOLOGGING (I haven't tested yet what happens then to
the amount of flashback) and you also have the option to declare it as
CACHE READS rather than CACHE for read and write.
Note that all this applies to Basicfiles, if you're already on 11g
Securefiles provide even more options (e.g. the FILESYSTEM_LIKE_LOGGING
option).
Have you checked the mentioned bug "6168063 - High "Flashback buf free by
RVWR" waits [ID 6168063.8]" in the document "Flashback Database Best
Practices & Performance [ID 565535.1]" that you've cited (and bug 9032717
for SecureFiles...)
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
