Recommended stripe depth for Oracle on a Raid?

In the next weeks, I will have the seldom opportunity to rebuild the
storage containing the DB. All file systems will be reformated.

We are using a CX-400 (Dell/EMC) connected via FibreChannel to a Sun
Server; Oracle is 8.1.7 by now but will be 9.2.0.4 after rebuilding
the Raid.

Papers I have read say that for Oracle, one should set the stripe
depth (the amount of data in KBytes per Disk) according to the product
(Oracle block size X DB_FILE_MULTIBLOCK_READ_COUNT), in my case:
8x32=256K.

I just don't understand this theory. That would mean that every I/O in
a full table scan would only read from one disk at a time, one disk
after the next - bad.

However, somewhere else a rule of thomb is mentioned saying that the
total amount of data per I/O (i.e. blocksize x
DB_FILE_MULTIBLOCK_READ_COUNT) should correspond to the stripe width.
The stripe width is (the stripe depth X the number of disks in the
raid group) - we have 8 disks (+1 parity +1 spare). This advice seems
more reasonable to me, because every I/O would be serviced by ALL
disks in parallel.

So what do you recommend? The number of users is about 5, but they use
batch jobs spending hours with DB work.

Thanks a lot
Rick Denoire

Rick Denoire wrote:
In the next weeks, I will have the seldom opportunity to rebuild the
storage containing the DB. All file systems will be reformated.

We are using a CX-400 (Dell/EMC) connected via FibreChannel to a Sun
Server; Oracle is 8.1.7 by now but will be 9.2.0.4 after rebuilding
the Raid.

Papers I have read say that for Oracle, one should set the stripe
depth (the amount of data in KBytes per Disk) according to the product
(Oracle block size X DB_FILE_MULTIBLOCK_READ_COUNT), in my case:
8x32=256K.

I just don't understand this theory. That would mean that every I/O in
a full table scan would only read from one disk at a time, one disk
after the next - bad.

Each read only has to do 1 disk I/O = good.


However, somewhere else a rule of thomb is mentioned saying that the
total amount of data per I/O (i.e. blocksize x
DB_FILE_MULTIBLOCK_READ_COUNT) should correspond to the stripe width.
The stripe width is (the stripe depth X the number of disks in the
raid group) - we have 8 disks (+1 parity +1 spare). This advice seems
more reasonable to me, because every I/O would be serviced by ALL
disks in parallel.

Every I/O hits every disk, so you get no speedup from having multiple
disks = bad.

If you have many I/Os going on, you want them to be hitting different
disks so they don't interfere with each other. If you had only one job
running, like backing up the whole DB, then you would want it to do large
I/Os that stripe across all of the disks to improve bandwidth as you
describe.

Most DB access is not bandwidth limited it is IOPS limited.


So what do you recommend? The number of users is about 5, but they use
batch jobs spending hours with DB work.

Thanks a lot
Rick Denoire


-- Jason

Le Thu, 30 Oct 2003 00:01:29 +0100
Rick Denoire a =E9crit:

In the next weeks, I will have the seldom opportunity to rebuild the
storage containing the DB. All file systems will be reformated.

We are using a CX-400 (Dell/EMC) connected via FibreChannel to a Sun
Server; Oracle is 8.1.7 by now but will be 9.2.0.4 after rebuilding
the Raid.

Papers I have read say that for Oracle, one should set the stripe
depth (the amount of data in KBytes per Disk) according to the product
(Oracle block size X DB_FILE_MULTIBLOCK_READ_COUNT), in my case:
8x32=3D256K.

It's not totally True.
In fact, Oracle will run a 32x8=3D256K IO only if all the 32 Oracle's
Block are not in the SGA.
The right way to understand the rule "(Oracle block size X
DB_FILE_MULTIBLOCK_READ_COUNT)" is :
Oracle will run a maximal size of 32 block.

So in a sequential read, if oracle need 45 block, if the block 5 and
41,42,43,44 are in the SGA you will get the following IO:

#IO Block Location
1 1-4 Disk
2 5 SGA
3 6-37 Disk
4 38-40 Disk
5 41-44 SGA
6 45 Disk

For you Striping size, you could use a size of 8k (one oracle block)
or a multiple of this size.

But tunning for DB performance is not simple.
There isn't an universal solution. The solution greatly depend of the
database profil. There isn't the same profile on a full transactional DB
or on a Datawarehouse DB. One is playing with small random IO mostly in
read an the other one use more batch IO (sequential).

