Database load

[mooks]

Hi

The following is the load of the database server during peak hours. Is this consider high? How to reduce the load? Please advice. Thanks!

Load averages: 13.55, 13.00, 11.34
248 processes: 211 sleeping, 37 running
Cpu states:
CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 15.35 75.2% 0.0% 24.8% 0.0% 0.0% 0.0% 0.0% 0.0%
1 11.76 81.0% 0.0% 19.0% 0.0% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 13.55 78.0% 0.0% 22.0% 0.0% 0.0% 0.0% 0.0% 0.0%

Memory: 356504K (270360K) real, 332748K (261540K) virtual, 1250248K free Page#
1/12

[slimdave]

Don't be afraid of high CPU usage -- if you don't use those CPU cycles then you don't get to save them for a rainy day.

[mooks]

In this case how to rate the CPU usuage? Still consider normal?

[sreddy]

Do not Tune until some one complain that its running slow. Why do you care its normal or not if everyone is happy with response times.

Ask this question yourself when someone cpmplain. By that time you can anlyze the load and you know whats normal and whats abnormal.

Its relative term "Load averages" depending upon the applications and kinda of transactions and whats going on the database. Hope you got it.

[slimdave]

Originally posted by mooks
In this case how to rate the CPU usuage? Still consider normal?

You might monitor it long-term ... use sar to get a cpu snapshot every ten minutes, dumping the result to a file. It makes a nice Excel chart for management.

[mooks]

The server will slow down a lot whenever the usuage goes up, which is normal, but sometimes it could hang up my database which is my main concern! So is it the load which is causing the database to hand or could it be something else??

[slimdave]

Does the database actually stop responding?

[mooks]

The database will becomes very very slow and eventually it just hanged! :(

[slimdave]

Questions, I'm afraid:

What oracle version?
Were all sessions unresponsive?
Could you see (through Enterprise Manager or your tool of choice) any one or two sessions that were heavy CPU consumers?
Were there any batch jobs running at the time the COPU load peaked?
Have you implemented Resource Manager?

[jperusco]

Those load averages do seem high for the number of processes.

What O/S are you using?