Home » RDBMS Server » Performance Tuning » Using Index Monitoring in Oracle 9i
Using Index Monitoring in Oracle 9i [message #65090] Tue, 27 April 2004 06:38 Go to next message
staylor
Messages: 3
Registered: April 2004
Junior Member
All.

I've been racking my brain on this one...  Has anyone used the index monitoring function in 9i.  Well it only returns a 0 or a 1 to determine whether its used.  What I'm really after is detailed on how many times its used.  I've thought about writing a job that runs every 1 minute - runs through the V$OBJECT_USAGE table and increment a user table with these values.  Whilst its not an exact means it would overtime identify the hot indexes and the cold ones.  The cold ones I would then look at and determine whether I can actually delete them.

Any pointers would be fantastic.

Cheers,

Steve
Re: Using Index Monitoring in Oracle 9i [message #65091 is a reply to message #65090] Tue, 27 April 2004 21:20 Go to previous messageGo to next message
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
Senior Member
Hi,

Well according to me it works fine and it return the values YES/NO to specifu whether a particular index has been used or not. To accomplish this first enable the monitoring on an index using following command :

alter index IND_ACCPAY monitoring usage;

After that select the data from appropriate table with proper where clause which will use this index let say

select * from com_acc_pay
where dttrans = '28-apr-2004';

After that query the V$OBJECT_USAGE view, it will show whether ur index is used or not. You can check this by looking at USED column of this view.

And at end u can stop monitoring for ur index using

alter index IND_ACCPAY nomonitoring usage;

Daljit Singh.
Re: Using Index Monitoring in Oracle 9i [message #65092 is a reply to message #65091] Tue, 27 April 2004 22:25 Go to previous messageGo to next message
staylor
Messages: 3
Registered: April 2004
Junior Member
Thanks Daljit. Sorry this wasn't what I was after, I agree the process works fine but only returns a 0 or 1 (YES or NO) - I'm after the number of times the index has been used - something a little more granualar than a yes or a no.

Many thanks for the reply.

Steve
Re: Using Index Monitoring in Oracle 9i [message #65095 is a reply to message #65092] Wed, 28 April 2004 22:57 Go to previous messageGo to next message
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
Senior Member
Hi,

Sorry, i didn't catch u properly. But if u want to know the number of times ur index is used so instead of using monitoring, u should go for the audit.

Just audit that indexes i think it will tell u wht u want.

Daljit Singh
Re: Using Index Monitoring in Oracle 9i [message #65098 is a reply to message #65095] Thu, 29 April 2004 00:15 Go to previous message
staylor
Messages: 3
Registered: April 2004
Junior Member
Thanks Daljit - I'm not after the number of indexes on the system; but the number of times an index is used. so for example I run a statement it invokes an index and I want to be able to log that index has been used once. This will then increment every time an index is invoked.

Cheers,
Steve
Previous Topic: Oracle server Ram Upgrade
Next Topic: TKPROF
Goto Forum:
  


Current Time: Sat Sep 19 01:55:43 CDT 2020