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