SEO

Hi,

If I would like to compute hit ratio for individual query by quering
v$sqlarea, is this query good?

select ( 1 - DISK_READS / (BUFFER_GETS + DISK_READS+0.0001) ) * 100,
ROWS_PROCESSED, EXECUTIONS, ELAPSED_TIME, SQL_TEXT from v$sqlarea;

Thanks in advance
Robert

Robert wrote:

> Hi,
>
> If I would like to compute hit ratio for individual query by quering
> v$sqlarea, is this query good?
>
> select ( 1 - DISK_READS / (BUFFER_GETS + DISK_READS+0.0001) ) * 100,
> ROWS_PROCESSED, EXECUTIONS, ELAPSED_TIME, SQL_TEXT from v$sqlarea;

What is an acceptable answer for the above query
& why is that value deemed correct/acceptable?

It depends.

"Good" is a relative term.

BCHR is a meaningless indicator of performance/reality.

An alternative measure would be number of sun spots per day.

fine anna ...

I do not need any philosophical answer. My question is simple. Is the above
query suitable for measuring "individual query cache hit ratio" or not. So
if you know the answer please say it and do not talk about the sun spots.
The reason why I`m asking is that I`m not familiar with many of the Oracle
dynamic views and your answer did not helpd me to uderstand them more :-)

Regards
Robert

"Robert" <grobert@azet.sk> wrote in message
news:bq6ppa$l62$1@news.telecom.sk...
> fine anna ...
>
> I do not need any philosophical answer. My question is simple. Is the
above
> query suitable for measuring "individual query cache hit ratio" or not. So
> if you know the answer please say it and do not talk about the sun spots.
> The reason why I`m asking is that I`m not familiar with many of the Oracle
> dynamic views and your answer did not helpd me to uderstand them more :-)

Its a reasonable stab at it. There might be some stats for queries that do
not show up in v$sqlarea for example sort direct writes I don't *think* is
included in the disk stats.

What lies behind Anna's reply is the realization that the hit ratio is an
(almost) useless measure however you calculate it. If it changes
dramatically it is probably a clue that something has changed with your
database - unfortunately the direction of change of the statistic doesn't
tell you whether performance has improved or worsened.



--
Niall Litchfield
Oracle DBA
Audit Commission UK

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 ...

"Robert" <grobert@azet.sk> wrote in message
news:bq7i0u$sjk$1@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

OK. I will try what you have sugested. Seems it is better idea.

Thanks a lot
Robert

"Robert" <grobert@azet.sk> wrote in message news:<bqerou$ccb$1@news.telecom.sk>...
> OK. I will try what you have sugested. Seems it is better idea.
>
> Thanks a lot
> Robert

typically the poorest running queries, the ones that cause a slowdown
have the highest cache/hit ratio or have a ton of sorting and your
sort_area_size is too small.

Its typically the first one. you will often have a small number of
VERY bad queries that do tons of logical I/Os. This causes contention
on the latches in the buffer cache and slows everything else down.

when you run statspack, you can get the most resource intensive
queries. Niall's method of contacting users is a good idea, but you
should also run statspack. it may be a few really bad queries slowing
everything down. Statspack will help to identify them.