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"