OraFAQ Forum: Performance Tuning » How do you know whether the Index is getting used or not

Home » RDBMS Server » Performance Tuning » How do you know whether the Index is getting used or not (Oracle 10g,Win XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How do you know whether the Index is getting used or not [message #409900]

Wed, 24 June 2009 07:03

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

Report message to a moderator

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

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Look at the execution plan or the session trace.

Report message to a moderator

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

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

You can use Explain Plan.

Ross Leishman

Report message to a moderator

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

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")

Report message to a moderator

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

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

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

Yes.

Regards
Michel

Report message to a moderator

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

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

Report message to a moderator