Re: SQL causing 100% CPU utilization
From: joel garry <joel-garry_at_home.com>
Date: Thu, 5 May 2011 17:08:24 -0700 (PDT)
Message-ID: <fda7159d-42e2-418c-8b57-5caea7f709d3_at_r33g2000prh.googlegroups.com>
On May 5, 11:38 am, "Gerard H. Pille" <g..._at_skynet.be> wrote:
> joel garry wrote:
> > As David said, post the sql and plan. I smell full table scans
> > thrashing through the sga, hogging the cpu.
>
> Don't trust your nose, Gary, full table scans don't get into the SGA.
Date: Thu, 5 May 2011 17:08:24 -0700 (PDT)
Message-ID: <fda7159d-42e2-418c-8b57-5caea7f709d3_at_r33g2000prh.googlegroups.com>
On May 5, 11:38 am, "Gerard H. Pille" <g..._at_skynet.be> wrote:
> joel garry wrote:
> > As David said, post the sql and plan. I smell full table scans
> > thrashing through the sga, hogging the cpu.
>
> Don't trust your nose, Gary, full table scans don't get into the SGA.
That is the conventional wisdom, but I think I see that when a small (relative to the sga size) table is being full scanned and it is all in there. But I have no proof that I can post and it wouldn't be unusual for me to get this wrong. All I can do is point to the 11gR2 direct read scan that copies dirty blocks from the sga to the pga and mumble something about later patches of 10g maybe trying to do something similar. Speculation, yes, could be something more straightforward like copying CR blocks in the SGA for non-full scans. Only some seriously deep analysis could tell.
>
> CPU is most often wasted by using PL/SQL functions in a where clause. That is a sure kill.
Not what I see, but my system may not be the most often.
jg
-- _at_home.com is bogus. http://www.signonsandiego.com/news/2011/may/05/new-website-explores-impact-2050-transportation-pl/Received on Thu May 05 2011 - 19:08:24 CDT