Oracle 11g Rel 2 :- Question on Index usage

What will be the overhead on turning on usage monitoring on all the 120k + indexes in an OLTP with heavy select transactions?
Will that can be a performance bottleneck ?
We are in the process of dropping unused indexes so need to know which indexes are not at-all used. Is there some better way to get the result?
OranewAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
the overhead is very little
but, it is non-zero.

You probably won't notice but without knowing your exact usage profile, can't say for sure.


Unfortunately, monitoring simply flips a flag.   So, it'll work great for things used often as they will get the "Yes" flag turned on immediately.

Indexes that are used less frequently but are still important will appear unused, but that doesn't mean you don't need them.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
>> need to know which indexes are not at-all used.

Even though this was commented on above, I wanted to state it differently:
How are you going to determine "unused"?  

I realize this is by "monitoring" but how long are you going to monitor before you label it "unused"?

An example:
An index might only be necessary for an end-of-year closeout.  With the index, a closing might take 10 hours.  Without the index, it might take 48 hours.

What about other batch type processing that happens say, monthly, quarterly, etc...?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.