Can monitoring an Index cause an overhead?

Can monitoring an Index cause an on the table..? If something like defined on the table where index in question is a partitioned index?
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.

of course!

monitoring is extra activity, the recording of that activity is extra io.

It's minimal, but non-zero.  So, yes, anything that is non-zero, is overhead

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
Rao_SAuthor Commented:
how to find out if a particular index is being used at all..?
Rao_SAuthor Commented:
if a index is not being used at all and if it exits, then just the existence of the index is an overhead..?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

-- how to find, turn on monitoring and check out

select * from v$object_usage

if you perform any dml on the indexed columns of the table then you are using the index.

also note,  an index, even if it's not queried may still be required for support of constraints.

A primary key constraint requires an index.

A foreign key constraint requires a unique index, and ideally should have an index on the constrained columns too, but, technically, isn't required.
slightwv (䄆 Netminder) Commented:
>>then just the existence of the index is an overhead..?

There is overhead in the maintenance of it.  Think about it:  Every row added to the table will need an index entry.
>>>  Every row added to the table will need an index entry.

this isn't entirely true.

only inserts of non-null indexed values, I say "values" because function-based-indexes might create nulls from non-null columns or create non-null from null columns,  The index is only populated if there is at least one non-null value to be indexed.

I apologize for any confusion of my earlier comment where I said "dml on the indexed columns"
that was a bit sloppy of me.
Adding to the already correct answer: monitoring will ONLY add a negligible overhead WHEN the index is actually used. If it is not, it won't add any overhead. And if it is used, as soon as you find out you can turn monitoring off.

Regarding your other question: if the index is never modified (read: if the indexed columns are never updated, rows with non-null indexed columns are never deleted or inserted), then it does not add any performance overhead. It does occupy a certain space (you can check how much by querying dba_segments), so if the index is not involved in querying you could say you are getting "space overhead".
Rao_SAuthor Commented:
i have attached an file ..where i was trying a query to check if N6 index is being used... let me know what you think....
Rao_SAuthor Commented:
sorry here is the file.... index-check.txt
Oops... too late! :-)

And yes, even when an index is not involved in querying it could be used to enforce a unique or primary constraint, and is required to create a foreign key pointing to those.
also of interest,  the mere existence of an index can be used by the optimizer, even if the index itself isn't part of the final plan.

The easiest place to see this would be with foreign key constraints

select * from emp
where emp.dept_id in (select dept_id from dept)

if there is a fk from emp to dept, on dept_id,  there will be an index on dept_id, but it won't be used in this query, in fact, the optimizer should completely remove the subquery from the plan as it is not needed.  This is more an attribute of the constraint than the index, but since the constraint can't exist without the index, I thought it was worth mentioning.
your plan shows the n6 index isn't used in this particularly query, but that doesn't mean it's not used somewhere else in the application
also note,  monitoring does not identify all usage either.

using the previous example,  if I have an index on emp.dept_id (not necessary, but recommended practice for FK's)

then I delete a row from dept that has children in emp with cascading deletes

The index will be used to do the cascading deletes but that usage will not show up in the monitoring.

Also, indexes may be used solely for metadata, but not really part of the plan.
Richard Foote has an excellent example of a query that does table scans with and without an index
but the plans change based on whether the index exists or not.

This sort of cardinality checking isn't picked up by the monitoring either.

Rao_SAuthor Commented:
thank you all!!!
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.