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:

1. Use bind variables and remove hard-coded literal values from code, as explained in the
   “Optimizing the Library Cache” section earlier in this chapter.
2. 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.
3. 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.