Re: A Performance issue

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 / ಶಿವಸ್ವಾಮಿ < shivaswamykr_at_gmail.com> 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 10.1.0.5.0 on HP UX
>

-- 
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
Toon Koppelaars
RuleGen BV
+31-615907269
toon_at_rulegen_dot_com
www_dot_rulegen_dot_com

Author: "Applied Mathematics for Database Professionals"
--
http://www.freelists.org/webpage/oracle-l