Re: A Performance issue

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

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