Skip to Main Content
  • Questions
  • Unused Index(s) for a specific Time?

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, Sikandar.

Asked: June 17, 2002 - 10:42 pm UTC

Answered by: Tom Kyte - Last updated: June 25, 2016 - 3:05 am UTC

Category: Database - Version: 8.1.7

Viewed 10K+ times! This question is

Whilst you are here, check out some content from the AskTom team: Up to date text indexes

You Asked

Hi Tom,
I want to drop those indexes which are not used in the last 2 months so to reclaim space. For example

There are two indexes on scott.emp on the empno and the other is on deptno. While the application is only using empno index but no use of deptno index. After getting the info I will drop the deptno index and if required will recreate the index.

Any script or any idea to get the above required info?

Regards,
Sikandar

and we said...

No in 8i -- but in Oracle9i -- this is trivial. You can "alter index monitoring" to capture statistics about whether an index has been used in a query or not.



and you rated our response

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

What about this script?

July 01, 2002 - 5:47 am UTC

Reviewer: Sikandar Hayat Awan from Pindi Bhattian - Pakistan

Tom Kyte

Followup  

July 01, 2002 - 7:36 am UTC

useless for this purpose.


Think about it. If you insert a row into a table, what'll happen -- every index on that table will be updated. Now, did you use those indexes? (yes, in a way). But -- did you REALLY use those indexes? (no, you didn't).

You need to figure out how to determine whether you used an index as an access path or just maintained it. That script shows you the indexes that are maintained as well as used as access paths (and you cannot tell the difference between the two)

What about this script?

July 01, 2002 - 5:47 am UTC

Reviewer: Sikandar Hayat Awan from Pindi Bhattian - Pakistan

set serverout on size 1000000
set verify off

column owner format a20 trunc
column segment_name format a30 trunc

spool bufferidx.lst

select distinct b.owner, b.segment_name
from x$bh a, dba_extents b
where b.file_id=a.dbarfil
and a.dbablk between b.block_id
and b.block_id+blocks-1
and segment_type='INDEX' and b.owner not in ('SYS','SYSTEM')
/

spool off

</code> http://www.think-forward.com/sql/bufferidx.htm <code>

Tom Kyte

Followup  

July 01, 2002 - 7:36 am UTC

see above.

Your comments...

July 18, 2002 - 11:21 am UTC

Reviewer: Sikandar Hayat Awan from Pindi Bhattian - Pakistan

For your comments on the following,

Mining Gold from the Library
By Donald K. Burleson

</code> http://www.oracle.com/oramag/oracle/00-nov/index.html?o60dba.html <code>

Tom Kyte

Followup  

July 18, 2002 - 1:38 pm UTC

If you have my book -- I show a very very similar (but much more automated) method that uses query plan stability. We automagically capture all of the plans for you with query plan stability.

It works -- but it is very cumbersome. Less cumbersome then the above referenced article but still....

I'll share a bit of it here from the book:

....
To See the Indexes Used

This is not actually one of the intended uses of stored outlines, more of a side effect – but, hey, it works! A frequently asked question is 'I have lots and lots of indexes in my database and I'm sure some of them are not being used, but I'm not sure which ones. How can I tell?' Well, one way is via the stored outlines – they'll list the name of every index they use in a query access plan. If you use an ON LOGON trigger to enable automatic outline generation, run your system for a while, and then disable it – you'll have a fairly inclusive list of what indexes are used in the system (and by which queries). As we see below, all of the 'hints' that stored outlines use are stored in a data dictionary table. It becomes very easy to see what indexes are used (and by what queries) and which are not. For example, using the output of our two examples above, we can see which queries use the index EMP_PK we have created in our database via:

tkyte@TKYTE816> select name, hint
2 from user_outline_hints
3 where hint like 'INDEX%EMP_PK%'
4 /

NAME HINT
--------------- --------------------
MYOUTLINE INDEX(EMP EMP_PK)
FIRST_ROWS_EMP INDEX(EMP EMP_PK)

