Re: severe performance issues when flashback is on

Take a snapshot of the session statistics (V$MYSTAT / V$SESSTAT) from the start to the end of the insert and check for any statistics relating to physical reads. My first guess is that you're seeing "physical reads for flashback new". When you re-use a block from new (e.g. recycling an undo block) Oracle can "new" it in memory rather than reading it and reformatting - but when flashback is enabled Oracle may decide fairly often that it needs to read the block and write it to the flashback log before newing it. The commonest source is undo blocks, but when you truncate and reuse objects, their blocks can also be subject to a lot of "new"ing that gets into the flashback log.

You can also check how closely the reads for flashback new correlate to "flashback log write bytes" and "flashback log writes".

Regards
Jonathan Lewis

huanshengchen_at_gmail.com wrote:
> When flashback on, what oracle is reading during second round? the datafi=
> le
> or flashback logs? refer to p1/p2 of db file sequential read in the trace=
> ..
> i've not testing in 11gr2 myself, from MOS, the overhead is expected.
>
> Flashback Database Best Practices & Performance [ID 565535.1]
>
> --
> Regards
> Sidney Chen

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