Home » RDBMS Server » Performance Tuning » Issue of low buffer hit ratio
Issue of low buffer hit ratio [message #110626] Wed, 09 March 2005 04:20 Go to next message
mustafaisonline
Messages: 2
Registered: March 2005
Location: Pakistan
Junior Member
Hello everyone,

I need very quick response on this topic.

The issue is this that:

They were running there database(8.1) and VB application, it was running greate. Then they:
1. Added one new column and made an index on it.
2. They changed their subnets.

After doing this there performance went very low. So they upgraded to 9i with very high tech hardware. When they ran the database from SQL, the query took 0.01(ms) to execute it. But when they ran the application it took 7 minutes to run. The buffer hit ratios was 100% when query was run but when users came on the system it went down to 13-14% which is disastrus.
We increased the sizes of caches etc etc but still the hit ratio is not up.

Is it a totally application side issue that due to application
connecting with DB the system got down but dear it was going greate in past before doing two things which i have already mentioned about?

Please give suggestions

Waiting despirtely
Re: Issue of low buffer hit ratio [message #110630 is a reply to message #110626] Wed, 09 March 2005 04:36 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Quote:

After doing this there performance went very low. So they upgraded to 9i with very high tech hardware

Seems to me there are more differences then only the two you mention.
I reckon that they will be using the new column too, meaning they changed their code as well...

The way to the solution will be to track down the problem query/queries. To do this, trace a session and use tkprof to see if there are any queries that look suspicious.
Use statspack. Analyze the statspack reports to zoom in on the actual problem.

Was the new index analyzed? Is the new index used when you do not want it?

Lots of (nice) work for you to do!
Please come back whenever you know more about the problem, and we'll gladly (try to) help you out.

hth
Re: Issue of low buffer hit ratio [message #110634 is a reply to message #110626] Wed, 09 March 2005 04:56 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>The buffer hit ratios was 100% when query was run but when users came on the system it went down to 13-14% which is disastrus.
We increased the sizes of caches etc etc but still the hit ratio is not up.

Just to add what Frank has said.
Considering buffer hit ratio's as a measure for tuning is completely flawed. You can increase the ratio programmatically to any value you want. Increasing buffer cache / hardware is NOT a solution.

Please have a look here. Connor Mcdonald & Jonathan lewis can prove
this.
http://www.orafaq.com/forum/m/65488/42800/#msg_65488.

[Updated on: Wed, 09 March 2005 04:57]

Report message to a moderator

Previous Topic: Results TRACE
Next Topic: URGENT:-Performance Tuning a query
Goto Forum:
  


Current Time: Thu Mar 28 07:10:18 CDT 2024