RE: The best CPU usage measurement in Oracle: BUFFER_GETS or CPU

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Mon, 21 Jun 2004 09:55:38 -0500
Message-ID: <029301c4579f$d4680560$6601a8c0@CVMLAP02>

Jurijs,

If, in step 1, you've discovered that either (a) 'CPU service' or (b) competition for CPU service (what the book calls 'unaccounted for' time) = is
the major source of your response time consumption, then the right step = 2 is
to identify the top consumers of CPU_TIME during the problem time = interval.
The direct statistic, not an indirect or incomplete one like BUFFER_GETS = for
exactly the reasons you have described.

This is true /if/ CPU_TIME is a reliable statistic in Oracle. I have not investigated whether it is, but I do expect that it is.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 6/22 Pittsburgh, 7/20 Cleveland, 8/10 = Boston

- SQL Optimization 101: 5/24 San Diego, 6/14 Chicago, 6/28 Denver
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of J.Velikanovs_at_alise.lv
Sent: Monday, June 21, 2004 3:05 AM
To: oracle-l_at_freelists.org
Subject: RE: The best CPU usage measurement in Oracle: BUFFER_GETS or CPU_TIME? >> My point is: may be now the better measure is CPU_TIME? I show in=20 previous
>> posting situation then CPU_TIME figure is 10 times accurate then=20 BUFFER_GETS.

>> Can you show me contrariwise situation?=20
>>=20
>> [Cary Millsap] No, I think you're actually on the best path to the=20

solution.
Lets me ask contrariwise question:
Are there situations when using statspack report (if using) or other=20 instance wide performance tool the better way to see TOP SQL-s is sort = by=20
BUFFER_GETS not CPU_TIME?

Jurijs

Additional info from previous postings:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
1. Activity=20
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
NAME VALUE

----------------------------------------------------------------=20

session logical reads 23002
CPU used by this session 137

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
2. Activity=20
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
NAME VALUE

----------------------------------------------------------------=20

session logical reads 23006
CPU used by this session 1422

I.
session logical reads=20
Action 1 =3D Action 2
II.
CPU used by this session
Action 1 * 10 =3D Action 2

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Jun 21 2004 - 09:53:55 CDT

RE: The best CPU usage measurement in Oracle: BUFFER_GETS or CPU_TIME?