High CPU Utilization !

[titu]

Hello all ,

In one of my system I am running oracle database due to which there is high CPU Utilization.

When DBA start the Oracle it goes up to 99%

Any fine tuning or suggestion to decrease CPU utilization apart from adding more CPU

Thanks in advance.

Titu

[Patrick Wallek]

>>When DBA start the Oracle it goes up to 99%

What goes up to 99%? If you run 'top' or 'glance' what process or processes use the CPU?

[Hein van den Heuvel]

You already suggest that Oracle consumes the CPU.
First you want to make sure that the bulk of the CPU is USER mode, not SYS.

If there is a lot of SYS (more then 30%), then some system (memory management) tuning may be needed, or oracle is doing too many system calls perhaps to gather statistics.

Assuming most is user mode you have to work with the DBA (you may actually have to talk to the DBA!) to figure out what makes Oracle consume the CPU and whether that consumption is in line with the expectations.
If it is in line, then you may have to add CPUs or acquire faster CPUs (or faster memory!).
If it seems out of whack, then use STATSPACK or AWR to figure out what the top consuming queries are, and tune them!
It is NOT really (1) possible to tune user time away as a system manager. User time corresponds directly with what the system is asked to do.

Hope this helps some,
Regards,
Hein van den Heuvel
HvdH Performance Computing

(1) On a Numa box, with extreme tuning using cell awareness, and memory locality you can sometimes reduce user time by reducing memory latency, but that's typically reserved for benchmarks and not very practical.

[Emil Velez]

Whats the memory utilization like. You may be paging like crazy.. You might want to go into glance and see the memory report and see how much free memory you have.

If you are low on memory you can see how much SGA the dba configured and ask him to lower it or buy more memory.

More CPUs only help if it is truely a CPU issue.

[Andrew Cowan]

Titu,

Is Oracle running with a large (pre-warmed) SGA and/or do you have the correct indexes configured.

Oracle will always pin large amounts of swap even if it never actually uses it. The classic symptoms here are if the system constantly Pages-Out, and reclaims pages, but never pages in. If there are a large amount of 'Context-switches' shown by sar or vmstat, the processor cannot progress its threads, and in this case you probably need more physical memory.

[Rita C Workman]

Titu,

All good suggestions, but without more clear understanding of your environment, just guesses.

Please provide folks here with:

Your server model
The O/S version
Number of CPU
Amount of physical memory
Amount of swapspace set up
Are you patched reasonably current?

Oracle version
Oracle SGA size

Also parm info (assuming it's pre 11.31)

dbc_max%
dbc_min%
nproc
ninode
maxdsiz
sem*
sshmax
vx_ninode

That should give us all at least a good start

Thanks,
Rita

[Rita C Workman]

..and one last question...

Are you running any other third party vendor software on that box?

Thanks,
Rita

[titu]

12:50:00 %usr %sys %wio %idle
12:50:05 4 3 10 83
12:50:10 6 3 7 84
12:50:15 6 3 9 83

This output I got from the sar

[Patrick Wallek · Accepted solution]

Well, that shows 83-84% idle. Basically your server isn't doing much.

[Steven E. Protter]

Shalom,

There is nothing wrong with a system being constantly at 99% utilization.

Think about it as getting your money's worth on the investment.

As we see, the 99% situation was temporary. Expect that when you start up a server like Oracle. Oracle uses a lot of resources at startup to open up the various daemons.

SEP

[Hein van den Heuvel]

Titu,

The sar output you posted shows a near idle system.

But you started with:
"When DBA start the Oracle it goes up to 99%"

Assuming you did not start this topic just for fun, we must conclude there is some disconnect or miss commumnication

You maybe able to help by trying to tell us what the real problem is.

"When DBA start the Oracle it goes up to 99%"

- 99% of what?
- according to who ?
- which tool was used to establish that?
- for how long?
- just once, or all the time?
- why is this perceived as a problem (I'm serious!)
- how important is it to address this?
:


fwiw,
Hein.

[titu]

Thanks you all !!