RE: Table Scans

2002-10-10 Thread Mohammed Shakir 

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 990 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
fromsys.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 [EMAIL PROTECTED] 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:[EMAIL PROTECTED]]
  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 100
  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: [EMAIL PROTECTED]
  
  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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
  
  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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California

RE: Table Scans

2002-10-10 Thread Rajesh . Rao 


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   
  
ShakirTo: Multiple recipients of list ORACLE-L 
[EMAIL PROTECTED]
mshakir08816@cc:  
  
yahoo.comSubject: RE: Table Scans 
  
Sent by:   
  
[EMAIL PROTECTED] 
  
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 990 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
fromsys.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 [EMAIL PROTECTED] 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:[EMAIL PROTECTED]]
  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

RE: Table Scans

2002-10-09 Thread Vergara, Michael (TEM) 

I am curious about something in your query.  

 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';

Where did you learn of the correct pattern for the
AND standard.bitand(x.flag,524288)0 clause?

Thanks,
Mike
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Vergara, Michael (TEM)
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

RE: Table Scans

2002-10-09 Thread K Gopalakrishnan 

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 100
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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

RE: Table Scans

2002-10-09 Thread Jay Earle (DBA) 

http://www.think-forward.com/sql/bufferts.htm

-Original Message-
Sent: Wednesday, October 09, 2002 4:00 PM
To: Multiple recipients of list ORACLE-L


I am curious about something in your query.  

 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';

Where did you learn of the correct pattern for the
AND standard.bitand(x.flag,524288)0 clause?

Thanks,
Mike
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Vergara, Michael (TEM)
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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: Jay Earle (DBA)
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

RE: Table Scans

2002-10-09 Thread Gogala, Mladen 

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:[EMAIL PROTECTED]]
 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 100
 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: [EMAIL PROTECTED]
 
 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
 
 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

RE: Table Scans

2002-10-09 Thread Alexander . Feinstein 
Title: RE: Table Scans





Mladen,


Look up definition of x$bh.flag.
For example, http://www.jlcomp.demon.co.uk/buf_flag.html


Correction to the query, join should be x.obj=o.data_object_id
And as a note, one needs to take care about clustered tables.


Alex.



-Original Message-
From: Gogala, Mladen [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 09, 2002 1:19 PM
To: Multiple recipients of list ORACLE-L
Subject: 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.


 set serverout on size 100
 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';

Re: Table Scans

2002-10-09 Thread Mladen Gogala 

Aargh! Thanks! Flag 19 is sequential only access and bitand with 2**19 
will get only the buffers belonging to objects accessed using full table scan.
Thanks!
On 2002.10.09 23:08 [EMAIL PROTECTED] wrote:
 Mladen,
 
 Look up definition of x$bh.flag.
 For example, http://www.jlcomp.demon.co.uk/buf_flag.html
 
 Correction to the query, join should be x.obj=o.data_object_id
 And as a note, one needs to take care about clustered tables.
 
 Alex.
 
 
 -Original Message-
 Sent: Wednesday, October 09, 2002 1:19 PM
 To: Multiple recipients of list ORACLE-L
 
 
 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.
 
  set serverout on size 100
  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';
 

-- 
Mladen Gogala
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mladen Gogala
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).