why is it useful to seperate different i/o pattersn? such as multi-block reads and single block reads?
>
> From: "Nuno Souto" <nsouto_at_optusnet.com.au>
> Date: 2003/07/15 Tue AM 11:59:23 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: Re: should you seperate indexes from tables in seperate datafiles
>
> ----- Original Message -----
>
> > I must say that I haven't actually seen the benefits myself but my faith
> > is rock solid and I'll continue to separate data from indexes.
>
> Don't want to debate faith... However, the technical side I can. ;)
>
> The practice of separating indexes and tables into individual
> tablespaces comes from very early in the history of databases,
> as many here know. It made sense then in terms of performance,
> when databases were relatively small and the number of objects
> and users in a database was also small. Besides, in those days
> disks were discrete and controllers controlled each disk
> separately. It was easy to separate loads, for those who
> could be bothered and knew the arcane arts of disk partitioning
> and file system creation.
>
> Nowadays with databases of thousands of tables and indexes,
> with disk farms and multiple disk strings and monster
> caches and disk arrays and logical volume managers, it makes
> no sense whatsoever to separate tables from indexes
> *FROM THE PURE PERFORMANCE POINT OF VIEW*.
>
> However from the maintenance and management point of view,
> it makes a lot of sense. If you ever want to separate
> indexes to a different disk, it's infinitely easier to
> just move a set of datafiles making up a tablespace than
> to have to create tablespaces, move indexes, blah blah.
> In addition indexes tend to have different allocation patterns
> than tables, even in LMT. It makes sense to separate them
> from that point of view.
>
> There is another consideration which you brushed on:
> You normally don't want to mix the type of I/O for indexes
> (small, discrete blocks) from the potential "stringed" reads
> of a full table scan. They have different characteristics
> and cause all sorts of different I/O prioritization at controller
> and cache level. As such, they should not reside in the same
> logical device. This means in effect: not the same tablespace.
> Having said that, all sorts of tricks are possible at LVM level
> to avoid this even in single tablespace or single file system.
>
> But once again: this is all very relative and highly dependent
> on the type of database and its use, type of hardware, etc.
>
> Bottom line: separate if you feel comfortable doing so. You do
> NOT have to move the separate tablespaces into different disk
> devices: it all depends on I/O patterns, configuration, load
> management and so on. Analyze I/O patterns and loads, find
> the bottleneck (if there is one!) and solve it.
>
> Do not do things just because someone says they "should/should not
> be done". And that includes the above. ;)
>
> Try and apply a "one size fits all" policy to your work and
> sooner or later you'll be replaced by a program or outsourced.
> You are a DBA: think.
>
> Cheers
> Nuno Souto
> nsouto_at_optusnet.com.au
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Nuno Souto
> INET: nsouto_at_optusnet.com.au
>
> 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
Received on Tue Jul 15 2003 - 11:20:16 CDT
