Re: Raid and size of datafile(s)

"Armin Ollig" <ollig_at_biofrontera.de> wrote in message news:39129F68.870D022D_at_biofrontera.de...
> Dear colleagues,
>
> i plan to stripe my oracle instance across 4
 raid0+1 slices.
> However i dont find any usefull information about
 the
> layout of the datafiles. Since my oracle and OS
 are 64bit i
> dont have a 2GB limit. Hence imho it would be a
 good thing
> to have about 4 very big datafiles on each raid
 instead of
> e.g. 20 2GB files since oracle accesses all
 datafiles in
> parallel and having 20 io threads on one raid will
 certainly
> not impove perfomance.
>
> What is the appropriate method to optimze the
 number
> and size of datafiles in order to get the best
 performance
> ?
>
> Thanks in advance,
> --Armin
>
> --
> "To save energy
> the light at the end of the tunnel
> will temporarily be switched off."
>

"Due to an unexpected buget constraints,

    Funding for smoke has now been eliminated.

       The project will continue utilizing mirrors alone."

The best method for determining size and placement of files for an oracle database starts with understanding  the nature of i/o that will be performed on the different types of files that make up the Oracle database.

There are lots of questions you need answers to:

Is the database going to be used for OLTP or as a "data warehouse"? What type of applications? Will the database be in archivelog mode? How large will the tables be? What is the frequency of well-defined transactions, or will the vast majority of work be ad-hoc selects that look at lots of rows ? How many disk drives and controllers will be available? how much memory will be available for data base block buffers?

The answers to these questions will help you determine how to optimize the size and placement of the datafiles across the available resources, to minimize i/o contention (waits) and optimize overall throughput of the i/o subsystem.

In any case, you'll need more than 4 files. At a minimum:
2 files for redo logs,
1 file for system tbs,
1 file for rollback tbs,
1 file for temp tbs.
OFA recommended:
1 file for tools tbs
1 file for users tbs
then Add
tablespaces to hold data and
indexes for the applicaton(s).

What do you mean, by "oracle accesses all files in parallel" ? The Oracle processes read from and write to specific files when they need to. Each concurrent query has it's own associated process(es) that read data from the datafiles. Writes to the database files, on the other hand are handled by the dedicated ARCH, LGWR, DBWR, etc. processes.

"striping" some of the Oracle datafiles over multiple disks can possibly improve performance of the database. But having a good understanding of the type and the frequency of i/o that will be performed (random reads, sequential reads, sequential writes) on each of the datafiles is an important step in identifying those files that would benefit most from striping, and in choosing an effective stripe size for those files.

If you are looking for optimum performance from your database, I would recommend that you resist a temptation to stripe everything everywhere, at least without understanding the impact that can have on the overall performance of the database.

HTH Received on Fri May 05 2000 - 00:00:00 CDT