Re: process running slowly, lots of yield() calls ?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 29 Nov 2008 12:41:39 -0000
Message-ID: <4q2dnb842aQcpazUnZ2dnUVZ8sTinZ2d@bt.com>

"Andy" <andyjgw_at_gmail.com> wrote in message news:438c632e-79e5-4fa9-b88a-be1e3e5a0d5d_at_3g2000yqs.googlegroups.com... On Nov 21, 3:04 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
<snip>
> Andy,
>
> I believe that Oracle changed some of the latch-related code
> so that (for some latches, at least) rather than sleeping after a
> failed spin, the first failure turns into a yield, putting the process
> immediately at the bottom of the run queue in a 'ready to run'
> state. So it's possible that your yields are a sign of latch contention.
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle:
> Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users'
> FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html

Thank you Jonathan, that's very helpful. Am wondering, though, if this is consistent with such high CPU usage of the process in question (it saturated a processor all day long - luckily, it finished running 15 minutes before the backup) ?

When the process first started going slowly and it didn't appear to be directly inefficient SQL, I had suspected some latch/queue contention, but statspack didn't really show anything along these lines.

Thanks for all the thoughts on the above everyone :)

Andy

It is possible.

A bad choice of plan could result in a very large number of buffer visits - which tends to translate into CPU. Since buffer visits often have to be preceded by latch activity your query could have been suffering a lot of contention on the cache buffers chains latch (possibly without showing a large number of sleeps).

-- 
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
Received on Sat Nov 29 2008 - 06:41:39 CST

Original text of this message