jump to navigation

Index Monitoring and Foreign Keys – Caution (Kid A) September 12, 2008

Posted by Richard Foote in 11g, Constraints, Foreign Keys, Index Monitoring.

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’;

------------ --- ---

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            |         |
|*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’;

------------ --- ---

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’;

------------ --- ---

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.

          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’;

------------ --- ---

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.

          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 …


1. Tony - September 12, 2008

Great example. I would consider this a bug, but I doubt the behavior will ever change.


2. Pete Scott - September 12, 2008

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…


3. Richard Foote - September 13, 2008

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.


4. CJ - September 15, 2008

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.


5. SeánMacGC - September 16, 2008

Interesting indeed Richard, and well worth the knowing. Thanks.


6. Kash - September 16, 2008

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.


7. Richard Foote - September 16, 2008

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.


8. CJ - September 17, 2008

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.


9. Richard Foote - September 22, 2008


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.


10. Book Review: Oracle SQL Recipes: A Problem – Solution Approach « Charles Hooper's Oracle Notes - June 7, 2010

[…] 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] […]


11. SQL Server VS Oracle: Index Monitoring | 140,000 Characters or Less - March 28, 2011

[…] 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) […]


12. arun bharat kaushik - September 3, 2014

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



Richard Foote - September 3, 2014

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 😦


arunkaushik - September 8, 2014

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.


13. torrent - September 29, 2019

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: