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

--
http://www.freelists.org/webpage/oracle-l

Other related posts: