Home » RDBMS Server » Performance Tuning » How to reduce CPU Utilization (10.2.0.2.0 ,windows)
How to reduce CPU Utilization [message #429139] Mon, 02 November 2009 05:27 Go to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member


Hi all,

How to reduce the CPU utilization of a server when it exceeds its maximum limit?

I see , out of 300 processes , 297 sleeping and rest 3 CPU's were active.

In this case how to reduce the CPU utilization?

Regards,
Re: How to reduce CPU Utilization [message #429142 is a reply to message #429139] Mon, 02 November 2009 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A server never exceeds its limit of 100% CPU utilization.
A server should always run at almost 100% of utilization every time.

Regards
Michel
Re: How to reduce CPU Utilization [message #429145 is a reply to message #429142] Mon, 02 November 2009 05:47 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
"A server never exceeds its limit of 100% CPU utilization"


Exceeds its limit means say 80% is the threshold it should alert saying database
has crossed 80% of its limit.I meant this.

Regards,
Re: How to reduce CPU Utilization [message #429157 is a reply to message #429145] Mon, 02 November 2009 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Exceeds its limit means say 80% is the threshold it should alert saying database
has crossed 80% of its limit.I meant this.

Why? A CPU is made to run at 100%, why do you want to limit it to 80%? You then waste 20% of its power.

Regards
Michel
Re: How to reduce CPU Utilization [message #429160 is a reply to message #429157] Mon, 02 November 2009 06:18 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
i am not limiting it to 80%.I set it to 80% for an alert so that we can resolve the issue
by the time it reaches 80% of its CPU usage.

"A CPU is made to run at 100%"

When a CPU is made to run at 100% , i suspect the database will be hanged.


Regards,
Re: How to reduce CPU Utilization [message #429177 is a reply to message #429160] Mon, 02 November 2009 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you are trying to solve a problem that does not exist.

Regards
Michel
Re: How to reduce CPU Utilization [message #429185 is a reply to message #429177] Mon, 02 November 2009 08:07 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member


Not like that , im trying to take some initiatives before the problem goes critical.


Regards,

[Updated on: Mon, 02 November 2009 08:09]

Report message to a moderator

Re: How to reduce CPU Utilization [message #429188 is a reply to message #429185] Mon, 02 November 2009 08:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you don't check the correct indicator. It is the load queue you have to check not the cpu utilization.

Regards
Michel
Re: How to reduce CPU Utilization [message #429238 is a reply to message #429188] Mon, 02 November 2009 12:22 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member


How to check load queue in database ?


Regards,
Re: How to reduce CPU Utilization [message #429239 is a reply to message #429238] Mon, 02 November 2009 12:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Load queue, as well as cpu utilization, is not a database indicator, it is an OS one.
It measures the number of processes asking for the processor(s) at the same time.

Regards
Michel
Re: How to reduce CPU Utilization [message #429343 is a reply to message #429239] Tue, 03 November 2009 05:08 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member



Thanks.

Re: How to reduce CPU Utilization [message #429796 is a reply to message #429139] Thu, 05 November 2009 17:18 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Another thought on the general topic:

CPU as related to Oracle is almost always highly correlated to buffer gets. Buffer gets are in turn almost always correlated to index usage and joins. If your goal overall is to reduce CPU consumed by oracle sql execution, then tune your sql to reduce buffer gets. The two most common things this means are:

1) look for sql that has high buffer gets, then create indexes to support joins and thus change the query plan to a more efficient plan.

2) make sure your statistics are up-to-date, including index stats so oracle can also do the opposite of #1, which is to say, so it can decide when not to use indexes to support joins but instead go with FTS and Hash Join when appropriate. This too will result in a more efficient plan and thus reduced buffer gets and thus reduced CPU. You will need the necessary hash settings to enable hashing (I do not recall at the moment what these are but you can google or read the docs).

In the end, to reduce CPU resulting from Oracle SQL execution means to do basic tuning with an emphasis on reduction of buffer gets.

Good luck, Kevin

[Updated on: Thu, 05 November 2009 17:19]

Report message to a moderator

Previous Topic: Tune the code without autotrace+ dictionary - fed up with DBA advice (merged 3)
Next Topic: lock issue
Goto Forum:
  


Current Time: Sat Dec 03 14:10:09 CST 2016

Total time taken to generate the page: 0.16867 seconds