Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: individual query hit ratio

Re: individual query hit ratio

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 28 Nov 2003 13:48:21 -0000
Message-ID: <3fc75225$0$9392$ed9e5944@reading.news.pipex.net>


"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 UK
Received on Fri Nov 28 2003 - 07:48:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US