System Activity Report (SAR)
-bash-2.05b$ sar -u 10 5
Linux 2.4.21-37.0.0.1.4.ELhugemem (ap6188rt) 06/12/2007
10:48:24 PM CPU %user %nice %system %iowait %idle
10:48:34 PM all 22.07 0.00 14.36 0.03 63.54
10:48:44 PM all 16.70 0.00 13.93 0.17 69.20
10:48:54 PM all 8.80 0.00 8.15 0.25 82.80
10:49:04 PM all 2.52 0.00 3.55 0.00 93.92
10:49:14 PM all 2.05 0.00 4.00 0.00 93.95
Average: all 10.43 0.00 8.80 0.09 80.69
We can check the processes which are consuming high CPU units. When we use the command ps -eaf, 4th column shows the number of CPU units, that process is using. Example
oracle03 12979 1 0 21:39 ? 00:00:00 ora_q003_tkr12m1
oracle03 22815 1 0 21:57 ? 00:00:00 ora_q001_tkr12m1
oracle03 2720 1 0 22:36 ? 00:00:00 oracletkr12m1 (LOCAL=NO)
oracle03 30548 1 0 22:42 ? 00:00:00 oracletkr12m1 (LOCAL=NO)
oracle03 25572 1 0 22:48 ? 00:00:00 oracletkr12m1 (LOCAL=NO)
oracle03 14232 1 35 22:53 ? 00:00:03 oracletkr12m1 (LOCAL=NO)
You can also check the users, which are consuming high CPU.
SELECT n.username,
s.sid,
s.value
FROM v$sesstat s,v$statname t, v$session n
WHERE s.statistic# = t.statistic#
AND n.sid = s.sid
AND t.name=’CPU used by this session’
ORDER BY s.value desc;
USERNAME SID VALUE
—————————— ———- ———-
1093 194184
1092 77446
1088 67564
1089 43054
1090 19192
1072 15009
APPS 832 1777
APPS 998 1190
APPS 822 577
APPS 900 508
APPS 823 477
You can also check session level CPU using
SQL> select * from v$sesstat
2 where statistic# = 12
3 order by value desc;
SID STATISTIC# VALUE
———- ———- ———-
1093 12 194184
1092 12 77475
1088 12 67579
1089 12 43062
1090 12 19194
1072 12 15010
832 12 1785
998 12 1197
822 12 577
You can also decompose the total CPU usage. Basically a CPU time is
total CPU time = parsing CPU usage + recursive CPU usage + other CPU usage
SELECT name,value FROM V$SYSSTAT
WHERE NAME IN (‘CPU used by this session’,
‘recursive cpu usage’,
‘parse time cpu’);
NAME VALUE
—————————————————————- ———-
recursive cpu usage 6276669
CPU used by this session 8806491
parse time cpu 482645
Ideally (parsing CPU usage + recursive CPU usage) should be significantly less then CPU used by this session. Here we can see that (parsing CPU usage + recursive CPU usage) is almost equal to CPU used by this session. Here we need to make some tuning in reducing the time for recursive CPU usage.
To determine percentage of CPU usage for parsing
select (a.value / b.value)*100 “% CPU for parsing”
from V$SYSSTAT a, V$SYSSTAT b
where a.name = ‘parse time cpu’
and b.name = ‘CPU used by this session’;
% CPU for parsing
—————–
5.48300146
Reducing Parse Time CPU Usage
If parse time CPU is the major part of total CPU usage, you need to reduce this by performing the following steps:
- Use bind variables and remove hard-coded literal values from code, as explained in the
“Optimizing the Library Cache” section earlier in this chapter. - Make sure you aren’t allocating too much memory for the shared pool. Remember that even if you have an exact copy of a new SQL statement in your library cache, Oracle has to find it by scanning all the statements in the cache. If you have a zillion relatively useless statements sitting in the cache, all they’re doing is slowing down the instance by increasing the parse time.
- Make sure you don’t have latch contention on the library cache, which could result in increased parse time CPU usage. We will see latch contentions and how to reduce it – later.
To determine percentage of Recursive CPU Usage
select (a.value / b.value)*100 “% recursive cpu usage”
from V$SYSSTAT a, V$SYSSTAT b
where a.name = ‘recursive cpu usage’
and b.name = ‘CPU used by this session’;
Recursive CPU usage
——————-
71.421139
This is really a bad number for recursive CPU usage. Sometimes, to execute a SQL statement issued by a user, the Oracle Server must issue additional statements. Such statements are called recursive calls or recursive SQL statements. For example, if you insert a row into a table that does not have enough space to hold that row, the Oracle Server makes recursive calls to allocate the space dynamically if dictionary managed tablespaces are being used.
Recursive calls are also generated due to the inavailability of dictionary info in the dictionary cache, firing of database triggers, execution of DDL, execution of SQL within PL/SQL blocks, functions or stored procedures and enforcement of referential integrity constraints.
If the recursive CPU usage percentage is a large proportion of total CPU usage, you may want to make sure the shared pool memory allocation is adequate. However, a PL/SQL-based application will always have a significant amount of recursive CPU usage.
To reduce the recursive CPU usage, make sure that shared pool is sized correctly. Also you can check the parameters like
CURSOR_SHARING – values can be EXACT, SIMILAR, FORCE
SESSION_CACHED_CURSORS – value can be anything numeric like 500.
You might find this useful:
http://oracledatabaseissues.blogspot.com/2009/12/high-cpu-usage-spike-load-on-database.html
thanks
Good explanation thanks for sharing.
I read almost 4 blog on this site all are excelent.Thanks for sharing such internal info.
It’s amazing to pay a visit this site and reading the views of all colleagues regarding this article, while I am also zealous of getting know-how.
After looking into a handful of the blog posts on
your web page, I really like your technique of writing a blog.
I book-marked it to my bookmark webpage list and will be checking back soon.
Please visit my website too and tell me what you think.
I’m amazed, I must say. Seldom do I encounter a blog that’s equally educative and interesting, and without
a doubt, you have hit the nail on the head. The issue is
something that not enough people are speaking intelligently about.
I’m very happy that I found this in my search for something concerning this.
Thanks for the detail explanation…Actually we are looking to generate the report which shows the CPU usage in percentage by the session…Like in below report shows the cpu usage in seconds..so is there any way that i can generate the same report with cpu usage in percentage by service name in last hour>?
SELECT
s.SERVICE_NAME,
s.OSUSER,
sum(VALUE/100) as “cpu usage (seconds)”
FROM
v$session s,
v$sesstat t,
v$statname n
WHERE
t.STATISTIC# = n.STATISTIC#
AND
NAME in (‘%CPU used by this session%’)
AND
s.SERVICE_NAME is not null
GROUP BY SERVICE_NAME,s.OSUSER order by 1
/
SERVICE_NAME OSUSER cpu usage (seconds)
—————————— —————————— ——————-
OPMSIPNT dw_rep 650,094.9600
OPMSIPNT_CUST pm1was 975,142.4400
OPMSIPNT_CUST pm3was 650,094.9600
OPMSIPNT_CUST pm5was 650,094.9600
OPMSIPNT_CUST pm6was 650,094.9600
OPMSIPNT_DPREF pm1was 162,523.7400
OPMSIPNT_DPREF pm3was 325,047.4800
OPMSIPNT_DPREF pm5was 162,523.7400
OPMSIPNT_DPREF pm6was 325,047.4800
OPMSIPNT_EMPL pm3was 162,523.7400
OPMSIPNT_EMPL pm5was 81,261.8700
OPMSIPNT_EMPL pm6was 81,261.8700
OPMSIPNT_ENRT pm4was 3,087,951.0600
OPMSIPNT_MDM pm1was 162,523.7400
OPMSIPNT_MDM pm3was 406,309.3500
OPMSIPNT_MDM pm5was 162,523.7400
OPMSIPNT_MDM pm6was 81,261.8700
OPMSIPNT_TOOL splunk 162,523.7400
SYS$BACKGROUND oracle 3,494,260.4100
SYS$USERS oracle 2,519,117.9700