Re: A Performance issue
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.
At 11:14 PM Friday, you 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
Hemant K Chitale
.htmlReceived on Fri Sep 26 2008 - 10:21:54 CDT