Prev: Partitioning Best Practices
Next: Change ip address on 10g / windows 2003 rac nodes
From: Ronny on 8 Jul 2008 19:41
We are on 10.2.0.4 on Linux SLES-10 (64-bit) and we are analyzing the
IO setup of an OLAP/DWH system (about 800GB in total). We have a large
table of about 50 GB in size and it is stored in its own tablespace.

Now the question comes up whether few big (say 2 x 25GB) or many small
(say 25 x 2GB) datafiles are more beneficial. We have currently
DB_WRITER_PROCESSES=8.

Can there be only one file handle to access a datafile? Or can a
datafile be accessed by more than one db writer process? So dbw0 could
read at the beginning of a datafile, and dbw1 could read at the end at
the same time.

Any suggestions for a good datafile size independently from
DB_WRITER_PROCESSES?

Thanks for your comments,
Ronny
From: Michael Austin on 8 Jul 2008 20:54
Ronny wrote:
> We are on 10.2.0.4 on Linux SLES-10 (64-bit) and we are analyzing the
> IO setup of an OLAP/DWH system (about 800GB in total). We have a large
> table of about 50 GB in size and it is stored in its own tablespace.
>
> Now the question comes up whether few big (say 2 x 25GB) or many small
> (say 25 x 2GB) datafiles are more beneficial. We have currently
> DB_WRITER_PROCESSES=8.
>
> Can there be only one file handle to access a datafile? Or can a
> datafile be accessed by more than one db writer process? So dbw0 could
> read at the beginning of a datafile, and dbw1 could read at the end at
> the same time.
>
> Any suggestions for a good datafile size independently from
> DB_WRITER_PROCESSES?
>
> Thanks for your comments,
> Ronny

How many spindles (or logical volumes - and hopefully not just
partitions of the same devices).

And yes there can be more than one process accessing datafiles at the
same time. (parallel read/writes).

Google/Metalink search for db_block_lru_latches and db_writer_processes.

And 50G is NOT a big datafile.
From: Mark D Powell on 9 Jul 2008 10:40
On Jul 8, 8:54 pm, Michael Austin <maus...(a)firstdbasource.com> wrote:
> Ronny wrote:
> > We are on 10.2.0.4 on Linux SLES-10 (64-bit) and we are analyzing the
> > IO setup of an OLAP/DWH system (about 800GB in total). We have a large
> > table of about 50 GB in size and it is stored in its own tablespace.
>
> > Now the question comes up whether few big (say 2 x 25GB) or many small
> > (say 25 x 2GB) datafiles are more beneficial. We have currently
> > DB_WRITER_PROCESSES=8.
>
> > Can there be only one file handle to access a datafile? Or can a
> > datafile be accessed by more than one db writer process? So dbw0 could
> > read at the beginning of a datafile, and dbw1 could read at the end at
> > the same time.
>
> > Any suggestions for a good datafile size independently from
> > DB_WRITER_PROCESSES?
>
> > Thanks for your comments,
> > Ronny
>
> How many spindles (or logical volumes - and hopefully not just
> partitions of the same devices).
>
> And yes there can be more than one process accessing datafiles at the
> same time.  (parallel read/writes).
>
> Google/Metalink search for db_block_lru_latches and db_writer_processes.
>
> And 50G is NOT a big datafile.- Hide quoted text -
>
> - Show quoted text -

Actually depending on the Oracle bock size 50G may well exceed the
largest file size that Oracle can support on a small file tablespace.
With an 8K block I believe the limit is 32G while if you use a bigfile
tablespace you can get up somewhere aroung 128T.

In my opinion fewer larger files would be better. During a checkpoint
operation every file header has to be updated. During startup before
opening the database Oracle has to check every file header. Generally
speaker you want to use as few files as practical for housing your
database. What is practical depends on your OS limitations which vary
by version, the Oracle version, your backup startegy, your tape backup
system limitations, and so on.

HTH -- Mark D Powell --



From: Chuck on 9 Jul 2008 11:33
Ronny wrote:
> We are on 10.2.0.4 on Linux SLES-10 (64-bit) and we are analyzing the
> IO setup of an OLAP/DWH system (about 800GB in total). We have a large
> table of about 50 GB in size and it is stored in its own tablespace.
>
> Now the question comes up whether few big (say 2 x 25GB) or many small
> (say 25 x 2GB) datafiles are more beneficial. We have currently
> DB_WRITER_PROCESSES=8.
>
> Can there be only one file handle to access a datafile? Or can a
> datafile be accessed by more than one db writer process? So dbw0 could
> read at the beginning of a datafile, and dbw1 could read at the end at
> the same time.
>
> Any suggestions for a good datafile size independently from
> DB_WRITER_PROCESSES?
>
> Thanks for your comments,
> Ronny

The real question IMO is about recoverability. If you lose a datafile,
how long can you afford to be down? A 50g file takes a whole lot longer
to restore and recover than a 4g file. Will you ever need to move the
file to balance I/O or space? If so, same problem with a large file.

I don't create datafiles larger than 4g.
From: Ronny on 11 Jul 2008 09:44
Thanks for your comments so far.

For my question I want to disregard aspects like
- any sort of partitioning
- managebility / administrability
- backup and recovery
- IO paths / physical disks / number of spindels
(although these aspects are of course worth to be discussed in
different threads).

I want to set the focus just on performance. If I look at your
comments I conclude that it is nearly the same (few big vs. many
small). Maybe there's a bit of an advantage for fewer larger files due
to checkpoint operations with the need to update every file header.
But isn't it a mostly theoretical point of view? Is this time
difference really measurable?

Ronny
 |  Next  |  Last
Pages: 1 2
Prev: Partitioning Best Practices
Next: Change ip address on 10g / windows 2003 rac nodes