We can use the NAME column from this query to go back to the actual SQL query stored in USER_OUTLINES, to see the original query text that is making use of this index.
.........................




about your book

July 18, 2002 - 11:23 pm UTC

Reviewer: Sikandar Hayat Awan from Pindi Bhattian - Pakistan

Dear TOM Your book in not available in Pakistan. I have check on www.amazon.com and it is $49+shipment+taxes and it is about $60+. Very expensive due to conversion. I wish that your book will be available in Pakistan very soon.

Currently I don't have your script/book so can't tell you about it. While the script available in the above mentioned Article is under my testing.


Tom Kyte

Followup  

July 19, 2002 - 7:22 am UTC

Yes it is -- I've recieved the royalties.

Take the ISBN, goto a reputable bookstore, and they should be able to order it.

As a followup to the prov followup, here is a little more detail:

...
We could have used an ON LOGON database trigger, such as the following, to achieve the same result:

tkyte@TKYTE816> create or replace trigger tkyte_logon
2 after logon on database
3 begin
4 if ( user = 'TKYTE' ) then
5 execute immediate
6 'alter session set use_stored_outlines = hr_application';
7 end if;
8 end;
9 /

Trigger created.

You need the privileges CREATE TRIGGER and ADMINISTER DATABASE TRIGGER in order to create a LOGON trigger. Additionally, the owner of this trigger needs the ALTER SESSION privilege granted directly to them, rather than via a role.
..........


Line 4 is something you would think about -- you probably don't need it -- you want to generate outlines for every user. Once you have that, the outlines will be generated and you can use queries againt the document OUTLINE tables to see what hints get placed in there and see what indexes the hints use.

Play with it -- try the alter session command in sqlplus, run some queries, disable the query plan generation and query the resulting tables to see what information you get.

Excelent

July 19, 2002 - 11:36 am UTC

Reviewer: Sikandar Hayat Awan from Pindi Bhattian - Pakistan

SYSTEM > ALTER SESSION SET CREATE_STORED_OUTLINES = true;

Session altered.

SYSTEM > select * from scott.emp;
SYSTEM > select * from scott.emp where empno = 7566;

SYSTEM > select name, hint
from user_outline_hints
where hint like 'INDEX%';
NAME HINT
------------------------------ ------------------------------
SYS_OUTLINE_020719211705845 INDEX(EMP EMP_PRIMARY_KEY)
SYS_OUTLINE_020719211713085 INDEX(O OL$NAME) <<<<<< ?
SYS_OUTLINE_020719211914610 INDEX(O OL$NAME)

after some other queries
NAME HINT
------------------------------ -------------------------------
SYS_OUTLINE_020719211705845 INDEX(EMP EMP_PRIMARY_KEY)
SYS_OUTLINE_020719211713085 INDEX(O OL$NAME)
SYS_OUTLINE_020719211914610 INDEX(O OL$NAME)
SYS_OUTLINE_020719212026173 INDEX(DTS)
SYS_OUTLINE_020719212026173 INDEX(TTS)
SYS_OUTLINE_020719212026173 INDEX(U I_USER1)
SYS_OUTLINE_020719212026303 INDEX(I I_IND1)
SYS_OUTLINE_020719212026303 INDEX(OBJAUTH$ I_OBJAUTH2)
SYS_OUTLINE_020719212026303 INDEX(O I_OBJ2)
SYS_OUTLINE_020719212026303 INDEX(U I_USER1)
SYS_OUTLINE_020719212041435 INDEX(EMP EMP_EMP_NO_PK)
SYS_OUTLINE_020719212050458 INDEX(O OL$NAME)

SYS_OUTLINE_020719212050458 << this is because I have not created outline?

Also reading the following link,
</code> http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a87503/outlines.htm <code>

very good idea to use outlines gathering in 8i to monitor indexes using

March 02, 2003 - 11:14 am UTC

Reviewer: MarkMal from Canada


Script to find unused index

October 16, 2006 - 10:38 am UTC

Reviewer: Nikhil

Hello Tom,
How to find all the unused indexes in Oracle 10g?
Thanks


