Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Table Scans

RE: Table Scans

From: <Rajesh.Rao_at_jpmchase.com>
Date: Thu, 10 Oct 2002 14:33:40 -0800
Message-ID: <F001.004E64E7.20021010143340@fatcity.com>

How do you use this script to find bottlenecks in the system? It would only show you the scattered and sequential read waits? The query would not display rest of the wait events....

Raj

                                                                                                                     
                    Mohammed                                                                                         
                    Shakir                To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>        
                    <mshakir08816@        cc:                                                                        
                    yahoo.com>            Subject:     RE: Table Scans                                               
                    Sent by:                                                                                         
                    root_at_fatcity.c                                                                                   
                    om                                                                                               
                                                                                                                     
                                                                                                                     
                    October 10,                                                                                      
                    2002 06:13 PM                                                                                    
                    Please respond                                                                                   
                    to ORACLE-L                                                                                      
                                                                                                                     
                                                                                                                     




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;
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).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Rajesh.Rao_at_jpmchase.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:33:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US