Re: A Performance issue
- From: "Toon Koppelaars" <toon@xxxxxxxxxxx>
- To: shivaswamykr@xxxxxxxxx
- Date: Fri, 26 Sep 2008 17:17:51 +0200
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@xxxxxxxxx> 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"
Other related posts:
