ash_full_scans.sql

ash_full_scans.sql

select
  h.sql_id
 ,h.SQL_PLAN_OPERATION
 ,h.SQL_PLAN_OPTIONS
 ,p.object_owner||'.'||p.OBJECT_NAME object_name
 ,count(*)
from v$active_session_history h,v$sql_plan p
where h.IN_SQL_EXECUTION='Y'
  and h.sql_plan_operation in ('INDEX','TABLE ACCESS')
  and p.object_name is not null
  and p.object_owner!='SYS'
  and p.SQL_ID = h.sql_id
  and p.CHILD_NUMBER = h.SQL_CHILD_NUMBER
  and p.ID = h.SQL_PLAN_LINE_ID
  and h.sql_plan_options like '%FULL%'
group by 
  h.sql_id
 ,h.SQL_PLAN_OPERATION
 ,h.SQL_PLAN_OPTIONS
 ,p.object_owner||'.'||p.OBJECT_NAME