Home » RDBMS Server » Performance Tuning » How much to tune
How much to tune [message #65725] Wed, 15 December 2004 20:24 Go to next message
Fahami
Messages: 22
Registered: December 2004
Junior Member
Dear all,

Everytime we face with the oracle database performance issue, we will do something to gain the perfect performance. We can run any scripts provided to check any lack from the database that cause the performance issue. And almost the suggestion is to increase let say db buffer and sga.

My questions are: Are there any formulas that can be used to calculate how much exactly we want to increase or decrease any volume of our database? Let say we got problem and the tuner recommended to increase db buffer. So how much it should be increased? How to get the good new value for that (eg db buffer) so the db performance back to normal.

 
Re: How much to tune [message #65733 is a reply to message #65725] Thu, 16 December 2004 02:35 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>And almost the suggestion is to increase let say db buffer and sga

NO.
Here You are trying to allocate more resources or
reduce the resources.
You need to analyze , identify the issue and try to fix it.
Throwing More dbBuffers / sga is not going to help with anything once it reaches a saturation point and NOT ALL ISSUES are always related to sga/buffers.
Re: How much to tune [message #65734 is a reply to message #65733] Thu, 16 December 2004 16:01 Go to previous messageGo to next message
Fahami
Messages: 22
Registered: December 2004
Junior Member
You said: NOT ALL ISSUES are always related to sga/buffers.

I just gave those two as exmaple. I know that in database tuning got many aspects to see. BUT my concern is, how much more can I tune my database if got performance problem. What are the mathematical formula to increase or decrease any part of the database. How the calculation look like?
For an EXAMPLE (just an example, the answer I want should cover everything related to oracle db tuning!): let say my Read Hit Ratio is less than 95%, let say only 70%, the suggestion given is to increase DB_BLOCK_BUFFER, so my entire question is I want the calculation, so I know how much more I must increase the db buffer. It is not limited at this point only. I also want to know how to calculate for the new value of OTHERS too if performance problem occured.
Re: How much to tune [message #65738 is a reply to message #65734] Thu, 16 December 2004 21:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Unfortunately, performance-tuning is not an exact science. It is not possible to throw some formulas at your db and get some key figures as to how fast it could go.
This is why the 3rd party performance-tools that give tips about how to increase performance very often rely on expanding hardware: more iron means more power.
If only it were so simple... but then again, that would take away the fun of the tuning ;-)

hth
Re: How much to tune [message #65739 is a reply to message #65734] Fri, 17 December 2004 01:07 Go to previous message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> let say my Read Hit Ratio is less than 95%, let say only 70%, the suggestion given is to increase DB_BLOCK_BUFFER,

It is an old myth.

First considering the Hit-ratios as a measure for performance is COMPLETELY FLAWED.
You can get any desired hit ratio you want without touching the parameters you mentioned.

Please have a look here.

http://www.orafaq.com/forum/t/23453/0/

Second,
If i see my hitratio consistenly 99% i would be definatly worried, becuase it "MAY INDICATE " some issues.

>> I also want to know how to calculate for the new
>> value of OTHERS too if performance problem occured

My question is what do you consider as "performance issue"?

Is there an statspack report or execution plan or tkprof report (or the least, the "time" factor ) OR ANY MEASURABLE statistics you have before and after the "issue" originated?

[Updated on: Fri, 18 February 2005 23:32]

Report message to a moderator

Previous Topic: Query not executing
Next Topic: Bookon tuning
Goto Forum:
  


Current Time: Wed Jan 27 13:57:45 CST 2021