Tom Kyte

Followup  

October 16, 2006 - 11:10 am UTC

define "unused" :)


you can set the index to monitoring and query the associated v$ table to see if it was used as an ACCESS METHOD while the instance has been up.

It can do a "first level" sweep for you, a human being with knowledge must then verify it is "not used" (eg: could be used, just not right now, could be used, but not as an access method - rather as part of an integrity constraint, could be used, but not as an access method or integrity constraint but because of concurrency reasons (unindexed forieng keys))

Thanks for the reply

October 17, 2006 - 2:20 am UTC

Reviewer: Nikhil

Hello Tom,
Thanks for the reply. By unused, I meant not all used till now.

I was looking at DBA_HIST_SQL_PLAN contents. Can this query be used to get those indexes which are not used till now?

select distinct table_name, index_name
from dba_ind_columns
WHERE TABLE_OWNER <> 'SYS'
and index_name not in (
select distinct p.object_name c1
from dba_hist_sql_plan p,
dba_hist_sqlstat s
where p.object_owner = 'SYS'
and p.operation like '%INDEX%'
and p.sql_id = s.sql_id);

Also you were referring to v$ views. Can you let me those?

Thanks

Tom Kyte

Followup  

October 17, 2006 - 4:27 am UTC

right - but what does NOT ALL USED mean??

you can use that to get an IDEA, one that must be validated by a HUMAN BEING since they might be there for integrity constraints, concurrency, a report that hasn't run in a bit

Also, best to add "and p.objectg_name is not null" to the subquery when using IN.  for performance and for "correctness"

ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2> insert into t values ( null );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select ename
  2    from scott.emp
  3   where sal not in ( select * from t );

no rows selected

ops$tkyte%ORA10GR2> select ename
  2    from scott.emp
  3   where sal not in ( select * from t where x is not null);

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.


since it is not known that object_name is not null in the result - the optimizer will be limited in choice of plan (hence - for performance) and if a null does come into play - it'll result in zero rows in all cases.


v$sql_plan was the v$ view 

Tracking index usage with dba_hist_sql_plan and dba_hist_sqlstat

October 27, 2008 - 7:54 am UTC

Reviewer: Yuri Gorelik from Israel

Hello Tom,
I'm trying to track index usage with dba_hist_sql_plan and dba_hist_sqlstat. The sql_plan table seem to contain all the sqls that have been executed on the system since ever, and the timestamp column contains the date of the first time that this sql plan was produced. But dba_hist_sqlstat only has sqls that meet certain criteria.
It means that wehn I find sqls which use a specific index, I can't establish when was the last time that the plan was produced and can't use the snap_id and *delta columns of dba_hist_sqlstat to figure out when and how much times this sql was executed.

1. Am I correct in what I'm saying or am I missing something?
2. Is there a way to configure the criteria for dba_hist_sqlstat?

Thanks,
Yuri
Tom Kyte

Followup  

October 27, 2008 - 8:45 am UTC

it'll contain most of the sql, it is never assured to contain all of the sql - it uses sampling, it can and will miss some.


I'm not sure what you mean in the last bit. why do you think you cannot use the totals to get arbitrary deltas? It doesn't matter when the plan was last generated - the plan_hash_value will discern between different plans for the same query text - the total values between snap ids can be subtracted given the same plan hashes

Tracking index usage with dba_hist_sql_plan and dba_hist_sqlstat

October 28, 2008 - 4:09 am UTC

Reviewer: Yuri Gorelik from Israel

Hello Tom,
Thank you very much for your response. 
I needed some time to investigate to be sure.
In my case dba_hist_sqlstat contains a small portion of all sqls:


SQL> select count(distinct sql_id) from dba_hist_sql_plan;

COUNT(DISTINCTSQL_ID)
---------------------
                60089

SQL> select count(distinct sql_id) from dba_hist_sqlstat;

COUNT(DISTINCTSQL_ID)
---------------------
                 2653

Does it seem like I have a problem? And if so what should I do to verify it?
Can I do something to make dba_hist_sqlstat get more sqls?

