Re: SQL causing 100% CPU utilization
Date: Tue, 10 May 2011 06:17:02 -0700 (PDT)
Message-ID: <2e8ebae8-5250-4b36-8d4c-2149c38e33c9_at_z7g2000prh.googlegroups.com>
On May 6, 10:56 am, joel garry <joel-ga..._at_home.com> wrote:
> 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.
>
> May have nothing to do with the OP, buthttp://jonathanlewis.wordpress.com/2011/03/24/small-tables/
> and related links is the kind of thing I had in mind.
>
> Taking a quick gander at one of my systems, I see an index fast full
> scan, only getting rowid for 5 rows out of 200K, cpu cost 44670240, I/
> O cost 427, briefly pounding the hell out of a cpu. Before I changed
> the code, it did a full table scan, pounding the cpu harder for a
> shorter time, IIRC. It's the type of query that has a nullable column
> in the middle of a five column key that is not part of the query and
> the last column is a date and I'm getting > an arbitrary date and the
> rest of the key is specified. I'm wondering if it would make a
> difference if I specified it as a primary key. (Other priorities is
> why I haven't simply tried it yet, load testing is problematic).
>
> jg
> --
> _at_home.com is bogus.
> If you thought breaking up by text message was bad...http://www.signonsandiego.com/news/2011/may/06/motorola-mobility-keep...
You guys are right. Here's what I found out.
Looking at the AWR report closer I noticed a back ground wait event that caught my eye that I missed before. The 'rdbms ipc message' metric was too high. The event rdbms ipc message indicates that a process is waiting for a message to arrive on his message queue. The number of message blocks parameter was set too small. If a process needs a message it will get the latch, search a linked list for a free message block, unlink this message block , put this message on the message queue for the intended process and then release this latch. indicated If a process needs a message it will get the latch, search a linked list for a free message block, unlink this message block , put this message on the message queue for the intended process and then release the latch. The event rdbms ipc message indicates that a process is waiting for a message to arrive on his message queue. The event rdbms ipc message reply is used to indicate that a message is expected back by the sending process."
So....The process was pegging the CPU requesting a message block from the LRU list in the SGA but ran into latch contention. The latch contention also caused contention with the rollback process. Received on Tue May 10 2011 - 08:17:02 CDT