flashback buf free by RVWR waits and LOB caching
- From: Laimutis.Nedzinskas@xxxxxx
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Feb 2011 10:00:06 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
