Oracle Database Discussions

It would be very simple to

set pages0
spool enable_monitoring.sql
select 'set echo on' from dual;
select 'alter index   ' || index_name || '  monitoring usage ;' 
from user_indexes
order by 1;
spool disable_monitoring.sql
select 'set echo on' from dual;
select 'alter index   ' || index_name || '  nomonitoring usage ;' 
from user_indexes
order by 1;
spool off

But enabling monitoring is not the right thing to do for at least --3-- 4 important reasons :
1. It can falsely capture an index that is being incorrectly used by the optimizer -- i.e. a sub-optimal that the optimizer selects (for whatever reasons, including bad statistics)
2. It can falsely capture an index that is used only once in one query out of a million queries that have been executed
3. It can falsely NOT capture an index that is used only on specific days -- e.g. month-end reports which require a few specific indexes not used for the 28 other days of the month
4. It can falsely NOT capture indexes that are required for constraints, simply because they are not used in queries (although they are important to maintain data consistency !)



Hemant K Chitale

Edited by: Hemant K Chitale on Jan 14, 2011 5:01 PM

Hi,

Thanks for this quick help.

My question is in the v$object_usage can be populated 7000+ rows means i am going to monitor 7000 + indexes at one shot in the view of v$object_usage ?


I understand the notes/Reasons for false ones.. but we have the time line to monitor for 3 weeks where the peak load is expected. really thanks for this notes again.

Thanks.

You could first identify those indexes that are required for 'logical' reasons:
- They support a primary-key or unique constraint
- They support a foreign-key constraint
If the database was designed 'rationally' with primary keys and foreign keys, this would probably reduce the scope of your analysis significantly.

Hi,

Today i tested but i am not seeing the object_usage is not populated... any idea ? am i missing anything here?

SQL> alter index WYX.AB_FK2 monitoring usage ;

Index altered.

SQL> select count(*) from v$object_usage;

COUNT(*)
----------
0

SQL> alter index WYX.AB_FK2 nomonitoring usage ;

Index altered.

Dear Hermant

You can add also to your list

5. It can falsely NOT capture FK indexes that are used by Oracle behind the scene to avoid the locking threat