I have an index that I want to drop, and I'm trying to check if it's being used and how. So I found one sql_id in dba_hist_sql_plan that uses this index, its timestamp is from 4 months ago (but ths does not say much because the timestamp column is from when the plan was produced for the first time), this sql is not in dba_hist_sqlstat so I don't have statistics relevant to snap_id's and I can't know when it was executed and how many time.
I ofcourse checked v$sqlarea and v$sql_plan but did not find this sql or any other usage of this index.

The only that is left for me is just turn on monitoring for this index and wait a couple of months.

Thanks a lot,
Yuri

Tom Kyte

Followup  

October 28, 2008 - 7:53 am UTC

the stuff you find in there is sampled, it will never be comprehensive. Nothing will be - you'd need an analog sample, versus the digital sample we can take.

eg: an analog sample would be continuous, always happening.

the digital signal, like a statspack.snap for example, happens at point X in time, say you:

a) snap
b) run a new sql
c) new sql ages out
d) snap

it'll be as of new sql never existed.


... The only that is left for me is just turn on monitoring for this index and wait
a couple of months.
....

well, I don't know that you would need to wait MONTHS would you. But that would be the appropriate approach - assuming you capture the v$ table contents PRIOR to shutting down (as it resets with each shutdown)

Tracking index usage with dba_hist_sql_plan and dba_hist_sqlstat

October 28, 2008 - 9:27 am UTC

Reviewer: Yuri Gorelik from Israel

Hello Tom,
So it means that to get more sqls in dba_hist_sqlstat I should set AWR to run in shorter intervals.

Also, since v$object_usage is a view of sys.object_usage it does not reset with each shutdown.

Thanks a lot,
Yuri
Tom Kyte

Followup  

October 28, 2008 - 10:53 am UTC

... So it means that to get more sqls in dba_hist_sqlstat I should set AWR to run
in shorter intervals.
...

I did not recommend that, I would not recommend that. You would have to set it infinitely close together over time to get everything.

Tracking index usage with dba_hist_sql_plan and dba_hist_sqlstat

October 28, 2008 - 11:54 am UTC

Reviewer: Yuri Gorelik from Israel

Tom,
You are totally right ofcourse.

I'm running AWR every hour and getting statistics for approximately 4% of the slqs, a crude estimate would suggest to run AWR every 2 minutes to get near 100% (50% and even less would be good for me as well), also I guess that I can write a little something to run in very short intervals for a couple of days to estimate the minimum life period for a sql during different hours, and then decide how to set AWR.


Thanks again for the input,
Yuri
Tom Kyte

Followup  

October 28, 2008 - 12:49 pm UTC

dba_hist_sqlstat:

... This view captures the top SQL statements based on a set of criteria and captures the statistics information from V$SQL. ...

it'll never have them all - it doesn't do that...


Tracking index usage with dba_hist_sql_plan and dba_hist_sqlstat

October 30, 2008 - 4:11 am UTC

Reviewer: Yuri Gorelik from Israel

Hello Tom,
Guess I'll have to go with monitoring after all.

Are there any new views or methods of tracking index usage in 11g?

Thanks,
Yuri
Tom Kyte

Followup  

October 30, 2008 - 8:28 am UTC

the index monitoring is what you want - everything else is a sample.

Summary on V$SQL_PLAN and dba_hist_sql_plan

June 24, 2016 - 5:24 pm UTC

Reviewer: Alejandra from Argentina

Hi Tom,

After reading this thread I want to confirm whether both views, v$SQL_PLAN and DBA_HIST_SQL_PLAN, contain sample data.
V$SQL_PLAN as per Oracle Documentation:
V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.

Thanks!
Alejandra


Connor McDonald

Followup  

June 25, 2016 - 3:05 am UTC

DBA_HIST_SQL_PLAN has samples.

V$SQL_PLAN you could consider a "sample" in that elements are aged out of it, hence you cannot be 100% sure that you would capture every plan.

Obviously frequent polling reduces the likelihood of this.