Try the following script. I am not sure where I found it on the web.
However, this script I use to find the bottlenecks in the system. Run
it while your application is running. Look for wait event
'db_file_scattered_read'. Check the related SQL. You can remove
other wait events if you do not need them.
Shakir
set echo off feedback off timing off pause off
set pages 100 lines 500 trimspool on trimout on space 1 recsep each
col sid format 990
col program format a15 word_wrap
col event format a8 word_wrap
col ospid format 9999990 heading "Srvr|PID"
col name format a15 word_wrap heading "OBJECT NAME"
col sql_text format a30 word_wrap
select /*+ rule */
w.sid,
w.event,
s.program,
p.spid ospid,
e.owner || '.' || e.segment_name || ' (' || e.segment_type || ')'
name,
a.sql_text
from sys.v_$sqlarea a,
sys.dba_extents e,
sys.v_$process p,
sys.v_$session s,
sys.v_$session_wait w
where w.event in ('write complete waits',
'latch free',
'log buffer space',
'free buffer waits',
'buffer busy waits',
'db file scattered read',
'db file sequential read',
'library cache pin',
'log file switch completion',
'enqueue',
'log file parallel write',
'db file parallel write',
'log file sync',
'file open',
'direct path write',
'library cache lock')
and s.sid = w.sid
and p.addr = s.paddr
and e.file_id = to_number(w.p1)
and to_number(w.p2) between e.block_id and (e.block_id + (e.blocks -
1))
and a.address (+) = s.sql_address;
- "Gogala, Mladen" <MGogala_at_oxhp.com> wrote:
> 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).
Mohammed Shakir
CompuSoft, Inc.
11 Heather Way
East Brunswick, NJ 08816-2825
(732) 672-0464 (Cell)
(732) 257-6001 (Home)
Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos & More
http://faith.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammed Shakir
INET: mshakir08816_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).
Received on Thu Oct 10 2002 - 17:13:41 CDT