[http://richardfoote.wordpress.com/2008/09/12/index-monitoring-and-foreign-keys-caution-kid-a/]

Best Regards

Mohamed Houri

hi,

any answers for my question.

any view exist to see the index monitoring? i understand only owner can log in and see the v$object_usage not the DBA..

any views exists at DBA level to see the usage?

Thanks.

Dear,

any answers for my question

In my opinion, you don't have to rely on this index usage monitoring in order to make a decision to drop the non used indexed as reported by this monitoring. It is the wrong path to follow.

Instead, you can look to your indexes (particularly the b-tree ones) and try to find redundant indexes. By redundant indexes I mean indexes like the following

 SELECT   table_name
        ,index_name
        ,column_name
        ,column_position
    FROM user_ind_columns
ORDER BY 
     table_name
    ,index_name;

And look to your output if you have such a kind of following situation

 table_name   index_name   column_name  column_position
   t1               ind1        col1                  1
   t1               ind2        col1                  1
   t1               ind2        col2                  2

In this situation we know that index ind1 is redundant because index ind2 contains already the col1 in position 1.

This index Ind1 is candidate to be dropped. BUT, I will advise you even in this case to be carreful when droping the ind1 Index.

It is always preferable to do not create a redundant index than to drop it afterwards.

Because there exist situations where Index ind1 might have a perfect clustering factor which makes it desirable by the Oracle Optimiser when executing one of your queries. And when you will drop it (hoping that your query will be covered by ind2), the Oracle Optimiser will go for a FULL Table scan because the High Clustering Factor of Index Ind2 makes it not desirable by the Oracle Optimiser.

So, you can
(a) locate redundant indexes
(b) drop them but test, test, test and test before implementing in PROD

Best Regards

Mohamed Houri

How much time elapsed between your first two statements (turning on monitoring and selecting from v$object_usage)? If, as appears, they were back-to-back, then it is most likely no usage was generated in the 2 seconds it took you to type the second command. It's not like the stats are already being collected.

Yes, but the OP is dealing with the hand that was dealt to him. Hindsight is always 20-20 (and hopefully instructive) but we still have to deal with the situation as it exists today.
> I've never had to wrestle with clustering factor of indexes, but I'd think that if two indexes were defined the same, but actually varied due to clustering, you'd still be safe to drop one and simply rebuild the remaining one or re-gather stats on it.

Agreed.

My concern is how to find out the usage of user level indexes as DBA?

i can see user_ind_columns...is there i can see in dba_ind_coumns?

Thanks

yes dba_* system views has all objects from db.

Dear EdStevens,

I've never had to wrestle with clustering factor of indexes, but I'd think that if two indexes were defined the same,
 but actually varied due to clustering, you'd still be safe to drop one and simply rebuild the remaining one or re-gather stats on it.

The clustering factor, I believe, will not change when you gather stats on its corresponding index. The Clustering Factor indicates how well data are packed within the index structure. If you see that the clustering factor of the index approaches the number of rows in the table then this is an indication of a bad clustering factor. If you know the value that corresponds to a good clustering factor then you can set the CF with this value using dbms_stats.set_index_stats.

But we will not rebuild an index only to generate a perfect clustering factor. And I doubt that rebuilding the index will generate a good CF at all.

I will give it a try and let you know

Best Regards

Mohamed Houri

Mohamed,

I am probably misunderstanding a little about what you wrote, and if that is the case, there is a chance that other people may also misunderstand.

Rebuilding an index will not change its true clustering factor (the estimated value for the clustering factor may change, but not if the sample size is 100%). The clustering factor desribes the order of the index entries in relation to the order of the data in the table blocks. If that is what you were stating, I apologize for misunderstanding.

Richard Foote has a little bit of information on the subject:
http://richardfoote.wordpress.com/2008/01/21/introduction-to-reverse-key-indexes-part-iv-cluster-one/

Jonathan Lewis wrote a chapter on the subject:
http://apress.com/book/downloadfile/2410

Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Charles,

Thanks for your comment. Yes you are right. I haven't been clear with my own words.

This is why I will re-phrase what I wanted to say about this thread and I will be very happy if you could correct me if I am still not getting the point

(a) do not rely on index monitoring usage to take decision of dropping reported non used indexes
(see richard foote link about the FK index usage monitoring )

(b) This is what I wanted to say about the Clustering Factor definition using Jonathan Lewis words

"The clustering factor is a single number that represents the degree to which data is randomly distributed through a table"

This is what I wanted to say also about the CF value (using richard foote words)

•“Good” CF generally has value closer to blocks in table
•“Bad” CF generally has a value closer to rows in table

(c) This is what I wanted to say about dropping redundant indexes

Be carreful when you drop a redundant indexes because you might drop the index that has a good CF "

(d) To EdStevens who said that he has never had to wrestle with clustering factor of indexes; in presence of redundant indexes he said, he will drop one index and rebuild or gather stats of the remaining index
do you agree with EdStevens suggestion?


Best Regards

Mohamed Houri

Mohamed,

Thank you for clarifying your words from the previous post - I requested the clarification just in case someone finds this thread at a later point in time through a search engine.

I am not sure how to reply to Ed Steven's comment. I think that in part it depends on what he means by "redundant indexes". If he intended to suggest that if the clustering factor is poor for an index, that the index should be rebuilt or statistics should be gathered for the index, I think that your clarified post addresses that statement.

Here is an interesting example that does show the clustering factor changing after a rebuilt, but there is a catch (read the comments carefully to see why the clustering factor changes):
http://jonathanlewis.wordpress.com/2009/10/23/quiz-night-4/

Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Hi,

Look like you using owner prefix with Index name, I presume you login using Sys or System but not by actual owner of index/table ie. WYX.

If you login using WYX and query V$object_usage , you will see data, , if you login using any other including super user, no data will be shown as V$object_usage defination usage current user(login) as filter .

Thanks
krishan

Others have already commented on the merits and otherwise of using index monitoring.

Two other places to consider for determining the extent that an index is being used are:

DBA_HIST_SQL_PLAN (assuming you have sufficient licencing), searching for which SQL plans are being used by which indexes. You can then tie the SQL_ID back to a number of other views, such as DBA_HIST_ACTIVE_SESS_HISTORY to determine the frequency of such usage. This will give you a good idea of how and when a particular index is (or not) being used.

Another useful source of information is V$SEGMENT_STATISTICS. Those indexes with very high logical read counts are likely used a lot. Those indexes with logical reads in the same ball park as db block changes are likely used less frequently and worthy of more investigation.

The golden rule I state with indexes is that it's really quite easy to create a new index. However, it's generally a lot lot harder to subsequently get rid of them :)

