Re: Warehouse Stripe Size with Oracle8

Howard,

I'm working on a DW too.

I executed several tests and IMHO my personal belief is that no one can really answer the question specifically. In broad generalities, some conclusions can be said. You have to take time and run your own tests on your box.

Here's my $.02. They should be taken with a grain of salt based on the above statement.

I ran tests using 8K and 16K raw and file system (32K was never really considered). Striped across 20 or 5 disks.

I ran a series of "large" ins/up/del/ and SQL*Loads, selects, create indexes, truncates... I shut down the instance between runs. I also set up 50 cuncurrent users tablescanning. We have 256Mb of Level2 cache between the disks and the OS - so it is possible things were still cached there. All of this was RAID5. This is a UNIX box.

Some items ran better than others, but overall it was very close

I can state that 20 disks beat the pants off of the 5 disks, and most importantly the 20 disk stripes never were near the 90% busy threshold - the 5 disk array could be rather easily be pushed to 100%. The file systems ran just as fast as the raw - faster in some cases. The ORACLE blocking factor in the big picture comapring all scenarios ins, upd, del, create index... was very close, but 16K beat 8K by a whisker. This was based on weighted averages based on our test importance. So we are running 16K on 20 disk stripes on RAID5. Our block size at the OS level was 8K.

Does this apply to you - maybe, maybe not. It all has to be proven out by executing your own tests.

Hope this help out some. My take on it is test, test, test - it took me about 1 week of solid work to get everything set up and run. Slammed it into a spread sheet - and if anyone ever questions me, I pull out the tests. Nothing beats cold hard numbers.

BTW - The 512 byte stripe sounds low to me (fow what it's worth, we are at 8K). Can you have the VMS guys prove to you via their tests that they are right? If not have them do the tests, or certify in writing that that they are correct and stand behind their conclusions. That way when users bitch about slow performance you're covered.

Sounds like the easy answer to me. ;-)

JB

howard.galusha_at_thehartford.com wrote:

> I am a data warehousing consultant working with Oracle8. I have some
> experience with Oracle on UNIX, but I'm certainly not a DBA and I'm looking
> for advice with the following issue:
>
> We are running Oracle 8.03 on a 4-way DEC Alpha under VMS (please don't ask
> why, it is a long story.) We have over 300 GB of disks, set up in various
> RAID arrays (RAID 1, RAID 0+1, RAID5).
>
> Our system DBA's set up the Oracle blocksize to be 32K (to oltp folks, that
> may sound somewhat high, but for data warehousing, it's considered kosher.)
> The disk arrays were set up with a 512 byte stripe size.
>
> Now, intuitively, the stripe size sounds way too small - I picture the o/s
> getting swamped performing 64 i/o's to read 1 Oracle block (and in data
> warehousing, you are reading lots of blocks). Now apparently the disk
> controllers have a 32 MB cache to mitigate that somewhat, but I've read
> information (page 58 of Oracle8 Data Warehousing by Dodge and Gorman, Oracle
> Consulting's Physical Database Design Guide by Rainer Runge) that says the
> minimum stripe size should be at least 2X the DB_BLOCK_SIZE.
>
> This has been deemed "probably correct in theory" by our VMS team, who are
> unwilling to change the set up based on anything other than practical
> experience (they haven't had any problems with 512 bytes in their oltp
> systems and this is their first encounter with a warehouse.)
>
> Does anybody out there have any "practical experience" with striping an Oracle
> data warehouse under VMS, or have enough Oracle/VMS experience to refute the
> recommendations from Oracle and Mssrs. Dodge and Gorman? I'm concerned that
> proceeding with this set up will burn us and will be a ton of work to "undo".
>
> Thanks,
>
> Howard Galusha
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Tue Feb 16 1999 - 19:08:37 CST