Top row sources from ASH

ash_top_row_sources.sql

select 
  h.SQL_PLAN_OPERATION
 ,h.SQL_PLAN_OPTIONS
 ,p.object_owner||'.'||p.OBJECT_NAME object_name
 ,decode(p.OPERATION
                 ,'TABLE ACCESS',p.OBJECT_OWNER||'.'||p.OBJECT_NAME
                 ,(select i.TABLE_OWNER||'.'||i.TABLE_NAME from dba_indexes i where i.OWNER=p.OBJECT_OWNER and i.index_name=p.OBJECT_NAME)
               ) table_name
 ,count(*)
from v$active_session_history h
    ,v$sql_plan p
where h.sql_opname='SELECT'
  and h.IN_SQL_EXECUTION='Y'
  and h.sql_plan_operation in ('INDEX','TABLE ACCESS')
  and p.SQL_ID = h.sql_id
  and p.CHILD_NUMBER = h.SQL_CHILD_NUMBER
  and p.ID = h.SQL_PLAN_LINE_ID
  -- если захотим за последние 3 часа:
  -- and h.sample_time >= systimestamp - interval '3' hour
group by
  h.SQL_PLAN_OPERATION
 ,h.SQL_PLAN_OPTIONS
 ,p.OPERATION,p.object_owner,p.OBJECT_NAME
order by count(*) desc
fetch first 10 rows only