I felt most folks looking for full-table scans were not getting the full picture and created my own script to pick up the operations I felt went into this category.

#!/bin/ksh
sqlplus -s "/ as sysdba" <<EOF
spool fts_MRC.log

set linesize 200
set pagesize 66
col operation format a13
col object_name format a32
col object_owner format a10
col options format a15
col executions format 999,999,999

select
a.sql_id,a.object_owner,a.object_name, rtrim(a.operation) operation,
a.options, sum(b.executions) executions, c.bytes, (sum(b.executions)*c.bytes)/(1024*1024) fts_meg
from
v\$sql_Plan a, v\$sqlarea b, dba_segments c
where (a.object_owner=c.owner and a.object_name=c.segment_name) and
a.address=b.address and
a.operation IN ('TABLE ACCESS','INDEX','HASH JOIN') and
nvl(a.options,'NULL') in ('FULL','FULL SCAN','FAST FULL SCAN','SKIP SCAN','SAMPLE FAST FULL SCAN','OUTER','NULL') and
a.object_owner not in ('SYS','SYSTEM','PERFSTAT','SYSMAN','WKSYS') and
b.executions&gt;1
group by a.sql_id,a.object_owner, a.object_name, operation, options, c.bytes
order by 8, a.object_owner,a.object_name,operation, options, executions desc;

spool off

EOF

Ready for Action?

LET'S GO!
Copyright 2024 IT Remote dot com
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram