Home » RDBMS Server » Performance Tuning » Index Hit Retio (Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production)
Index Hit Retio [message #448938] Thu, 25 March 2010 22:39 Go to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
How to calculat/Get Index hit ratio ?

Is follwing query is right to get same ?

SQL> SELECT namespace ,(100*gethitratio ) hit_ratio    FROM v$librarycache;

NAMESPACE        HIT_RATIO
--------------- ----------
SQL AREA        50.3694248
TABLE/PROCEDURE 99.9740047
BODY            99.1189027
TRIGGER         99.9983301
INDEX           97.1096594
CLUSTER         99.9637897
OBJECT                 100
PIPE                   100
JAVA SOURCE            100
JAVA RESOURCE          100
JAVA DATA              100

11 rows selected.

Re: Index Hit Retio [message #448940 is a reply to message #448938] Thu, 25 March 2010 22:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Any hit ratio is a mythical indicator of performance & can be safely ignored.
Re: Index Hit Retio [message #448947 is a reply to message #448940] Thu, 25 March 2010 23:10 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
BlackSwan wrote on Thu, 25 March 2010 22:48
Any hit ratio is a mythical indicator of performance & can be safely ignored.


but it must be indicating something.
Re: Index Hit Retio [message #448949 is a reply to message #448947] Thu, 25 March 2010 23:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>but it must be indicating something.
It is a number.
If you think its value is meaningful, you are entitled to your opinion.

If it provides meaningful information, then please state the value
where it changes from "good" to "bad" meaning.
Re: Index Hit Retio [message #448956 is a reply to message #448938] Fri, 26 March 2010 00:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Define "index hit ratio".

Regards
Michel

[Updated on: Fri, 26 March 2010 00:25]

Report message to a moderator

Re: Index Hit Retio [message #449017 is a reply to message #448938] Fri, 26 March 2010 04:49 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Number which show the index being used n times !!
Re: Index Hit Retio [message #449021 is a reply to message #449017] Fri, 26 March 2010 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
v$object_usage
v$segment_statistics

Regards
Michel

[Updated on: Fri, 26 March 2010 04:57]

Report message to a moderator

Re: Index Hit Retio [message #449033 is a reply to message #448938] Fri, 26 March 2010 05:57 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Thanks You Michel.
Quote:

Define "index hit ratio".

Regards
Michel



BTW I presume , on this Forum if YOU dont know then that does not exists in Oracle Smile and YES it is true to great extent.


I have used follwing Query.

col c1 heading 'Object|Name' format a30
col c2 heading 'Operation' format a15
col c3 heading 'Option' format a15
col c4 heading 'Index|Usage|Count' format 999,999
break on c1 skip 2
break on c2 skip 2

select
   p.object_name c1,
   p.operation c2,
   p.options c3,
   count(1) c4
from
   dba_hist_sql_plan p,
   dba_hist_sqlstat s
where
   p.object_owner <> 'SYS'
and
   p.operation like '%INDEX%'
and
   p.sql_id = s.sql_id
group by
   p.object_name,
   p.operation,
   p.options
order by
   1,2,3;



Re: Index Hit Retio [message #449116 is a reply to message #449017] Fri, 26 March 2010 15:10 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
rahulvb wrote on Fri, 26 March 2010 02:49
Number which show the index being used n times !!


OK, you have a number.
Please elaborate on how, when, where & why this number can & should be used.
Previous Topic: Swap Partition or Recreate Synonym
Next Topic: sql tuning
Goto Forum:
  


Current Time: Sat Dec 07 00:43:54 CST 2024