Re: oracle full table scan
Would you please? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 3:28 PM Sorry, the attachments didn't make it, though they were only text. I can put them some accessible via the web if anyone wants them. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 11:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan If you can change it to an IOT, it may be beneficial. There's no blanket clause to be used that says 'Always do this'. I higly encourage folks on this list to setup and use the run_stats method of comparing different access methods. This is something Tom Kyte put together. It is very simple to use. URL: http://osi.oracle.com/~tkyte/runstats.html I've attached my versions of the scripts for your convenience. You can use these to easily compare unindexed vs indexed reads on small tables, indexed vs IOT, etc. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 10:05 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Thanks Jared, What if my developer is selecting all or most of the records from the table and not all the columns in the select list are in the index that should have been used? I understand your point, in fact to use Jonathan's words .. should a small lookup table BE an index (IOT)? ... I am testing this approach here and have found some performance benefit out of it. Cheers Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, April 03, 2003 1:01 PM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Importance: High Raj, Indexing small tables is a good thing if you are doing single row lookups. An index read and lookup by rowid is much more scalable than doing an FTS, even if the table is only 2 blocks. Jared The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out
Re: oracle full table scan
A zip file of the run_stats scripts can be downloaded from http://www.cybcon.com/~jkstill/download/run_stats.zip Jared Igor Neyman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/04/2003 06:08 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: oracle full table scan Would you please? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 3:28 PM Sorry, the attachments didn't make it, though they were only text. I can put them some accessible via the web if anyone wants them. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 11:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan If you can change it to an IOT, it may be beneficial. There's no blanket clause to be used that says 'Always do this'. I higly encourage folks on this list to setup and use the run_stats method of comparing different access methods. This is something Tom Kyte put together. It is very simple to use. URL: http://osi.oracle.com/~tkyte/runstats.html I've attached my versions of the scripts for your convenience. You can use these to easily compare unindexed vs indexed reads on small tables, indexed vs IOT, etc. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 10:05 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Thanks Jared, What if my developer is selecting all or most of the records from the table and not all the columns in the select list are in the index that should have been used? I understand your point, in fact to use Jonathan's words .. should a small lookup table BE an index (IOT)? ... I am testing this approach here and have found some performance benefit out of it. Cheers Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, April 03, 2003 1:01 PM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Importance: High Raj, Indexing small tables is a good thing if you are doing single row lookups. An index read and lookup by rowid is much more scalable than doing an FTS, even if the table is only 2 blocks. Jared The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask
Re: oracle full table scan
Perhaps you need to do a trace to determine the real cause of you problems. Full table scans are not necessarily the problem. When you have trace for the program and the explain plain you have of the executing SQL you will have a better idea than assuming you need indexes to stop full table scans. Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom The People, The Experience, The Vision = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. Arvind Kumar [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/04/2003 02:58 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:oracle full table scan Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arvind Kumar 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.net -- Author: 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: oracle full table scan
Hi Arvind, A little test for you. You have a table that contains 10,000,000 rows that is packed tightly into 1,000,000 data blocks. You have an index that has a level of 4 and has 10,000 leaf blocks. The table is well striped across a number of devices and you have 4 CPUs on the box. You write a simple select statement that queries the table based on the indexed column and *just 10%* of the data needs to be retrieved. You determine that the CBO has performed a full table scan. Do you break out into a nervous sweat or do you sigh thank goodness and worry about something else instead ? Cheers Richard (let me know if you want to know the comparative costs ;) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 2:58 PM Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arvind Kumar 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.net -- Author: Richard Foote 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: oracle full table scan
Title: RE: oracle full table scan To answer the original question ... 1. use following query to see which tables are part of FTS ... it is a point in time information. (Query from www.ixora.com I think). SELECT usr.name oowner, ob.name oname FROM ( SELECT obj FROM sys.X_$BH WHERE TO_NUMBER(bitand(flag, POWER(2,19))) 0 GROUP BY obj) bh, sys.obj$ ob, sys.USER$ usr WHERE ob.dataobj# = bh.obj AND ob.owner# = usr.USER# ORDER BY usr.name, ob.name / 2. FTS can happen for many reasons ... if Oracle is performing FTS on a small table, that's the way to do it. Remember when you create an index Oracle had to perform 2 IOs, one for INDEX lookup and (if required) one for Table lookup. Sometimes associated costs dictate that a FTS is cheaper than the combined cost (of index lookup and table lookup), so Oracle prefers that. One upon a time, I used to think on the same lines, but the bright minds on this list have time and again proven that FTS, isn't a bad thing after all. Sometimes it is, but not ALL the times. Creating indexes is not the solution, a careful analysis of the logic implemented in the SQL is also required, and you will be surprised that, just by making the query changes, the performance gain can be achieved. PS: Stephane, you probably have this on the top of your Oracle Myth list ... right? YMMV Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 2:58 PM Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: oracle full table scan
Hi Arvind,
I don't judge full table scan is good or not necessary bad.
this is the script might answer your question.
-joan
The following scripts provide information on the full table scan
activity.
If your application is OLTP only, having long full table scans can be an
indicator of having missing or incorrect indexes or untuned SQL.
#
drop table Full_Table_Scans
/
create table Full_Table_Scans as
select ss.username||'('||se.sid||') ' User Process,
sum(decode(name,'table scans (short tables)',value)) Short Scans,
sum(decode(name,'table scans (long tables)', value)) Long Scans,
sum(decode(name,'table scan rows gotten',value)) Rows Retreived
from v$session ss, v$sesstat se, v$statname sn
where se.statistic# = sn.statistic#
and (name like '%table scans (short tables)%'
OR name like '%table scans (long tables)%'
OR name like '%table scan rows gotten%' )
and se.sid = ss.sid
and ss.username is not null
group by ss.username||'('||se.sid||') ';
column User Process format a20;
column Long Scans format 999,999,999;
column Short Scans format 999,999,999;
column Rows Retreived format 999,999,999;
column Average Long Scan Length format 999,999,999;
ttitle ' Table Access Activity By User '
select User Process, Long Scans, Short Scans, Rows Retreived
from Full_Table_Scans
order by Long Scans desc;
Richard Foote wrote:
Hi Arvind,
A little test for you.
You have a table that contains 10,000,000 rows that is packed tightly
into 1,000,000 data blocks.
You have an index that has a level of 4 and has 10,000 leaf blocks.
The table is well striped across a number of devices and you have 4
CPUs on the box.
You write a simple select statement that queries the table based on
the indexed column and *just 10%* of the data needs to be retrieved.
You determine that the CBO has performed a full table scan.
Do you break out into a nervous sweat or do you sigh thank goodness
and worry about something else instead ?
Cheers
Richard (let me know if you want to know the comparative costs ;)
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, April 03, 2003 2:58 PM
Dear All,
is there any way to find which tables (table name) are
suffering from
full table scan ,so that i can create indexes on them to enhance the
performance.
Thanks
Arvind
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Arvind Kumar
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.net
--
Author: Richard Foote
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.net
--
Author: Joan Hsieh
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: oracle full table scan
Hello Arvind, Thursday, April 3, 2003, 5:58:38 AM, you wrote: AK Dear All, AK is there any way to find which tables (table name) are suffering from AK full table scan ,so that i can create indexes on them to enhance the AK performance. AK Thanks AK Arvind AK -- AK Please see the official ORACLE-L FAQ: http://www.orafaq.net Use SQL_TRACE feature to find all statements which used FTS. -- Best regards, Alexmailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alex Andriyashchenko 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: oracle full table scan
Tables are not suffering, they're rather cruel and coldhearted. As in the Tom Godwin's story, The Cold Equations, it's always the users who pay the price. You might try with tuning the SQL statements that access tables. Occasionally, that does the trick. -Original Message- Sent: Wednesday, April 02, 2003 11:59 PM To: Multiple recipients of list ORACLE-L Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arvind Kumar 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.net -- 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: oracle full table scan
Arvind - If you want to locate tables that are being scanned and the SQL
statement, I have found the following script posted by Mohammed to work
quite effectively.
Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
REM From: Mohammed Shakir [mailto:[EMAIL PROTECTED]
REM Sent: Thursday, October 10, 2002 5:14 PM
REM To: Multiple recipients of list ORACLE-L
REM Subject: RE: Table Scans
REM Try the following script. I am not sure where I found it on the web.
REM However, this script I use to find the bottlenecks in the system.
REM Run it while your application is running.
REM Look for wait event 'db_file_scattered_read'.
REM Check the related SQL.
REM You can remove other wait events if you do not need them.
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;
-Original Message-
Sent: Wednesday, April 02, 2003 10:59 PM
To: Multiple recipients of list ORACLE-L
Dear All,
is there any way to find which tables (table name) are suffering from
full table scan ,so that i can create indexes on them to enhance the
performance.
Thanks
Arvind
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Arvind Kumar
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.net
--
Author: DENNIS WILLIAMS
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: oracle full table scan
Raj, Indexing small tables is a good thing if you are doing single row lookups. An index read and lookup by rowid is much more scalable than doing an FTS, even if the table is only 2 blocks. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 05:28 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan To answer the original question ... 1. use following query to see which tables are part of FTS ... it is a point in time information. (Query from www.ixora.com I think). SELECT usr.name oowner, ob.name oname FROM ( SELECT obj FROM sys.X_$BH WHERE TO_NUMBER(bitand(flag, POWER(2,19))) 0 GROUP BY obj) bh, sys.obj$ ob, sys.USER$ usr WHERE ob.dataobj# = bh.obj AND ob.owner# = usr.USER# ORDER BY usr.name, ob.name / 2. FTS can happen for many reasons ... if Oracle is performing FTS on a small table, that's the way to do it. Remember when you create an index Oracle had to perform 2 IOs, one for INDEX lookup and (if required) one for Table lookup. Sometimes associated costs dictate that a FTS is cheaper than the combined cost (of index lookup and table lookup), so Oracle prefers that. One upon a time, I used to think on the same lines, but the bright minds on this list have time and again proven that FTS, isn't a bad thing after all. Sometimes it is, but not ALL the times. Creating indexes is not the solution, a careful analysis of the logic implemented in the SQL is also required, and you will be surprised that, just by making the query changes, the performance gain can be achieved. PS: Stephane, you probably have this on the top of your Oracle Myth list ... right? YMMV Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 2:58 PM Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: oracle full table scan
Title: RE: oracle full table scan Thanks Jared, What if my developer is selecting all or most of the records from the table and not all the columns in the select list are in the index that should have been used? I understand your point, in fact to use Jonathan's words .. should a small lookup table BE an index (IOT)? ... I am testing this approach here and have found some performance benefit out of it. Cheers Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 03, 2003 1:01 PM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Subject: RE: oracle full table scan Importance: High Raj, Indexing small tables is a good thing if you are doing single row lookups. An index read and lookup by rowid is much more scalable than doing an FTS, even if the table is only 2 blocks. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 05:28 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: RE: oracle full table scan To answer the original question ... 1. use following query to see which tables are part of FTS ... it is a point in time information. (Query from www.ixora.com I think). SELECT usr.name oowner, ob.name oname FROM ( SELECT obj FROM sys.X_$BH WHERE TO_NUMBER(bitand(flag, POWER(2,19))) 0 GROUP BY obj) bh, sys.obj$ ob, sys.USER$ usr WHERE ob.dataobj# = bh.obj AND ob.owner# = usr.USER# ORDER BY usr.name, ob.name / 2. FTS can happen for many reasons ... if Oracle is performing FTS on a small table, that's the way to do it. Remember when you create an index Oracle had to perform 2 IOs, one for INDEX lookup and (if required) one for Table lookup. Sometimes associated costs dictate that a FTS is cheaper than the combined cost (of index lookup and table lookup), so Oracle prefers that. One upon a time, I used to think on the same lines, but the bright minds on this list have time and again proven that FTS, isn't a bad thing after all. Sometimes it is, but not ALL the times. Creating indexes is not the solution, a careful analysis of the logic implemented in the SQL is also required, and you will be surprised that, just by making the query changes, the performance gain can be achieved. PS: Stephane, you probably have this on the top of your Oracle Myth list ... right? YMMV Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 2:58 PM Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: oracle full table scan
Comparing users to Marilyn Cross. Naive, not overly bright, sentenced to death. Is that too harsh for users? Jared Gogala, Mladen [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 07:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Tables are not suffering, they're rather cruel and coldhearted. As in the Tom Godwin's story, The Cold Equations, it's always the users who pay the price. You might try with tuning the SQL statements that access tables. Occasionally, that does the trick. -Original Message- Sent: Wednesday, April 02, 2003 11:59 PM To: Multiple recipients of list ORACLE-L Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arvind Kumar 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.net -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: oracle full table scan
Jared, I like you more and more every day. -Original Message- Sent: Thursday, April 03, 2003 1:05 PM To: Multiple recipients of list ORACLE-L Comparing users to Marilyn Cross. Naive, not overly bright, sentenced to death. Is that too harsh for users? Jared Gogala, Mladen [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 07:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Tables are not suffering, they're rather cruel and coldhearted. As in the Tom Godwin's story, The Cold Equations, it's always the users who pay the price. You might try with tuning the SQL statements that access tables. Occasionally, that does the trick. -Original Message- Sent: Wednesday, April 02, 2003 11:59 PM To: Multiple recipients of list ORACLE-L Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arvind Kumar 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.net -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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.net -- 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: oracle full table scan
Jamadagni, Rajendra wrote: PS: Stephane, you probably have this on the top of your Oracle Myth list ... right? YMMV Raj Indeed, together with 'always replace NOT IN with NOT EXISTS ...' - another case today ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: oracle full table scan
only for some. unfortunately not for most of mine --- [EMAIL PROTECTED] wrote: Comparing users to Marilyn Cross. Naive, not overly bright, sentenced to death. Is that too harsh for users? Jared Gogala, Mladen [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 07:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Tables are not suffering, they're rather cruel and coldhearted. As in the Tom Godwin's story, The Cold Equations, it's always the users who pay the price. You might try with tuning the SQL statements that access tables. Occasionally, that does the trick. -Original Message- Sent: Wednesday, April 02, 2003 11:59 PM To: Multiple recipients of list ORACLE-L Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arvind Kumar 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.net -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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). __ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://tax.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: oracle full table scan
If you can change it to an IOT, it may be beneficial. There's no blanket clause to be used that says 'Always do this'. I higly encourage folks on this list to setup and use the run_stats method of comparing different access methods. This is something Tom Kyte put together. It is very simple to use. URL: http://osi.oracle.com/~tkyte/runstats.html I've attached my versions of the scripts for your convenience. You can use these to easily compare unindexed vs indexed reads on small tables, indexed vs IOT, etc. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 10:05 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Thanks Jared, What if my developer is selecting all or most of the records from the table and not all the columns in the select list are in the index that should have been used? I understand your point, in fact to use Jonathan's words .. should a small lookup table BE an index (IOT)? ... I am testing this approach here and have found some performance benefit out of it. Cheers Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, April 03, 2003 1:01 PM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Importance: High Raj, Indexing small tables is a good thing if you are doing single row lookups. An index read and lookup by rowid is much more scalable than doing an FTS, even if the table is only 2 blocks. Jared view.sql Description: Binary data The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. grants.sql Description: Binary data The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. run_stats.sql Description: Binary data The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. Runstats.sql This is the test harness I use to try out different ideas. It shows two vital sets of statistics for me The elapsed time difference between two approaches. It very simply shows me which approach is faster by the wall clock How many resources each approach takes. This can be more meaningful then even the wall clock timings. For example, if one approach is faster then the other but it takes thousands of latches (locks), I might avoid it simply because it will not scale as well. The way this test harness works is by saving the system statistics and latch information into a temporary table. We then run a test and take another snapshot. We run the second test and take yet another snapshot. Now we can show the amount of resources used by approach 1 and approach 2. Requirements In order to run this test harness you must at a minimum have: Access to V$STATNAME, V$MYSTAT, and V$LATCH If you want to use the view as I have, you must be granted select DIRECTLY on SYS.V_$STATNAME, SYS.V_$MYSTAT, and SYS.V_$LATCH. It will not work to have select on these via a ROLE. You can still run the test harness, you just will not be using the view STATS I have below (substitute in the query text in the PLSQL block where I reference the view STATS). The ability to create a table -- run_stats -- to hold the before, during and after information. You should note also that the LATCH information is collected on a SYSTEM WIDE basis. If you run this on a multi-user system, the latch information may be technically incorrect as you will count the latching information for other sessions - not just your
RE: oracle full table scan
Sorry, the attachments didn't make it, though they were only text. I can put them some accessible via the web if anyone wants them. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 11:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan If you can change it to an IOT, it may be beneficial. There's no blanket clause to be used that says 'Always do this'. I higly encourage folks on this list to setup and use the run_stats method of comparing different access methods. This is something Tom Kyte put together. It is very simple to use. URL: http://osi.oracle.com/~tkyte/runstats.html I've attached my versions of the scripts for your convenience. You can use these to easily compare unindexed vs indexed reads on small tables, indexed vs IOT, etc. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 10:05 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Thanks Jared, What if my developer is selecting all or most of the records from the table and not all the columns in the select list are in the index that should have been used? I understand your point, in fact to use Jonathan's words .. should a small lookup table BE an index (IOT)? ... I am testing this approach here and have found some performance benefit out of it. Cheers Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, April 03, 2003 1:01 PM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Importance: High Raj, Indexing small tables is a good thing if you are doing single row lookups. An index read and lookup by rowid is much more scalable than doing an FTS, even if the table is only 2 blocks. Jared The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. view.sql Description: Binary data The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing
RE: oracle full table scan
I got the attachments... --- [EMAIL PROTECTED] wrote: Sorry, the attachments didn't make it, though they were only text. I can put them some accessible via the web if anyone wants them. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 11:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan If you can change it to an IOT, it may be beneficial. There's no blanket clause to be used that says 'Always do this'. I higly encourage folks on this list to setup and use the run_stats method of comparing different access methods. This is something Tom Kyte put together. It is very simple to use. URL: http://osi.oracle.com/~tkyte/runstats.html I've attached my versions of the scripts for your convenience. You can use these to easily compare unindexed vs indexed reads on small tables, indexed vs IOT, etc. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 10:05 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Thanks Jared, What if my developer is selecting all or most of the records from the table and not all the columns in the select list are in the index that should have been used? I understand your point, in fact to use Jonathan's words .. should a small lookup table BE an index (IOT)? ... I am testing this approach here and have found some performance benefit out of it. Cheers Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, April 03, 2003 1:01 PM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Importance: High Raj, Indexing small tables is a good thing if you are doing single row lookups. An index read and lookup by rowid is much more scalable than doing an FTS, even if the table is only 2 blocks. Jared The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. ATTACHMENT part 2 application/octet-stream name=view.sql The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary
RE: oracle full table scan
Did you look at them... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] .com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: oracle full table scan 04/03/2003 02:08 PM Please respond to ORACLE-L I got the attachments... --- [EMAIL PROTECTED] wrote: Sorry, the attachments didn't make it, though they were only text. I can put them some accessible via the web if anyone wants them. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 11:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan If you can change it to an IOT, it may be beneficial. There's no blanket clause to be used that says 'Always do this'. I higly encourage folks on this list to setup and use the run_stats method of comparing different access methods. This is something Tom Kyte put together. It is very simple to use. URL: http://osi.oracle.com/~tkyte/runstats.html I've attached my versions of the scripts for your convenience. You can use these to easily compare unindexed vs indexed reads on small tables, indexed vs IOT, etc. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 10:05 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Thanks Jared, What if my developer is selecting all or most of the records from the table and not all the columns in the select list are in the index that should have been used? I understand your point, in fact to use Jonathan's words .. should a small lookup table BE an index (IOT)? ... I am testing this approach here and have found some performance benefit out of it. Cheers Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, April 03, 2003 1:01 PM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Importance: High Raj, Indexing small tables is a good thing if you are doing single row lookups. An index read and lookup by rowid is much more scalable than doing an FTS, even if the table is only 2 blocks. Jared The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists
RE: oracle full table scan
not closely enough :) my only excuse is that I had spent a few hours in a rah rah, we are wonderful meeting today after we laid off 400 people last week. brain dead. must get caffeine... immediately! --- Ron Thomas [EMAIL PROTECTED] wrote: Did you look at them... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] .com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: oracle full table scan 04/03/2003 02:08 PM Please respond to ORACLE-L I got the attachments... --- [EMAIL PROTECTED] wrote: Sorry, the attachments didn't make it, though they were only text. I can put them some accessible via the web if anyone wants them. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 11:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan If you can change it to an IOT, it may be beneficial. There's no blanket clause to be used that says 'Always do this'. I higly encourage folks on this list to setup and use the run_stats method of comparing different access methods. This is something Tom Kyte put together. It is very simple to use. URL: http://osi.oracle.com/~tkyte/runstats.html I've attached my versions of the scripts for your convenience. You can use these to easily compare unindexed vs indexed reads on small tables, indexed vs IOT, etc. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 10:05 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Thanks Jared, What if my developer is selecting all or most of the records from the table and not all the columns in the select list are in the index that should have been used? I understand your point, in fact to use Jonathan's words .. should a small lookup table BE an index (IOT)? ... I am testing this approach here and have found some performance benefit out of it. Cheers Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, April 03, 2003 1:01 PM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Importance: High Raj, Indexing small tables is a good thing if you are doing single row lookups. An index read and lookup by rowid is much more scalable than doing an FTS, even if the table is only 2 blocks. Jared The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask
oracle full table scan
Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arvind Kumar 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: oracle full table scan
Arvind, You probably need to look into Explain Plans or Tracing since they both show what objects are used in queries. Explain Plans are useful to see what a query is likely to do, Tracing is useful when you don't have access to the queries or suspect that what the query is doing will be different to what the explain plan says. There are many, many tools to help you achieve this goal - try www.orafaq.net/tools perhaps. Also, I am curious why someone working at a company called SQL Star International has to go to a list to find this answer? Arvind Kumar [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] rintl.comcc: Sent by: Subject: oracle full table scan [EMAIL PROTECTED] m 03/04/2003 14:58 Please respond to ORACLE-L Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arvind Kumar 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). Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard 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: oracle full table scan
Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind A time for indexed access; and a time for full scans. A time for nested loops; a time for hash joins. The Ecclesiastes, 3:8.3.0.5 Equating full scans to bad performance is not always true. It's more a matter of how much data you browse through compared to what you ultimately want to return. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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).
