RE: Table Scans

Jay:

The other option is to look for 'db file scattered read' waits and join with dba_extents/segments to get the segments which are accessed via full table scan. This would be better alternate since you don't need to scan the entire buffer cache to get the names of the segments whose blocks are read to the buffer cache via sequential scan.

Best Regards,
K Gopalakrishnan
http://www.oradebug.com

-----Original Message-----
(DBA)

Sent: Wednesday, October 09, 2002 9:34 AM To: Multiple recipients of list ORACLE-L

I am looking for a query that will allow me to find the SQL statements that are responsible for Full Table Scans.

I understand that sometimes a full table scan is the best way to return data but I would like to evaluate this on a case by case basis.

I use the following query to identify the Tables were recently accessed by a full table scan, however, that still leaves me with over 100 statements to trace.

set serverout on size 1000000
set verify off
col object_name form a30
SELECT distinct(o.object_name),o.object_type,o.owner FROM dba_objects o,x$bh x
WHERE x.obj=o.object_id
AND o.object_type='TABLE'
AND standard.bitand(x.flag,524288)>0
AND o.owner<>'SYS';

Thanks

Jay

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Earle (DBA)
  INET: Jay_Earle_at_smartforce.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: K Gopalakrishnan
  INET: kaygopal_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).