We help IT Professionals succeed at work.

Can monitoring an Index cause an overhead?

Rao_S
Rao_S asked
on
Can monitoring an Index cause an on the table..? If something like this..is defined on the table where index in question is a partitioned index?
ALTER INDEX XXXXX01.XQT_IT_YYYYYY_SEQ_N7
  MONITORING USAGE;
Comment
Watch Question

Most Valuable Expert 2011
Top Expert 2012
Commented:
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

Author

Commented:
how to find out if a particular index is being used at all..?

Author

Commented:
if a index is not being used at all and if it exits, then just the existence of the index is an overhead..?
 
Most Valuable Expert 2011
Top Expert 2012
Commented:
-- 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.

Most Valuable Expert 2011
Top Expert 2012
Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2019
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.
Most Valuable Expert 2011
Top Expert 2012
Commented:
>>>  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".

Author

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....

Author

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.
Most Valuable Expert 2011
Top Expert 2012
Commented:
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.
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
Most Valuable Expert 2011
Top Expert 2012
Commented:
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.

http://richardfoote.files.wordpress.com/2008/09/11g-join-multi-column-demo.pdf

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


Author

Commented:
thank you all!!!