Cheers

Richard Foote
http://richardfoote.wordpress.com/

Use the below query if login as sys or other administrative or other user than monitoring index owner schema.

SELECT io.NAME index_name, t.NAME table_name, DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES') Monitoring,
DECODE (BITAND (ou.flags, 1), 0, 'NO', 'YES') Used, ou.start_monitoring ,
ou.end_monitoring
FROM SYS.obj$ io, SYS.obj$ t, SYS.ind$ i, SYS.object_usage ou
WHERE i.obj# = ou.obj#
AND io.obj# = ou.obj#
AND t.obj# = i.bo#;

Regards
Q.A.Rab

NB. in RAC, use gv$segment_statistics. You will see the instance level statistics and need to combine them. You will also see how work is balanced for particular indices.

Additionally monitoring will not capture situations where the optimizer uses the index stats to pick a good plan that does not include using the index on which the stats were collected.

Be very careful dropping indexes. Do it slowly, deliberately, and be prepared to recreate the index immediately if you find any of the situations we have discussed in this thread. They don't happen often ... but they do happen.

Here are some of my notes. I'm fighting with index monitoring right now, as it turns out.

The object_usage view is actually coded to show only YOUR indexes that YOU have monitored. In order for you to see them all, you need to take that DDL and remove the user condition.

You can exclude your NONUNIQUE indexes from the list of indexes you monitor. You can exclude 'all but your app user' indexes from the list of indexes you monitor. Both of which narrow down things nicely.

+"It can falsely capture an index that is being incorrectly used by the optimizer"+ - it's still being used by the optimizer, though, so it is being used, whether a performance tuning or a statistics update would disagree or not.

+"It can falsely capture an index that is used only once in one query out of a million queries that have been executed"+ - again, it's still been used. That one query could be your year-end process that will run for weeks if you don't have that index in there. How many times it's been used isn't the point - it's the fact that it HAS been used

+"It can falsely NOT capture an index that is used only on specific days"+ - that's why you make sure the monitoring period is exhaustive: it captures your entire batch cycle (week, month, year)

The way we're identifying them is to allow for an entire batch cycle (13 weeks in one case) and for all indexes which have monitoring on and have NOT been used we provide a list to the application team.

If the application team has concerns, the index gets eliminated from the list. After a period of reflection (i.e. enough time for everyone to get their say), we make the indexes invisible. This keeps them up-to-date but tells the optimizer not to consider using them unless you specifically hint at doing so.

After a reasonable time with the indexes invisible and no users screaming or catastrophe caused, drop the index, but keep the DDL in case someone, somewhere does really need it.

Mark

This doesn't go far enough; it influences the optimizer even if it isn't used. I believe both Jonathan and Richard have demo'd that. Beyond that, the more modern baselines and adaptive cursors and all that stuff favor more indices to handle changing data distributions, while good development practices favors finding some "best" plan and locking that in, and storage indices turn everything on its head.

> From the comments I've seen every time people ask about this on the forums, they really don't exhaust the possibilities; no one wants to wait more than a year, few want to put in the resources to optimize year-end performance.

> I'm glad someone Does It Right (not sarcasm, either).

> That invisible index thing seems to make all this fuss more reasonable. Edit: Except when it changes behavior over patch levels http://richardfoote.wordpress.com/2008/11/20/visible-invisible-indexes-the-invisible-band/

Edited by: jgarry on Mar 12, 2013 3:04 PM