Bur sequential IO is right only from Oracle point of vue.
Once the DB live, then the block are written an deleted. So Oracle
look blocks as sequential blocks in the datafile but in fact they aren't
written sequentially on the disk. You have to take care too to the
FileSystem BlockSize. The best way is to have the FS BlokSize and the
Oracle BlokSize at the same value.

I work on HP-UX servers. With JFS we use synchronous IO so only One IO
could be done at one one time on a FS or on a disk. So we only one FS
for an entire big DB (450G), you can only do one IO after one for
adressing the entire DB. This is a serious bottleneck.

A basic line to get perfomance is to spread as possible the IO, on disk
(or lun) of course but on FS too, and over multiple ADAPTER if you have
a high IO charge or. In this case, you could have a better load
balancing accros the entire JBOD.

For exemple, i plan the layout for a 450Go DB with Oracle Application.
To get short response time in the transactionnal mode, i spread the DB
on 100 Lun (8G) with separate FS for index, data, redolog, ...
This work on an IBM ESS 2105 800 with 4 FC direct attachement with LVM
Distribution on 20VG and 40FS. But for planning this, i work with the DB
Administrator, with the IO number for each tablespace, the R/W ratio,
and so on...
If you want to get high performance DB you will have an hard work.

@++
Nicolas CHEVE

I just don't understand this theory. That would mean that every I/O in
a full table scan would only read from one disk at a time, one disk
after the next - bad.

However, somewhere else a rule of thomb is mentioned saying that the
total amount of data per I/O (i.e. blocksize x
DB_FILE_MULTIBLOCK_READ_COUNT) should correspond to the stripe width.
The stripe width is (the stripe depth X the number of disks in the
raid group) - we have 8 disks (+1 parity +1 spare). This advice seems
more reasonable to me, because every I/O would be serviced by ALL
disks in parallel.

So what do you recommend? The number of users is about 5, but they use
batch jobs spending hours with DB work.

Thanks a lot
Rick Denoire

"Nicolas CHEVE" wrote in message
et...
Le Thu, 30 Oct 2003 00:01:29 +0100
Rick Denoire a écrit:

In the next weeks, I will have the seldom opportunity to rebuild the
storage containing the DB. All file systems will be reformated.

We are using a CX-400 (Dell/EMC) connected via FibreChannel to a Sun
Server; Oracle is 8.1.7 by now but will be 9.2.0.4 after rebuilding
the Raid.

Papers I have read say that for Oracle, one should set the stripe
depth (the amount of data in KBytes per Disk) according to the product
(Oracle block size X DB_FILE_MULTIBLOCK_READ_COUNT), in my case:
8x32=256K.

It's not totally True.
In fact, Oracle will run a 32x8=256K IO only if all the 32 Oracle's
Block are not in the SGA.
The right way to understand the rule "(Oracle block size X
DB_FILE_MULTIBLOCK_READ_COUNT)" is :
Oracle will run a maximal size of 32 block.

So in a sequential read, if oracle need 45 block, if the block 5 and
41,42,43,44 are in the SGA you will get the following IO:

#IO Block Location
1 1-4 Disk
2 5 SGA
3 6-37 Disk
4 38-40 Disk
5 41-44 SGA
6 45 Disk

For you Striping size, you could use a size of 8k (one oracle block)
or a multiple of this size.


I suspect that in a case like the one described above you'd still be better
off using the 256 KB per-disk stripe unit. Because if the Oracle code is at
all smart (which it should be), it will issue all the I/O requests required
by a single logical read operation in parallel, and lower-level
software/firmware will coalesce the individual pieces into the minimum
number of actual platter accesses required to satisfy it. So all the
transferred blocks that fall within each single 256 KB range will be fetched
in a single disk rotation, and the number of disks touched by the overall
logical request will be limited (leaving other disks free to satisfy other
parallel requests) without noticeably increasing the latency in satisfying
the request (though if you go much above 256 KB with current disks, you may
start to see latency increases: that's fine if you're
parallel-throughput-limited, but for sequential single-threaded processing
you might be better off using smaller per-disk stripes).

- bill

We are using a CX-400 (Dell/EMC) connected via FibreChannel to a Sun
Server; Oracle is 8.1.7 by now but will be 9.2.0.4 after rebuilding
the Raid.

Papers I have read say that for Oracle, one should set the stripe
depth (the amount of data in KBytes per Disk) according to the product
(Oracle block size X DB_FILE_MULTIBLOCK_READ_COUNT), in my case:
8x32=256K.
There is an excellent white paper from EMC about optimizing the Clariion for
different workloads.

EMC has a pretty good realationship with Oracle, your EMC SE should be able
to point you

to specific guidelines on setting up the Clariion box for Oracle.

Good luck,

Greg G