Re: switching to 'direct path read' should help prevent thrashing buffer cache ?

Jonathan Lewis wrote:
> <p.santos000_at_gmail.com> wrote in message
> news:1167841629.716228.178910_at_n51g2000cwc.googlegroups.com...
> >
>
> > Maybe I misinterpreted what you said, but I was under the impression
> > that
> > an update to a table with a ROWNUM <=500000 will lock only the 500000
> > affected rows... likewise a parallel update with a ROWNUM <=500000 will
> > also
> > lock those 500K records .. but you seem to indicate that it's not the
> > case.
> >
>
> If you do a parallel update on a non-partitioned table
> and it actually runs as a parallel update, then the table
> is locked exclusively by the co-ordinator.
>
> > I'll have to retest, but I believe I saw tx row lock contention and I
> > would have
> > thought that if the whole table was locked, I would have seen a
> > different wait
> > event.
> >
>
> This suggests that you weren't running the update in parallel,
> only the 'select' part, and the co-ordinator was updating the
> table serially. Did you remember to:
> alter session enable parallel dml;
>
>
>
>
> --
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Ah, I didn't realize that a parallel table update ... even with ROWNUM as
part of the predicate would lock the entire table instead of just the affected rows ... so this won't work for us.

Usually if I have to run the parallel update, I put a PARALLEL hint in the
update statement .. I don't enable parallel updates at the session level.

I'll review the documentation link posted by Charles Hooper.

thanks gain.
-peter Received on Fri Jan 05 2007 - 10:25:51 CST