| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: sequential reads/scattered reads
This is the kind of knee-jerk reaction that folks have to think harder
about. Having data tablespaces and index tablespaces on the same "physical"
device (is it really a "physical" device, or is it a "logical" device?) does
not equal I/O contention. Maybe (just maybe) on your laptop or desktop, but
not on properly configured current storage subsystems. The "conventional
wisdom" to separate tables and indexes to separate tablespaces is not based
on performance considerations, but rather on availability considerations.
I/O to tables and I/O to indexes are vastly different. The idea that tables
and indexes need to be segregated to separate I/O devices in all cases is a
myth, and an expensive one.
Please use the information available in this forum and on websites such as
www.oraperf.com, www.hotsos.com, www.orapub.com, www.ixora.com.au, www.miracleas.dk, www.oaktable.net, www.jlcomp.demon.co.uk, and even www.evdbt.com to interpret the information readily available in the V$ viewsto determine the exact cause and location of I/O contention. Improving I/O performance consists both of reducing the "amount of I/O requests" as well as reducing the "cost per I/O request". Reducing the amount of I/O is SQL tuning; reducing the cost per I/O involves optimizing the I/O subsystem. In the vast majority of cases, the very best improvement that can be expected in reducing the cost per I/O is 10-50% (i.e. incremental improvement) at best, which is of course dependent on where you are starting from and where you can afford to get to. In contrast, reducing the volume of I/O requests by tuning SQL statements can provide performance improvements ranging anywhere from 1% to infinity, with the majority of improvements tending toward infinity. If you were going to put your scarce money behind one or the other approach, which would you pursue?
>
> If you can move the indexes to a separate physical device (and their own
> tablespace/datafile) then you will reduce I/O contention. If the
> tablespace/datafile is on the same RAID device then you will be using the
> same r/w heads and you will not reduce I/O contention.
>
>
>
> oraora
> oraora To: Multiple recipients of
list ORACLE-L
> <oraoraora <ORACLE-L_at_fatcity.com>
> @rediffmail.c cc:
> om> Subject: sequential
reads/scattered reads
> Sent by: root
>
>
> 07/11/2002
> 07:33 AM
> Please
> respond to
> ORACLE-L
>
>
>
>
>
>
> Guys,
>
> Querying my v$session_wait shows 10 rows each DB FILE SEQUENTIAL
> READ and DB FILE SCATTERED READ.
>
> manuals explain it all b'coz of I/O.
>
> there are 33 tables and 110 indexes in the same USERS tablespace
> with only one datafile USERS01.DAT of size 12GB.
>
> it's physically laid out in D:\oracle\data.
> but the hard disk capacity is 100GB with 8 disk heads and RAID5.
>
> will not moving the tables and indexes to seperate tablespace
> reduce I/O traffic ? my manager here says that since it is
> configured with RAID5 and 8 disk heads , it's not a problem.
>
> is it so ? is he right ?
> plz. clarify me. i need to explain him.
> it's urgent.he is sitting on my head.
>
> TIA.
> _________________________________________________________
> There is always a better job for you at Monsterindia.com.
> Go now http://monsterindia.rediff.com/jobs
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: oraora oraora
> INET: oraoraora_at_rediffmail.com
>
> 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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Thomas Day
> INET: tday6_at_csc.com
>
> 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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: Tim_at_SageLogix.com 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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Jul 11 2002 - 12:07:31 CDT
 |
 |