 |
|
Oracle Tips by Burleson |
CPU Usage Statistics
CPU usage statistics form a critical component of response time
analysis. Response time is defined as service time plus wait time.
The service time component is CPU usage and can be broken down into
CPU parse time, CPU recursive time and CPU other.
If CPU usage constitutes a large part of the response time, the
database should be tuned based on the type of CPU usage shown. Tune
SQL for the proper access path, less sorting and hashing for CPU
other. Furthermore, look at bind variable usage and cursor
sharing/caching for CPU parse; and look at excessive data dictionary
lookups, excessive dynamic sizing and other recursive operations for
CPU recursive time.
The following script will report the CPU time utilized by the
database since the last startup of the database.
* CPU_TIME.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
rem
rem CPU_TIME.SQL
rem Mike Ault
rem
col name heading 'Statistic'
col value heading 'Value'
ttitle 'CPU Related Statistics'
spool cpu_stats
select name,value from v$sysstat where upper(name) like '%CPU%'
;
spool off
clear columns
ttitle off
The following sample report displays CPU statistics from v$sysstat
from an Oracle 9iR2 instance:
Wed Sep 22 page 1
CPU Related Statistics
Statistic Value
--------------------------------------------------------
recursive cpu usage 35094
CPU used when call started 402131
CPU used by this session 402218
parse time cpu 12110
OS User level CPU time 0
OS System call CPU time 0
OS Other system trap CPU time 0
OS Wait-cpu (latency) time 0
8 rows selected.
The service time metric is important and is used in the calculation
of the response time metric which determines the breakdown between
wait time and CPU time (service time) for a specific system. All of
the components for the service time calculation are shown above.
Essentially the formula would look something like this: Service Time
= CPU Other + CPU Parse + CPU Recursion. CPU Other is calculated by
subtracting the sum of Recursive CPU Usage and Parse Time CPU from
CPU Used by this session.
The above book excerpt is from:
Oracle
Tuning Power Scripts
With 100+ High Performance
SQL Scripts
Oracle In-Focus Series
ISBN
0-9744486-7-2
Mike Ault, Donald K.
Burleson. Harry Conway
http://www.rampant-books.com/book_2005_1_power_tuning.htm |
