Re: A Performance issue
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_at_gmail.com> wrote:
> 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.
> 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-lReceived on Fri Sep 26 2008 - 10:17:51 CDT