A Performance issue
- From: "Shivaswamy / ಶಿವಸ್ವಾಮಿ" <shivaswamykr@xxxxxxxxx>
- To: Oracle-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 26 Sep 2008 11:14:41 -0400
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
Other related posts:
