Re: flashback buf free by RVWR waits and LOB caching

From: <Laimutis.Nedzinskas_at_seb.lt>
Date: Mon, 28 Feb 2011 10:25:17 +0200
Message-ID: <OFC444A227.5A3CD993-ONC2257845.00284FBE-C2257845.002E42BA_at_seb.lt>



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_at_www.sqltools-plusplus.org>                                                                                      
                                                                                                                                                  
  To:         oracle-l_at_freelists.org                                                                                                              
                                                                                                                                                  
  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

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





--
http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 28 2011 - 02:25:17 CST

Original text of this message