Query to find if FULL TABLE SCANS are running in the Database
What query can be used to see what FULL TABLE SCANS are taking place in the database? How does Oracle decide whether it should perform a FULL TABLE SCAN vs. using an index that is present on the table.
You can select text in v$sqlarea where diskreads>10000...
Well, i guess its a little wild
Behind The Success And Failure Of A Man Is A Woman
Not just wild.. It is completely wild... BTW, Which woman is behind you?
Originally Posted by yxez
Use AWR Statistics if your are in 10g
Oracle will identify the best optimized way - query plan - of excuting a query thru a costing method. Read manuls to understand more about STATISTICS and how Oracle identifies a best excution plan.
ttile ‘Large Full-table scans|Per Snapshot Period’
col c1 heading ‘Begin|Interval|time’ format a20
col c4 heading ‘FTS|Count’ format 999,999
break on c1 skip 2
break on c2 skip 2
select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, count(1) c4
from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn,dba_segments o
where p.object_owner <> 'SYS' and p.object_owner = o.owner
and p.object_name = o.segment_name and o.blocks > 1000
and p.operation like '%TABLE ACCESS%' and p.options like '%FULL%'
and p.sql_id = s.sql_id and s.snap_id = sn.snap_id
group by to_char(sn.begin_interval_time,'yy-mm-dd hh24')
order by 1;
Note: Read about statistics_level when you use AWR.
Click Here to Expand Forum to Full Width