so there are benefits of splitting indexes and tables on different mount points in an instance used for batch loads? such as a data publication model where you ingest deltas?
any data on this?
>
> From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
> Date: 2003/07/15 Tue PM 12:04:24 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: RE: RE: should you separate indexes from tables in separate dataf
>
> R,
> My personal theory on where this started was benchmarking. Before the
> Oracle Wait Interface was developed, about the only hard-core information
> you could get on tuning came as a result of people running benchmarks. If
> you benchmark a batch program by itself that uses an index to access a
> table, you will get much better performance if you place the index and table
> on separate devices.
> Now, as has been pointed out, in a busy multi-user system with many
> users, many tables and indexes, you shouldn't just put the indexes on
> separate devices and call it Miller time. You should observe the actual
> usage at a time when performance is critical (Cary Millsap does a nice job
> of describing this, although he focuses on finding critical applications),
> look for devices that are most heavily used, and move data files accordingly
> to spread the load. If you sill feel you must keep the table and indexes
> separate, you can criss-cross, i.e. put table A on device A and index A on
> device B, then table B on device B and index B on device A.
>
> Dennis Williams
> DBA, 80%OCP, 100% DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> Dennis Williams
> DBA, 80%OCP, 100% DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Tuesday, July 15, 2003 10:29 AM
> To: Multiple recipients of list ORACLE-L
> datafiles
>
>
> does anyoen disagree? Didnt this get started with the 'DBA Handbook' or was
> it a different text?
>
> >
> > From: "Mercadante, Thomas F" <NDATFM_at_labor.state.ny.us>
> > Date: 2003/07/15 Tue AM 11:10:05 EDT
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > Subject: RE: should you seperate indexes from tables in seperate datafiles
> >
> > R,
> >
> > Some of it depends on the disk storage. I have always followed the
> > time-proven method of organizing disks and placing indexes away from the
> > tables they belong to.
> >
> > Our warehouse is using EMC external disk. What the warehouse architect
> did
> > was to stripe the EMC disks in such a way that all mount points (Sun
> system)
> > are spread across all the EMC disks. What this does is to spread all
> files
> > in the database across all the EMC drives. And with 4 Gig of EMC cache
> > available, it further disproves the theory that separing indexes from data
> > are required. The end result, in my case, is almost like one big RAM disk
> -
> > where all disk IO is spread across all disk.
> >
> > If you do not have this arrangement, then I would still try and keep
> indexes
> > and data away from each other. But let's face it, we *never* have enough
> > disk mount points, so we end up merging things together somewhat anyway.
> >
> > hope this helps.
> >
> > Tom Mercadante
> > Oracle Certified Professional
> >
> >
> > -----Original Message-----
> > Sent: Tuesday, July 15, 2003 10:49 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > There has been alot of literature stating that you will recieve
> performance
> > improvements by seperating indexes and tables across multiple I/O points.
> >
> > Ie... you have a tables tablespace and an index tablespace. If you put
> them
> > on seperate hard drives, you will have less I/O contention.
> >
> > Now Im seeing some articles stating that this is not true. That oracle
> > actually accesses indexes and tables serially. Now it might be useful
> > seperate indexes from tables for maintenance purposes but this wont lower
> > I/O contention.
> >
> > Can anyone chime in on this? Curious to see where the evidence is leading?
>
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: <rgaffuri_at_cox.net
> > INET: rgaffuri_at_cox.net
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Mercadante, Thomas F
> > INET: NDATFM_at_labor.state.ny.us
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: <rgaffuri_at_cox.net
> INET: rgaffuri_at_cox.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
Received on Tue Jul 15 2003 - 11:13:26 CDT
