Re: A Performance issue

From: Toon Koppelaars <>
Date: Fri, 26 Sep 2008 17:17:51 +0200
Message-ID: <>

You might want to study the phenomenon of "delayed block cleanout". It could be that this is what you are experiencing.

On Fri, Sep 26, 2008 at 5:14 PM, Shivaswamy / ಶಿವಸ್ವಾಮಿ <> wrote:

> Hello.
> I am trying to trouble shoot the issue with a report run in a data
> warehouse. The session spends lot of time reading UNDO datafile, as I see
> from ASH. As a consequence, the blocks obtained are in CR mode and not
> current, which one would expect normally. The underlying tables called by
> the function in the report are not changing. Infact the function is
> operating on a daily static copy of the big table. This job started after
> the static copy is built.
> I notice that more than 3/4th of the time is spent on building the block in
> CR mode, in the past x hours. That is p1, p2 for sequential read point to
> undo datafile.
> What I want to understand is this - why the identified SQL is going to CR
> mode or visiting Undo, when it should have got the block directly from the
> table. How I can find the reason?
> Oh BTW, sess_stat has high value of "transaction tables consistent reads -
> undo records applied" for this session.
> Any input, appreciated.
> Thanks,
> Shiva
> PS: My version is on HP UX

Toon Koppelaars
RuleGen BV

Author: "Applied Mathematics for Database Professionals"
Received on Fri Sep 26 2008 - 10:17:51 CDT

Original text of this message