Diagnosing Oracle High CPU Utilization on NT

PURPOSE
-------
The purpose of this article is to define a procedure for
diagnosing high CPU utilization by Oracle on the NT platform.


SCOPE & APPLICATION
-------------------

Intended audience for this article is anyone responsible for an
Oracle database running on an NT platform.


Diagnosing High CPU Utilization on NT
-------------------------------------

The first symptom of high CPU utilization by Oracle on NT is
usually a slow down of the entire system. The first step in
isolating Oracle as the culprit is to start Task Manager
and sort the output by CPU utilization.

The Task Manager can be started on NT by typing CTRL-ALT-DELETE.
This key sequence will NOT reboot NT it will produce a dialog box
that will allow the activation of task manager.

Once Task Manager is started the Processes tab should be selected
and then the title of the CPU column should be selected. This
will sort the output by CPU utilization. Each mouse select on the
column will switch back and forth between sorting the output
ascending and descending.

At this point viewing the output of task manager should make
it obvious if Oracle is actually the culprit and if not which
process is the problem.

After it has been established that Oracle is actually causing
the problem, then it is time to isolate what thread(s) in the
Oracle process is causing the problem. This can be done with
the NT performance monitor.

The NT performance monitor can be started on the server by following
Start --> Programs --> Administrative Tools --> Performance Monitor.

Once the Performance Monitor has been started then the following
steps should be taken.

1) click on the plus "+" sign button on the button menu bar -- the
Add to Chart dialog box should appear

2) in the Object: list box select Thread

3) in the Counter: list box select % Processor Time

4) in the Instance: list box do a multiple select on all of the ORACLE
threads (The multiple select can be accomplished by clicking on
the first instance and then shift clicking the last.)

5) Click the Add button in the dialog box and then the Done button

At this point you will see a list of instance numbers/thread/% Processor
Times at the bottom of the screen. The list shows each thread associated
with the Oracle process (assuming that only one Oracle instance is running).
If multiple Oracle instances are running, then you will find that some of
the instance numbers (thread numbers) are duplicated.

Each thread has a specific function within the Oracle process. The first
few are Oracle processes such as PMON, SMON and so forth. This is
discussed in more detail in the following articles, Note 104981.1 and
Note 48681.1.

The thread numbers that are beyond the background processes are the
server processes retrieving data from the database.

The key sequence CTRL-H will cause the current line in the display to
be highlighted. The arrow key can then be used to isolate which
process is consuming a large amount of CPU time.

One of the possible reasons for high Oracle CPU utilization is
related to SMON space management activity. Note 61997.1 covers
this activity in detail.

Note 61997.1
http://jno.h14.ru/txt/ORA-01575.html

http://kr.forums.oracle.com/forums/thread.jspa?threadID=579564