Find unused indexes with STATSPACK
                                                 Last update (2008-07-30 13:14:25)
                                                                                                                   Date added (2008-07-30 13:06:05)

Creating an index doesn't mean that the index will be used. To find unused indexes for a period of time in Oracle9i and afterwards you can use STATSPACK views

When you activate the statspack snapshots, oracle stores the SQL that exceed the threshold settings for buffer_gets, physical I/O, parse calls, or executions. For example, statspack saves the statistics for all SQL that consume more than 1,000 disk reads. This information is displayed when you run the spreport.sql script.

In our case, however, spreport is not too helpful. We˘re not really interested in a summary of resource hogs; instead, we want to zero-in on index information. So, instead of running the spreport, we will query the Stats$ objects directly, extracting only that data relevant to our purposes.

2 usefull scripts
Let˘s take a look at two simple scripts. Our first retrieves all Sql related to a specific object name— in our case “CDRS” We˘ll also add a runtime threshold, so that we only look at Sql that runs longer than just a few seconds. Remember that the field Elapsed_Time is in units of microseconds, so be sure to scale accordingly. If you have stored a large number of snapshots, this script will take a few minutes:
SELECT Elapsed_Time/1000000 SECS, Sql_Text FROM (
SELECT /*+Use_Hash (St Use Sum T) Ordered*/
DISTINCT St.Snap_Id, Elapsed_Time,T.OLD_HASH_VALUE, T.Piece, T.Sql_Text
FROM Stats$Sql_Plan St,
Stats$Sql_Plan_Usage USE,
Stats$Sql_Summary SUM,
Stats$Sqltext T
WHERE St.Plan_Hash_Value = USE.Plan_Hash_Value
AND USE.Hash_Value = SUM.Hash_Value
AND Object_Name IN ('CDRS')
AND Elapsed_Time > 10000000
If you just want to find-out when a particular object was used, you can also get that from Statspack. Here˘s a simple script to find out when a particular object was used in some prior execution plan:
Select St.Snap_Id, To_Char(Snap_Time,'Dd-Mon-HH24:Mi') Snap
From Stats$Sql_Plan St,
Stats$Snapshot Sn
Where Object_Name In ('CDRS')
And St.Snap_Id = Sn.Snap_Id
Order By 2;
In the above scripts, you can easily customize your queries to list just index references, or perhaps just index references for which the query ran a long time. The view Stats$Sql_Plan is nearly an identical copy of V$Sql_Plan, so you can customize your queries to focus only on certain schemas or object types.