Monitoring CPU Usage – Oracle Database 10g

System Activity Report (SAR)

-bash-2.05b$ sar -u 10 5
Linux 2.4.21- (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,
FROM v$sesstat s,v$statname t, v$session n
WHERE s.statistic# = t.statistic#
AND n.sid = s.sid
AND’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;

———- ———- ———-
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

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”
where = ‘parse time cpu’
and = ‘CPU used by this session’;

% CPU for parsing

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”
where = ‘recursive cpu usage’
and = ‘CPU used by this session’;

Recursive CPU usage

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

SESSION_CACHED_CURSORS – value can be anything numeric like 500.

7 thoughts on “Monitoring CPU Usage – Oracle Database 10g

  1. 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.

  2. 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 is there any way that i can generate the same report with cpu usage in percentage by service name in last hour>?

    sum(VALUE/100) as “cpu usage (seconds)”
    v$session s,
    v$sesstat t,
    v$statname n
    NAME in (‘%CPU used by this session%’)
    s.SERVICE_NAME is not null

    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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s