RE: Table Scans

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_at_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_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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gogala, Mladen
  INET: MGogala_at_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_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).