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)

see above.

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

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.

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

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 

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

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)

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

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

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

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.