Re: flashback buf free by RVWR waits and LOB caching
- From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 27 Feb 2011 20:15:42 +0100
> 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
Other related posts:
