Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: individual query hit ratio
"Robert" <grobert_at_azet.sk> wrote in message
news:bq7i0u$sjk$1_at_news.telecom.sk...
> Thanks a lot ...
>
> we have SAP running on oracle and there are some quite big performance
> problem. Even the "global" cache hit ratio is about 92%. I was measuring
the
> query hit ratio and I found lots of queries (50-80)which shows hit ratio
at
> about 7-8%. I was unsure if the given query is suitable. I simply had to
> start somewhere. Also other performance params like wait events and some
> more indicates good performance shape, but thers is "some" problem.
>
> anyway thank you ...
If you can identify (or even better the business users can identify) the problem processes, then a much, much better approach is to trace an example session with sql trace at level 8 and use tkprof to determine which queries are causing your specific problems. If your query with a hit ratio of 7% completes in 5ms it probably isn't an issue - if a query with a hit ratio of 99.99% takes 12 hours it is likely to be a problem. The big advantage of using sql trace in this way is that you can tell which queries consume the most time, and what they were waiting for if it is a wait issue as opposed to a sql optimisation issue.
the big disadvantage of using the hit ratio as you describe is that it is essentially looking at the wrong thing. I should know I adapted an in-house tool to do exactly what you describe. it didn't work :(.
-- Niall Litchfield Oracle DBA Audit Commission UKReceived on Fri Nov 28 2003 - 07:48:21 CST