Re: High CPU usage

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 19 Jun 2012 05:17:41 +0100
Message-ID: <A74E5884486C406CAA245111740CB968_at_Primary>

Your example show you using 32 CPU seconds in the execution line with NO buffer visits.
Either

1. your CPU problem appears before you start to execute the statement or
2. the buffer visits associated with this query have not been reported properly.

The query itself looks, according to the Fetch stats, looks like a trivial, lightweight
query - it would have been nice to see how simple it really was - since you get 50
row in only 55 buffer visits.

Assuming the simplest interpretation - i.e. tkprof is reporting truthfully - we could guess
that your problem is in the library cache access required to pin and execute the statement.
It's possible you have a latch or mutex spin problem. Since you haven't said anything about
latch sleeps and mutex sleeps, and given the version of Oracle, I'd go for a mutex spin
issue.

Things to do:

    Try a few runs taking snapshots of v$mystat to see if any other big numbers show up

    Try a few runs taking snapshots of v$latch, or using Tanel's latchprofx script

    Try a few runs taking snapshots of the v$mutex_XXXX views

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• Original Message ----- From: "Gerry Miller" <gerry_at_millerandbowman.com> To: <oracle-l_at_freelists.org> Sent: Wednesday, June 13, 2012 5:10 AM Subject: High CPU usage

Furthermore, in comparing my regression test results I have found that CPU
usage is consistently and significantly higher in one result set than others. For example, consider this one tkprof output:

First Run: call     count       cpu    elapsed       disk      query
current        rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse 1 0.00 0.00 0 0 0   0
Execute 1 0.00 0.00 0 0 0   0
Fetch 5 0.00 0.00 0 55 0 50
------- ------ -------- ---------- ---------- ---------- ----------

---

total 7 0.00 0.00 0 55 0 50

Second Run: call     count       cpu    elapsed       disk      query
current        rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse 1 0.00 0.00 0 0 0   0
Execute 1 32.74 31.98 0 0 0   0
Fetch 5 0.00 0.00 0 55 0 50
------- ------ -------- ---------- ---------- ---------- ----------

---

total 7 32.74 31.99 0 55 0 50

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jun 18 2012 - 23:17:41 CDT