More on - Is my index used

try this SQL :-

SELECT P.object_name
--, P.operation
--, P.options
, COUNT ( 1 ) times_used
, MIN ( P.TIMESTAMP ) min_timestamp
, MAX ( P.TIMESTAMP ) max_timestamp
, AVG ( ind_size.size_mb ) size_mb
FROM dba_hist_sql_plan P
, dba_hist_sqlstat s
, (SELECT A.index_name
, A.owner
, ROUND ( SUM ( b.BLOCKS * 8 ) / 1024, 0 ) size_mb
FROM dba_indexes A
, dba_segments b
WHERE A.index_name = b.segment_name
AND A.UNIQUENESS <> 'UNIQUE'
GROUP BY A.index_name
, A.owner) ind_size
WHERE P.object_owner <> 'SYS'
AND P.operation LIKE '%INDEX%'
AND P.object_owner IN ('DWDIMS','DWFACTS')
AND P.sql_id = s.sql_id
AND P.object_owner = ind_size.owner
AND P.object_name = ind_size.index_name
GROUP BY P.object_name
--, P.operation
--, P.options
ORDER BY 4
, 1
, 2
, 3;