Question:  I am considering using ASM or RAID-10 and I need to decide what my optimal disk stripe size should be.  What are the rules for determining the best disk stripe size for my Oracle disks?

Answer:  The larger the stripe size, the more efficient db file scattered read operations will be (full-table scan I/O), because the disk read-write heads will only have to relocate once under each disk cylinder.  At the extreme, a too large stripe size may cause disk I/O bottlenecks if popular data resides on a contiguous disk, causing disk enqueues, the old "hot disk" problem.

Conversely, a tiny stripe size will load balance the disks ensuring that all hot spots are random, but it does so at the expense of slower full-table scan I/O operations.

One meg stripe size

Today, Oracle recommends a middle of the road stripe size of 1 megabyte (1M) stripe size, large enough to make full-table scans efficient and a small enough stripe size to prevent hot disk problems.

If you chose ASM to perform your striping, you're definitely using a 1MB stripe size unless you've changed the default ASM file templates. If you are not using ASM, the choices of Oracle stripe size  depends on your SAN hardware (if it has hardware level RAID) or the software you're using for RAID.

For more complete details on mapping-out complex RAID stripe sizes, see my tips for optimal RAID on Oracle.

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.