Message-ID: <F001.004E4D76.20021009121839@fatcity.com>
Date: Wed, 09 Oct 2002 12:18:39 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: "Gogala, Mladen" <MGogala@oxhp.com>
Subject: RE: Table Scans
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain;	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

That is correct, but I do think that everybody wants to know 
how did you get that number (512k) and where can we find more info 
about that.

> -----Original Message-----
> From: K Gopalakrishnan [mailto:kaygopal@yahoo.com]
> Sent: Wednesday, October 09, 2002 3:24 PM
> To: Multiple recipients of list ORACLE-L
> Subject: 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@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@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@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@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: Gogala, Mladen
  INET: MGogala@oxhp.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@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).