| Using Index Monitoring in Oracle 9i [message #65090] |
Tue, 27 April 2004 06:38  |
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  |
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 |
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 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
