flashback performance overhead

Maclean Liu(刘相兵

For batch direct load workloads, flashback can cause as much as 30% overhead in Oracle 10g.  In Oracle 11g, users can experience 2%-30% flashback overhead for batch direct workloads.  This wide difference is due to a performance optimization that kicks in only during certain situations.  In general, this performance optimization is used on data loads into space that was deallocated at least DB_FLASHBACK_RETENTION_TARGET minutes ago.  This performance optimization reduces overhead for direct load to approximately 2%.  When the performance optimization is not able to be used, flashback overhead for direct load operations can be as high as 30%.


Form document MOS 565535.1

anyone can clarify more details about "certain situations" ?

Maclean Liu(刘相兵

We need more  ODM test or Material  about flashback ,  Work in Process

GDS

这个文档有两点说得比较含糊：
1. 这里的performance optimization到底是怎么样一个过程？
2. 从语义中看限制条件应该是比较严的，那么具体的限制条件是什么，在那些场景下这下这个optimization才会起作用。
pls take a look at it.

Maclean Liu(刘相兵

Some ODM DATA:

Flashback is much faster and easier than traditional recovery
=================
Flashback impact


The environment for the OLTP < 2% overhead (actually < 1% in my tests) was a single instance 11.1.0.6 database running the Swingbench Order Entry workload with  on Linux 32-bit, RHEL ( 2.6.9-42.0.3.0.1.ELhugemem) and the I/O subsystem was:


Array     Serial #     Disks     Memory
EMC Clariion CX700     #1249     2 Trays - 15 disks per Tray - 73Gb     4Gb per SP (512Mb Read Cache / 2631Mb Write Cache)
EMC Clariion CX500     #1477     2 Trays - 15 disks per Tray - 73Gb     2Gb per SP (365Mb Read Cache / 1107Mb Write Cache)

where the cx700 was the data ASM disk group and the cx500 was the ASM flash recovery area disk group.  So the FRA had 28 spindles.

Other qualitative reasons for low FB logging impact:

- Only one before-image is logged per 30 min interval, regardless of number of changes to the block
- No extra block reads are required when writing to flashback logs (note: except for direct insert loads, which is fixed in 11.1 for single instance)
- Only data file block changes are tracked, not all database files (e.g. online redo, controlfile, etc.)
- In general, no process needs to wait for flashback log I/O in a well configured OLTP system.



Use the V$FLASHBACK_DATABASE_STAT view to monitor the overhead of logging flashback data in the flashback logs. This view also contains estimated flashback space needed based on previous workloads. This view contains 24 hours of information with each row representing a one-hour time interval. The FLASHBACK_DATA and REDO_DATA columns describe bytes of flashback data and redo data written during the time interval. The DB_DATA column describes bytes of data blocks read and written. The overhead of Flashback Database logging depends on the workload.



Flashback is Easy
Single command instead of complex procedure
Less performance overhead for OLTP and batch

Maclean Liu(刘相兵

In 11.2.0.2 Real Application Cluster databases can take advantage of an optimization which reduces the performance impact of flashback database for direct loads.  This optimization can reduce the overhead to as low as 2%.  11.2.0.1 Real Application Cluster databases can get the same benefits by applying  Patch:9184513 .  


For batch direct load workloads, flashback can cause as much as 30% overhead in Oracle 10g.  In Oracle 11g, users can experience 2%-30% flashback overhead for batch direct workloads.  This wide difference is due to a performance optimization that kicks in only during certain situations.  In general, this performance optimization is used on data loads into space that was deallocated at least DB_FLASHBACK_RETENTION_TARGET minutes ago.  This performance optimization reduces overhead for direct load to approximately 2%.  When the performance optimization is not able to be used, flashback overhead for direct load operations can be as high as 30%.  Different Oracle 11g releases may also impose other restrictions:

    For 11.1 the performance optimization described here only works on Single Instance databases.
    For 11.2.0.2 the performance optimization also works on RAC databases.  The optimization can be enabled in 11.2.0.1 by applying Patch:9184513.
    SQL*Loader parallel direct patch loads prior to 11.1.0.7 can have continual performance degradation when flashback database is enabled due to implicit segment shrinking that occurs.  This is fixed in 11.1.0.7


It looks like  Patch:9184513.  has been recalled .