Striping XP256 for a Oracle 8.1.7. Data Warehouse

[Thomas Lewis]

I have a 400GB Oracle 8.1.7 data warehouse deployed onto a XP256. The warehouse often needs to full table scan tables of c50GB and uses parallel execution to achieve this. The database is using 8K blocks and the DB_FILE_MULTIBLOCK_READ_COUNT is set to 16.

The XP256 has been configured with RAID 5 and a stripe size of 256K. Its 36 disks have been divided into 8 logical volumes of 4-6 disks each with different tables or indexes assigned to each logical volume to avoid contention.

I really need some help on two fronts.

What is the optimal relationship between the DB block size and the stripe size. Currently, I assume that the database will read 32 blocks off disk 1 and then 32 off disk 2 etc. etc. That does not sound very parallel to me. Should the stripe size be smaller?

Secondly, is it necessary to divide the XP256 into different volumes for different purposes or is a larger single volume a better approach?

Many thanks in advance

Tom

[Alexander M. Ermes]

Hi there.
If you setup lvols, use disks from different
paths through different interfaces. That will spread the load.
Stripe size : think abot a stripe size of 64k.
This will be an amount of data, that will be read into the cache of the XP and remain there, if necessary. Check your hit ratios with the Oracle Enterprise Manager Tuning and Diagnostic pack, then you will have data to make a decission.
Do not make the volumes too large.
We have setup 48 disks in six VG and every VG contains 8 lvols with two disks of 9 GB.
We connected the disk array with four fibre channel adapters and run Oracle Applications on it. The performance is satisfying.
Keep in mind, that you might need some space for an export of the database for a reorg.
If you need more info, let us know.
Rgds
Alexander M. Ermes

[Mark Stockham]

I am assuming that the cache configuration is something that should be set by an engineer at config time, and that there is not a great deal of modification available to the "operator" is this correct, if not, is it possible to "tweak" the caching characteristics for a "long" read ahead> Addtionally, is the cache configurable on a per volume basis. Thanks

[Stefan Farrelly]

There was a recent question about this. Here is the link which gives a much greater level of detail;

http://www.dutchworks.nl/htbin/hpsysadmin?h=3&dn=75782&q=db%5ffile%5fmultiblock%5fread%5fcount&fh

Weve been testing this setup here recently. We found the best performance to be using LVM striping, RAID1 (not Raid0/1 or Raid5) and with a stripe size of 64k. This is what Oracle also recommend in a recent document posted on the ITRC on Oracle database performance, although they recommended a larger stripe size.