Query to find if FULL TABLE SCANS are running in the Database

[Natik]

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.

Thanks.

[yxez]

You can select text in v$sqlarea where diskreads>10000...
Well, i guess its a little wild

[Thomasps]

Well, i guess its a little wild

Not just wild.. It is completely wild... BTW, Which woman is behind you?

Natik:

Use AWR Statistics if your are in 10g

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;

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.

Note: Read about statistics_level when you use AWR.
http://www.oracle-base.com/articles/...icsLevel9i.php