Re: SQL causing 100% CPU utilization

From: joel garry <joel-garry_at_home.com>
Date: Thu, 5 May 2011 10:54:32 -0700 (PDT)
Message-ID: <a88d7267-61c4-49b9-b825-df4fea3fdccf_at_z7g2000prh.googlegroups.com>

On May 4, 9:53 am, Steven Holland <smith.h.ste..._at_gmail.com> wrote:
> I have a problem SQL statement that our DBA's, Orace support nor
> myselfI cannot fix.  Believe me, I've tried everything. It takes 3
> hours to run and it's all CPU time.   The offending sqlid is
> '8m5dd8aqw4ka8'.  Here's part of the AWR report.  It's too big for
> this but I can email it to anyone who needs it.
>
> WORKLOAD REPOSITORY report for
>
> DB Name DB Id   Instance        Inst num        Release RAC     Host
> IDWSTGDV        425809231       IDWSTGDV        1       10.2.0.5.0      NO      istdwdev
...
> Cache Sizes
>
>         Begin   End
> Buffer Cache:   38,624M 38,624M Std Block Size: 8K

OK, you have a 38G cache...

> Top 5 Timed Events
>
> event waits time(s) avg wait(ms) % total call time wait class
> cpu time         1,726   88.6
> px deq credit: send blkd 225,491 184 1 9.4 other
> db file scattered read 21,794 43 2 2.2 user i/o

And you are waiting on parallel, cpu and full table scans.

As David said, post the sql and plan. I smell full table scans thrashing through the sga, hogging the cpu. If those are elements of a parallel query each doing a full table scan, yikes. Some things work better serially.

Also, don't forget in a complex multiuser system, something other than the problem sql may be taking up cpu, so cast a critical eye upon all your top sql. It is even possible that making one thing faster may bottleneck something else worse. Has anything changed since the problem started? Has it ever run better?

jg

--
_at_home.com is bogus.
http://www.theinquirer.net/inquirer/news/2068531/oracle-toss-129-patent-infringement-claims-google-lawsuit

Received on Thu May 05 2011 - 12:54:32 CDT