Re: High CPU usage
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 19 Jun 2012 05:17:41 +0100
Your example show you using 32 CPU seconds in the execution line with NO
buffer visits.
Either
a) your CPU problem appears before you start to execute the statement
or
b) 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@xxxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
