Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Multiple Datafiles and performance?

RE: Multiple Datafiles and performance?

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Thu, 07 Aug 2003 07:59:24 -0800
Message-ID: <F001.005C9788.20030807075924@fatcity.com>


Hey Tim,

I just wanted to point out an excellent thread (the responses, not my question) on this list from exactly one year ago titled "Checking the rebuildability of an index", which can be searched at fatcity.com using "rebuildability" and the year 2002 (be kind to search engines).

Rich

Rich Jesse                           System/Database Administrator
rjesse_at_qtiworld.com                  Quad/Tech Inc, Sussex, WI USA


> -----Original Message-----
> From: Tim Gorman [mailto:tim_at_sagelogix.com]
> Sent: Thursday, August 07, 2003 10:19 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Multiple Datafiles and performance?
>
>
> I don't mean to be argumentative, but every time I see assertions like
> these, I suspect someone has been reading some rather
> discredited books...
>
> So, my apologies in advance, but comments are inline below...
>
> >
> > In my experience, spreading datafiles across volumes
> (specially if you are
> > careful not to locate the a table's datafiles and its
> indexes datafiles in the
> > same drive) greatly increases performance.
>
> The assertion that performance is enhanced by distributing datafiles
> containing tables and datafiles containing indexes to
> different volumes is a
> myth.
>
> Think about it.
>
> Indexed access is a purely sequential activity from an I/O standpoint,
> putting aside the reality that a buffer cache exists. First,
> we access the
> root block of the index and read its contents in order to
> know where to
> perform the next I/O (i.e. a branch block). Then we read
> that branch block
> and read its contents in order to know where to perform the
> next I/O (i.e. a
> leaf block). Then we read the leaf block and read its
> contents in order to
> know where to perform the next I/O (i.e. a block in a table).
> And so on...
>
> Since we are performing sequential single-block I/O (hence
> the name of the
> wait event "db file sequential read"), how can separating datafiles
> containing tables from datafiles containing indexes matter to
> performance?
>
> >
> > As for the file size, I can not say because I have not
> tested it, but I think
> > it should have no real impact compared to splitting it.
> Reorganizing the
> > database regularly is a better way to optimize performance.
>
> And in what ways does "reorganizing the database regularly" improve
> performance?
>
> To break the question down into more manageable pieces:
>
> * In what way does rebuilding a table improve performance?
> * In what way does rebuilding an index improve performance?
>
> There are specific answers to these questions. For example, there are
> situations in which both tables and indexes can become
> "sparsely populated".
> Tables become sparsely populated due to large-scale
> deletions. Indexes
> become sparsely populated due to monotonically-ascending data values
> inserted transactionally.
>
> In these cases, how can you detect this condition? The
> package DBMS_SPACE
> has procedures that help for tables and the ANALYZE INDEX ... VALIDATE
> STRUCTURE command helps for indexes.
>
> Does rebuilding a table or index that is not "sparsely populated" aid
> performance in any way? Quite frankly, no...
>
> ...well, there is one condition involving the "clustering
> factor" of an
> index where a rebuild of the table can help, but you'll end
> up hosing the
> "clustering factor" of other indexes. It is a case of
> favoring one index
> over another, and that is a decision that requires intimate
> knowledge of the
> application's usage of the table and its indexes...
>
> So, "reorganizing the database" on a regular basis is purely
> a waste of
> time. Regularly monitoring the database for "sparsely
> populated" tables and
> indexes, and then determining if the condition is affecting
> performance of
> any important processes before rebuilding, will indeed help
> performance.
>
> Sorry for the combative tone, but I hope this helps...
>
> -Tim
>
>
> >
> >
> > -----Mensaje original-----
> > De: Dave Phillips [mailto:dphillips_at_gasper-corp.com]
> > Enviado el: miércoles, 06 de agosto de 2003 22:14
> > Para: Multiple recipients of list ORACLE-L
> > Asunto: Multiple Datafiles and performance?
> >
> >
> > Oracle 8.1.7.4
> > Win2k
> >
> > What is the consensus on datafile sizing and the impact/overhead
> > multiple datafiles have on performance?
> >
> > For example, if I have one 2.5g datafile, and three 1g
> datafiles, and I
> > need more space, would it be better to increase the size
> of the 1g to
> > 2g or add another 1g datafile?.
> > Is it better to keep them all uniform in size?
> >
> > I would think having multiple datafiles that could be spread across
> > drive volumes would be beneficial, am I wrong? (Wouldn't be
> the first
> > time :)
> >
> > TIA
> >
> > David Phillips
> > Support DBA
> > Gasper Corp.
> > BAARF member #30

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.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
(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 Aug 07 2003 - 10:59:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US