A Performance issue

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

--
http://www.freelists.org/webpage/oracle-l