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
;
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
;
No comments:
Write comments