"Where-used-list" for table indexes?

[ThomasZloch]

Hello,

as you can see from my number of posts I am trying to find existing answers first, but on this one I did not succeed:

The question I'm trying to answer is

"Which table indexes have (not) been used (i.e. chosen by the CBO) for data access in the past x days?"

We're on 6.20 patch level 55 and Oracle 10.2.0.2, I am aware of SQL-Trace which shows the index used for particular SQL statements. ST04 shows expensive statements since the last DB startup. Very nice, but not what I need here. ST10 shows table calls, almost there, but I need "table index calls".

We have some custom indexes on BSIS, BKPF, COEP and the likes which take up several GB of space, and I need to know if we can get rid of them without having to analyze tons of code by developers that have long left the company (yeah, the old knowledge transfer problem) and also without just switching them off and "see what happens".

Thanks

Thomas

[ThomasZloch]

Maybe I should have posted this in the Oracle forum...anyway I found a way to gather the required information from some V$-tables via Native SQL.

Cheers

Thomas

[Rudi_Wiesmayr]

Servus Thomas!

I think we fight similar fights ...

lG Rudi

[raymond_giuseppi]

(If you remenber) Was it V$OBJECT_USAGE (from Monitoring Index Usage) to be able to disable some standard indexes in database and delete some customer created ?

Regards,

Raymond

[ThomasZloch]

Holy cow, so this really got bumped up.

I don't remember by heart, I think it was the V$ tables that also hold the shared cursor cache (that also allows finding expensive SQL statements from the past days/weeks).

If I find the exact names again, I'll post them here. You might as well be correct with your suggestion.

Thomas

[raymond_giuseppi]

Thomas,

There is no emergency, cleaning unnecessary index, is not [yet] a priority.

Thank you in advance