Index Monitoring and Foreign Keys – Caution (Kid A) September 12, 2008
Posted by Richard Foote in 11g, Constraints, Foreign Keys, Index Monitoring.trackback
One of the questions asked of indexes is are they actually being used or are they sitting there looking pretty not being used, wasting valuable resources and storage.
One possible method of determining whether an index is being used is to set the MONITORING USAGE attribute of the index. This will place the index in the v$object_usage view and will flag the index as being used when indeed the Cost Based Optimizer decides to “use” the index in an execution plan.
For example:
SQL> create table daddy (id number constraint daddy_pk primary key, name varchar2(20));
Table created.
SQL> insert into daddy values (1, ‘BOWIE’);
1 row created.
SQL> insert into daddy values (2, ‘ZIGGY’);
1 row created.
SQL> insert into daddy values (3, ‘THIN WHITE DUKE’);
1 row created.
SQL> commit;
Commit complete.
SQL> alter index daddy_pk monitoring usage;
Index altered.
If we look at the v$object_usage view, we’ll now see an entry for this index, with the USED flag currently set to NO:
SQL> select index_name, monitoring, used from v$object_usage where index_name = ‘DADDY_PK’;
INDEX_NAME MON USE ------------ --- --- DADDY_PK YES NO
If we however make the CBO “use” this index:
SQL> select * from daddy where id = 1;
ID NAME ---------- -------------------- 1 BOWIE -------------------------------------------- |Id| Operation | Name | -------------------------------------------- | 0| SELECT STATEMENT | | | 1| TABLE ACCESS BY INDEX ROWID| DADDY | |*2| INDEX UNIQUE SCAN | DADDY_PK| --------------------------------------------
We now notice that the index is now marked as having been used:
SQL> select index_name, monitoring, used from v$object_usage where index_name = ‘DADDY_PK’;
INDEX_NAME MON USE ------------ --- --- DADDY_PK YES YES
Although it doesn’t give any indication on just how often the index is actually used, if an index is still marked as unused for a period of time, it does provide some indication that the index has not been used during that time and is a candidate for being dropped.
Or does it ?
Unfortunately, the are some cases when an index is being used but monitoring the index doesn’t give any such indication. However dropping such indexes can prove disastrous …
For example, let’s create another table that has a Foreign Key which references this DADDY table:
SQL> create table kiddie (id number, name varchar2(20), fk number, constraint kiddie_fk foreign key(fk) references daddy(id));
Table created.
Let’s now populate it with a number of rows:
SQL> insert into kiddie select rownum, ‘MAJOR TOM’, 1 from dual connect by level <= 1000000;
1000000 rows created.
SQL> commit;
Commit complete.
Let’s now create an index on the Foreign Key column and collect some stats:
SQL> create index kiddie_fk_i on kiddie(fk);
Index created.
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’DADDY’, estimate_percent=>null, cascade=>true, method_opt=> ‘FOR ALL COLUMNS SIZE 1′);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’KIDDIE’, estimate_percent=>null, cascade=>true, method_opt=> ‘FOR ALL COLUMNS SIZE 1’);
PL/SQL procedure successfully completed.
Let’s now monitor this index on the Foreign Key and see where it may or may not get used:
SQL> alter index kiddie_fk_i monitoring usage;
Index altered.
SQL> select index_name, monitoring, used from v$object_usage where index_name = ‘KIDDIE_FK_I’;
INDEX_NAME MON USE ------------ --- --- KIDDIE_FK_I YES NO
OK, let’s now delete a row from the parent table and look at the statistics to see how many consistent reads were performed:
SQL> delete daddy where id = 2;
1 row deleted.
Statistics --------------------------- 8 db block gets 26 consistent gets 1 physical reads 1 rows processed
Now, behind the scenes, Oracle indeed used the index on the Foreign Key to determine whether or not the parent row could be safely deleted or whether there were child records that were still referencing the parent row. Note we used just 26 consistent reads.
Let’s see if the monitoring of the index has picked this up …
SQL> select index_name, monitoring, used from v$object_usage where index_name = ‘KIDDIE_FK_I’;
INDEX_NAME MON USE ------------ --- --- KIDDIE_FK_I YES NO
Ooops, this is not good. Is this index really not being used, is it really perhaps safe to drop.
Let’s find out:
SQL> drop index kiddie_fk_i;
Index dropped.
Let’s now delete another parent row and see if we notice a difference in performance:
SQL> delete daddy where id = 3;
1 row deleted.
Statistics ---------------------------- 7 db block gets 3194 consistent gets 1599 physical reads 1 rows processed
Ooops, the consistent gets have jumped up dramatically from 26 to a massive 3194 !! Without the index on the Foreign Key, the only way now for Oracle to check whether it’s OK to delete a parent row is to perform a Full Table Scan on the child table. Not only is this potentially very costly but there could be some nasty locking implications as well.
Index Monitoring only tells you if an index has been used or if it hasn’t been used by the CBO.
It doesn’t however tell you whether an index has actually been used or whether it’s safe to drop the index.
More examples of when it may not be safe to drop a so called unused index to come …
Great example. I would consider this a bug, but I doubt the behavior will ever change.
LikeLike
Off topic (as I always am!) there is also the ‘use’ of the index by the CBO in deciding not to use it! – I have seen the presence of a non-unique index allow the CBO to use a better estimate of ROWS and (probably) COST than without the index but still go ahead with the FTS approach…
LikeLike
Tony, it’s a possible idea for a future enhancement 😉
Pete, absolutely. I’ll next give a nice example of just such a thing, with an 11g flavour.
LikeLike
As of 10g, another ‘problem’ with index monitoring is that if you gather statistics on an index, the ‘USED’ column of an index is set to ‘YES’. This makes the index monitoring feature most difficult to use. Not sure if 11g has fixed this or not.
LikeLike
Interesting indeed Richard, and well worth the knowing. Thanks.
LikeLike
Sometimes I find that useful information can be garnered from v$segment_statistics. I ran your example and after deleting from the parent the value of ‘physical reads’ on KIDDIE_FK_I increased (only by 2 though). Monitoring statistics in this view, for a period of time, can be useful.
LikeLike
CJ, you’ve attended my Index Internals Seminar as it’s a point I mention on the second day !!
Sean, no worries 🙂
Kask, indeed I use it to investigate and determine the “usage” of indexes.
LikeLike
Hi Richard,
I hope I will get an opportunity to attend your seminar one day.
I should have asked you if you know of any workarounds to this behaviour. I have come upon about two possible workarounds (which are definitely not foolproof):
1. Take frequent (probably daily) snapshots of v$object_usage table (thanks to HJR). If the stats aren’t gathered too regularly or using the staleness check, the index will show up as unused in lot of snapshots.
2. Turn off monitoring before gathering stats and turn on immediately after it is done. This again depends on what your stats gathering strategy is.
If only Oracle recorded the ‘LAST_USED’ timestamp with v$object_usage, it could have been compared to the ‘last_analyzed’ timestamp to identify such cases. Your thoughts please.
LikeLike
Hi CJ
My thoughts are basically that it’s quite usual to want to keep monitoring indexes past the periodic periods one would ordinarily collect stats.
If you do, then simply record which indexes have already been “used” (perhaps in a table somewhere) and refresh the monitoring of those that haven’t.
LikeLike
[…] Recipe 20-8 suggests an approach to identify those indexes that are safe to drop by enabling monitoring of the indexes. One of the problems with this approach is that the index may still be used by the cost-based optimizer even when monitoring reveals that the index is never used. [Reference/Test Case – Check the Comments Section Also] [Reference/Test Case – Check the Comments Section Also] […]
LikeLike
[…] And there’s always a big BUT Richard Foote (Twitter) blogs on how this data can be very misleading as the optimizer can use the index for building plans yet not have the index marked as used. Even worse, updating STATS on the index automatically causes it to be flagged. (Blog) […]
LikeLike
Yes Index monitoring only does not give clear idea about that index was used or not.
but we can identify that index was used or not and by which statements. See my blog
http://arunkaushikoracle.blogspot.in/2014/02/how-to-check-index-usage-specially-when.html
LikeLike
Hi Arun
Yes indeed, with the restriction that the data is only available for the life of the instance and the history data only if you have the Diagnostics Pack.
I do very very much disagree though with your comment on changing the block size of a table as a general better alternative to creating a viable index 😦
LikeLike
Its specific to some situation only .. my mistake should be more specific here.
In a project we had around 200 columns , and table is relatively small in terms of data , so here using the block size of 16k benefited us greatly.
LikeLike
Nice post. I learn something totally new and challenging on blogs I stumbleupon every day.
It’s always helpful to read articles from other writers and use a little something from other web
sites.
LikeLike