Re: severe performance issues when flashback is on
- From: jonathan@xxxxxxxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 20 Oct 2011 07:00:13 +0100
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@xxxxxxxxx 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
