| Issue of low buffer hit ratio [message #110626] |
Wed, 09 March 2005 04:20  |
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  |
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 |
 |
Mahesh Rajendran
Messages: 10708
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
