flashback buf free by RVWR waits and LOB caching

From: <Laimutis.Nedzinskas_at_seb.lt>
Date: Wed, 23 Feb 2011 10:00:06 +0200
Message-ID: <OFF8D460CC.DAFF5936-ONC2257840.0026D97A-C2257840.002BF466_at_seb.lt>



Hi,

Does anyone know some article explaining the connection between direct writes and log writer buffer ?

Ok, I will start from the very beginning:

"flashback buf free by RVWR" were causing app to timeout.

Searching metalink brought document "Flashback Database Best Practices & Performance [ID 565535.1] " which contains basically just about 2-3 useful options (sorry for being pessimistic regarding the other options but DBA can hardly do anything with "?Ensure there is sufficient I/O bandwidth to the flash recovery area" - unless he is Exadata DBA :)

  • For optimal performance when enabling flashback database on a database with the LOB or SecureFiles LOB data types in use, use the CACHE setting.
  • ...In general, RDBMS release 11.1.0.6 improves batch performance when flashback database is on. ... The performance impact was negligible once the DB_FLASHBACK_RETENTION_TARGET was met...
  • Set LOG_BUFFER to at least 8 MB to give flashback database more buffer space in memory. For large 11.1.0.7+ databases with more than a 4GB SGA, you may consider setting LOG_BUFFER to values in the range of 32-64 MB.

Ok, DB_FLASHBACK_RETENTION_TARGET looked interesting but too good to be true on 10r2 and again - what if the target is not met ??

log buffer was already 48MB.

Then knowing that lob writes caused us pain in the past I just turned on lob cache for all lob columns of app schema. Off topic: usually we have to follow formal procedures for any change but in that case I made use of panic induced freeze on our management and just did what had to be done long ago by running generated script in sqlplus, management and vendor representatives eyes helplessly following "command executed" messages on the screen

Anyway, now I am wandering how lob caching helps flashback buffer. My theory is that it is connected to direct writes - if lob is not buffered then it is directly written into datafile(according to oracle docs.) Direct write should do something about crash recovery - it must flush log buffer to disk (redo write) ? Else how oracle knows if and how to rollback/commit LOB changes during crash recovery ?
Flashback buffer resides in log buffer (?) As it (finally) turned out, our "IO bandwidth" can not sustain redo writes stream. Apparently, it can not sustain writes of other streams as well: dbwr, flashback writer.
If direct lob write causes flashback buffer writes - well, it should else crash recovery won't be able to recover(flashback) - then slow IO would explain flashback buffer waits(?)

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?

Thank you in advance, Laimis

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 23 2011 - 02:00:06 CST

Original text of this message