Oracle Database - List of Indexes / Tables used in SQL Query / PL-SQL Procedure

Recently I was tasked with taking out list of all the indexes which are used by long running queries inside PL/SQL code. 

I had designed query in such a way to select distinct sql_ids from active session history by providing top_level_sql_id of the PL/SQL code. 

Finally I joined DBA_OBJECTS AND DBA_HIST_SQL_PLAN with clause of object type as Index. 

This listed me all the indexes used.



select DISTINCT DO.OWNER , DO.OBJECT_NAME, DO.OBJECT_TYPE
FROM
DBA_OBJECTS DO, DBA_HIST_SQL_PLAN DP
WHERE DO.OBJECT_ID = DP.OBJECT# AND
DO.OBJECT_TYPE = 'INDEX' AND
DP.SQL_ID IN (select distinct  sql_id from V$active_session_history where top_level_sql_id in ('6bjgua013q2jj','8fd1c17n8vpxh','cn469rdpw22rz') )
ORDER BY 1,2
;

This is list for all available plans in the system. If you want to narrow down to a particular plan then 
it is a good idea to add a where statement with plan hash value 
' AND DP.PLAN_HASH_VALUE = ########## '


List of Tables -

select DISTINCT DO.OWNER , DO.OBJECT_NAME, DO.OBJECT_TYPE
FROM
DBA_OBJECTS DO, DBA_HIST_SQL_PLAN DP
WHERE DO.OBJECT_ID = DP.OBJECT# AND
DO.OBJECT_TYPE = 'TABLE' AND
DP.SQL_ID IN (select distinct  sql_id from V$active_session_history where top_level_sql_id in ('6bjgua013q2jj','8fd1c17n8vpxh','cn469rdpw22rz') )
ORDER BY 1,2
;



List of Tables and Indexes both 

select DISTINCT DO.OWNER , DO.OBJECT_NAME, DO.OBJECT_TYPE
FROM
DBA_OBJECTS DO, DBA_HIST_SQL_PLAN DP
WHERE DO.OBJECT_ID = DP.OBJECT# AND
DO.OBJECT_TYPE = 'TABLE' AND
DP.SQL_ID IN (select distinct  sql_id from V$active_session_history where top_level_sql_id in ('6bjgua013q2jj','8fd1c17n8vpxh','cn469rdpw22rz') )
ORDER BY 1,3,2
;