Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Flashback - High Load

ChristianMay 23 2011 — edited May 23 2011
Hey folks,

what do you think about the generated load while using flashback.

My database stopped working while a background process is deleting rows from a huge table.
Flashback is enabled - my flashback area filled up.

Just in general. How much load does flashback generate ?

Do you think thats always a good idea to switch off flashback, before deleting thousand of rows ?
Yes, I am sure, that the old data is not needed any more.... :-)

Chris

Comments

rajeysh May 23 2011
refer :- DELETE with NOLOGGING?

1018769
Pavan Kumar May 23 2011
Hi,

You can refer to doc:http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/rpfbdb003.htm
section : "Estimating Disk Space Requirements for Flashback Database Logs"
Do you think thats always a good idea to switch off flashback, before deleting thousand of rows ?
Yes, I am sure, that the old data is not needed any more...
If you have requirement (or) your user deleted the important data from huge tables - which needs to get recovered - what you will do ...?
Recovery from backups (point in time)

- Pavan Kumar N
Christian May 23 2011
Are flashback logs also transfered to the standby site in a data guard enviroment ?

Or are flashback logs on the standby site generated by the logs which were shipped by the rfs process ?
Uwehesse-Oracle May 23 2011
The performance impact of writing Flashback Logs is neglectable. You should not need to switch it off for performance reason therefore.
Flashback Logs do not get transported to your standby database. It is considered as best practice to have primary as well as standby running in flashback mode, though.

Kind regards
Uwe Hesse

http://uhesse.wordpress.com
chris_c May 23 2011
Flash back generation at the standby is independent, the flashback logs are not shipped accross the network, the impact of flashback logging can vary from system to system and depends on a number of factors. I have had issues where a guaranteed restore point is uses that prevents flashback logs being reused and then during heavy dml the size of the newly created logfiles is increased up to 2G, also if you then fill the available disk space everything will stop untill more space is made available.
In this case the filesystem used for flashback logs did not support async IO so the system effectivly paused every time a new log was created, have you got a statspack/awr report from the system showing the issue.
Dom Brooks May 23 2011
How much load does flashback generate ?

It depends.
You have to be careful with LOBS and cache settings:
See http://laimisnd.wordpress.com/2011/03/25/lobs-and-flashback-database-performance/
Jonathan Lewis May 23 2011
Christian wrote:

what do you think about the generated load while using flashback.

My database stopped working while a background process is deleting rows from a huge table.
Flashback is enabled - my flashback area filled up.

Just in general. How much load does flashback generate ?
If you delete large volumes of data you will generate large volumes of undo.

Before an undo block can be recycled it has to be "new"ed (in Oracle-speak). Normalllly this means that Oracle simply creates a new version of the block in memory without reference to the existing block on disc; but if you are running in flashback mode Oracle (usually) has to read the undo block from disc, and write it into the flashback log before newing it. This activity will show up in statistic "physical reads for flashback new" - the combination of the extra reads and the extra volume of flashback log could cause an I/O problem - the latter (as others have commented) being a reason for the database stopping.

Do you think thats always a good idea to switch off flashback, before deleting thousand of rows ?
That depends on why you're running in flashback mode.

Regards
Jonathan Lewis
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 20 2011
Added on May 23 2011
7 comments
2,559 views