How to identify full table scans?
List, We use the following script to identify recent full table scans or full index scans. This result set will be used to identify the potential queries that could benefit by creating any new indexes or modify the existing index structure as needed. Our intention is to run this query against X$BH every hour and gather this data. Do you have any suggestions or scripts to accomplish the same? Are there any issues in trying to do this every hour? Thanks, Govind /* Recent full table scan */ /* Should be run as user SYS */ set serverout on size 100 set verify off set pagesiz 300 set lin 120 col object_name form a30 col owner form a10 PROMPT Column flag in x$bh table is set to value 0x8, when PROMPT block was read by a sequential scan. spool recentfulltablescan.lst SELECT count(o.object_name) COUNT, o.object_name, o.object_type, o.owner, t.num_rows FROM dba_objects o,x$bh x, dba_tables t WHERE x.obj=o.object_id and o.object_name=t.table_name -- AND o.object_type='TABLE' AND standard.bitand(x.flag,524288)0 AND o.owner'SYS' group by o.object_name, o.object_type, o.owner, t.num_rows order by 1 ; spool off -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: How to identify full table scans?
Title: RE: How to identify full table scans? Govind, Just curious why you are attacking the full table scans. I implemented something like this in the past utilizing Steve Adams' script expensive_sql.sql. It was very telling and very very useful. Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, January 13, 2003 1:55 PM To: Multiple recipients of list ORACLE-L Subject: How to identify full table scans? List, We use the following script to identify recent full table scans or full index scans. This result set will be used to identify the potential queries that could benefit by creating any new indexes or modify the existing index structure as needed. Our intention is to run this query against X$BH every hour and gather this data. Do you have any suggestions or scripts to accomplish the same? Are there any issues in trying to do this every hour? Thanks, Govind /* Recent full table scan */ /* Should be run as user SYS */ set serverout on size 100 set verify off set pagesiz 300 set lin 120 col object_name form a30 col owner form a10 PROMPT Column flag in x$bh table is set to value 0x8, when PROMPT block was read by a sequential scan. spool recentfulltablescan.lst SELECT count(o.object_name) COUNT, o.object_name, o.object_type, o.owner, t.num_rows FROM dba_objects o,x$bh x, dba_tables t WHERE x.obj=o.object_id and o.object_name=t.table_name -- AND o.object_type='TABLE' AND standard.bitand(x.flag,524288)0 AND o.owner'SYS' group by o.object_name, o.object_type, o.owner, t.num_rows order by 1 ; spool off -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: How to identify full table scans?
Title: RE: How to identify full table scans? This helps to identify the queries that could be tunedfor LIO and/or PIO from a SQL Tuning perspective. We can give this list to the development or application teams so that they could independently work off this list (hopefully!). -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Monday, January 13, 2003 2:22 PMTo: Multiple recipients of list ORACLE-LSubject: RE: How to identify full table scans? Govind, Just curious why you are attacking the full table scans. I implemented something like this in the past utilizing Steve Adams' script expensive_sql.sql. It was very telling and very very useful. Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, January 13, 2003 1:55 PM To: Multiple recipients of list ORACLE-L Subject: How to identify full table scans? List, We use the following script to identify recent full table scans or full index scans. This result set will be used to identify the potential queries that could benefit by creating any new indexes or modify the existing index structure as needed. Our intention is to run this query against X$BH every hour and gather this data. Do you have any suggestions or scripts to accomplish the same? Are there any issues in trying to do this every hour? Thanks, Govind /* Recent full table scan */ /* Should be run as user SYS */ set serverout on size 100 set verify off set pagesiz 300 set lin 120 col object_name form a30 col owner form a10 PROMPT Column flag in x$bh table is set to value 0x8, when PROMPT block was read by a sequential scan. spool recentfulltablescan.lst SELECT count(o.object_name) "COUNT", o.object_name, o.object_type, o.owner, t.num_rows FROM dba_objects o,x$bh x, dba_tables t WHERE x.obj=o.object_id and o.object_name=t.table_name -- AND o.object_type='TABLE' AND standard.bitand(x.flag,524288)0 AND o.owner'SYS' group by o.object_name, o.object_type, o.owner, t.num_rows order by 1 ; spool off -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: How to identify full table scans?
Hi Govind, Just a thought: The value of _small_table_threshold - which is currently 2% of the DB_BLOCK_BUFFERS. Any table undergoing FTS will be placed at the MRU end rather than the LRU end and would thus live longer (and either cause problems or alleviate it as the case may be!). Keep this in mind if you are looking at FTS... John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- Sent: Monday, January 13, 2003 2:24 PM To: Multiple recipients of list ORACLE-L This helps to identify the queries that could be tuned for LIO and/or PIO from a SQL Tuning perspective. We can give this list to the development or application teams so that they could independently work off this list (hopefully!). -Original Message- Sent: Monday, January 13, 2003 2:22 PM To: Multiple recipients of list ORACLE-L Govind, Just curious why you are attacking the full table scans. I implemented something like this in the past utilizing Steve Adams' script expensive_sql.sql. It was very telling and very very useful. Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Monday, January 13, 2003 1:55 PM To: Multiple recipients of list ORACLE-L List, We use the following script to identify recent full table scans or full index scans. This result set will be used to identify the potential queries that could benefit by creating any new indexes or modify the existing index structure as needed. Our intention is to run this query against X$BH every hour and gather this data. Do you have any suggestions or scripts to accomplish the same? Are there any issues in trying to do this every hour? Thanks, Govind /* Recent full table scan */ /* Should be run as user SYS */ set serverout on size 100 set verify off set pagesiz 300 set lin 120 col object_name form a30 col owner form a10 PROMPT Column flag in x$bh table is set to value 0x8, when PROMPT block was read by a sequential scan. spool recentfulltablescan.lst SELECT count(o.object_name) COUNT, o.object_name, o.object_type, o.owner, t.num_rows FROM dba_objects o,x$bh x, dba_tables t WHERE x.obj=o.object_id and o.object_name=t.table_name -- AND o.object_type='TABLE' AND standard.bitand(x.flag,524288)0 AND o.owner'SYS' group by o.object_name, o.object_type, o.owner, t.num_rows order by 1 ; spool off -- Please see the official ORACLE-L FAQ: http://www.orafaq.net http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com 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.net -- Author: John Kanagaraj 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
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
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
Table Scans
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).
RE: Table Scans
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
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
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
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
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
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).
full table scans
How can I find out what queries and/or tables are being used in full table scans? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: full table scans
John Dunn wrote: How can I find out what queries and/or tables are being used in full table scans? John Select query text from V$SQLAREA (or, better, V$SQLTEXT since V$SQLAREA only contains the first 999 characters) and run EXPLAIN on it. Querying PLAN_TABLE afterwards will give you the info (do not forget to assign statement identifiers when running EXPLAIN). This is not too difficult to code in SQL or PL/SQL. This said, full table scans are not necessarily bad. Most reference tables are usually accessed faster through FTS, and sometimes big tables too. Only Big Bad Full Table Scans are to be feared; it really depends. You should concentrate on queries which do a high number of logical reads. There is a free script (peep.sql) on the Oriole site to find them. PLUGThere is also a product, ORISNOOP, to give you a fuller picture and generate a usually awesome HTML report/PLUG. -- Regards, Stephane Faroult Oriole Corporation -- http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
