Re: A Performance issue
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: shivaswamykr@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Fri, 26 Sep 2008 23:21:54 +0800
The CR mode block gets from Undo would be for either of Read
Consistency or Delayed Block Cleanout.
Oracle visits a table block, finds that it has been updated and needs
to visit the Undo block to see if the Update has been committed or not.
Hemant
At 11:14 PM Friday, you 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
Hemant K Chitale
http://hemantoracledba.blogspot.com
.html
--
//www.freelists.org/webpage/oracle-l
Other related posts:
