Re: Multiple Datafiles and performance?

From: Tim Gorman <tim_at_sagelogix.com>
Date: Thu, 07 Aug 2003 11:19:29 -0800
Message-ID: <F001.005C9803.20030807111929@fatcity.com>

Raj,

The point is that separating these data structures physically does not enhance performance per se. Notice that I'm not arguing against striping and the distribution of I/O...

Rather, more consideration should be given the I/O statistics (only the counts, not the timings!) in V$FILESTAT/V$TEMPSTAT rather than DBA_SEGMENTS.OBJECT_TYPE when considering how to distribute the load...

Thanks!

-Tim

on 8/7/03 11:34 AM, Rajesh.Rao_at_jpmchase.com at Rajesh.Rao_at_jpmchase.com wrote:

>
> Tim,
>
> For arguments sake, the I/O steps that you mention is for a single user.
> Assume thousands of users, in which case, everyone would be hitting the
> same disk volume. Whereas, if they were spread, the I/O would be spread
> across 2 different volumes.
>
> Having said that, I dont recommend spreading them on different disk
> volumes. The goal should be spreading I/O evenly across all the available
> disk volumes. The S.A.M.E principle. Just for the heck of spreading the
> datafiles across disk volumes, I would not want the index datafile to be
> moved from a disk with 20% utilization to one with 90%.
>
> Raj
>
>
>
>
> Tim Gorman
> <tim_at_sagelogix To: Multiple recipients of list
> ORACLE-L <ORACLE-L_at_fatcity.com>
> .com> cc:
> Sent by: Subject: Re: Multiple Datafiles and
> performance?
> ml-errors_at_fatc
> ity.com
>
>
> 08/07/2003
> 11:19 AM
> Please respond
> to ORACLE-L
>
>
>
>
>
>
> 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: Tim Gorman
  INET: tim_at_sagelogix.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 - 14:19:29 CDT