Home » RDBMS Server » Performance Tuning » How do you know whether the Index is getting used or not (Oracle 10g,Win XP)
How do you know whether the Index is getting used or not [message #409900] Wed, 24 June 2009 07:03 Go to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

How do we know whether the created index is getting used or not ?

If I create a table with Primary key,By default a unique index will be created for the primary key column. How do I know whether it is getting used or not

Regards,
Ashoka BL
Re: How do you know whether the Index is getting used or not [message #409901 is a reply to message #409900] Wed, 24 June 2009 07:07 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Look at the execution plan or the session trace.
Re: How do you know whether the Index is getting used or not [message #409902 is a reply to message #409900] Wed, 24 June 2009 07:08 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You can use Explain Plan.

Ross Leishman
Re: How do you know whether the Index is getting used or not [message #409904 is a reply to message #409900] Wed, 24 June 2009 07:12 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Thanks for the fast replies,


Do we have any data dictionery which will determine the usage ?

Can we use v$object_usage ( without altering index with "Monitoring Usage")

Re: How do you know whether the Index is getting used or not [message #409912 is a reply to message #409904] Wed, 24 June 2009 07:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can we use v$object_usage ( without altering index with "Monitoring Usage")

Yes.

Regards
Michel
Re: How do you know whether the Index is getting used or not [message #409913 is a reply to message #409900] Wed, 24 June 2009 07:28 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

But when i checked v$object_usage It doesn't have any values. untill we specify alter <indexname> monitoring usage v$object_usage will not contain values.

I want to check whether my Primary key unique index is getting used or not with out checking in v$object_usage
Re: How do you know whether the Index is getting used or not [message #409916 is a reply to message #409913] Wed, 24 June 2009 07:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
untill we specify alter <indexname> monitoring usage v$object_usage will not contain values.

Yes this is the purpose of this statement.

Quote:
I want to check whether my Primary key unique index is getting used

It is used as it guarantees the unicity. A PK is ALWAYS used. Smile

Regards
Michel

[Updated on: Wed, 24 June 2009 07:54]

Report message to a moderator

Re: How do you know whether the Index is getting used or not [message #410189 is a reply to message #409913] Thu, 25 June 2009 10:29 Go to previous message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Whether my index is being used or not:

http://www.adp-gmbh.ch/blog/2005/july/26.html

The following link would be helpful for you:

http://www.dbazine.com/oracle/or-articles/jlewis12


http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ#Does_Oracle_use_my_index_or_not.3F


Thanks

[Updated on: Thu, 25 June 2009 10:32]

Report message to a moderator

Previous Topic: Performance tuning
Next Topic: BACKUP OF HISTOGRAMS
Goto Forum:
  


Current Time: Sun Dec 04 21:02:58 CST 2016

Total time taken to generate the page: 0.08606 seconds