Re: Oracle Data Warehousing, UNIX and large file-enabled file systems

On 25 Mar 2002 11:37:04 -0800, don.gillespie_at_mts.mb.ca (Don Gillespie) wrote:

I recently built a .5 TB datawarehouse on Sun. With Veritas VXFS you can get close to raw performance with the mount options with or without Quick I/O. Managing RAW devices is a pain in the ying yang! Users very pleased!

Big issues inlcude stripe sizes, stripe sets, and max io size. This is very important as your DW will do lots of large sequential reads. You did not say how reguarly you would update it.

Use:

CBO
Local tablespaces
Parallel I/O at 8.1.7.3 and higher have fewer problems. Direct Loads if possible
16K blocksize (highest on Solaris)
Materialized views
Bitmap indexes
Paritioning.
Create diminisions to better influence the STAR TRANSFORMATION.

Review Oracle Datawarehousing guide

Export is gonna be slow but it can be done.

>I am the DBA for a data warehouse environment that is expected to get
>to about 3TB. That would mean about 1500 or so data files with the
>2GB file limit. Besides being a nightmare to manage that many files,
>I anticipate the overhead on checkpoints would be tremendous. The
>environment is 32-bit Oracle (possibility of 64 bit in the future) on
>AIX with disk storage on an IBM Shark SAN (RAID5, 32K stripe; no
>choice here), with a 16K Oracle block size (the max allowed). We are
>using Journaled File Systems, not raw partitions. I am contemplating
>the use of large file-enabled JFSs for all JFSs that would contain
>oracle data files, log files and control files. But I don't know much
>about them, and I am wondering if there are serious performance, space
>consumption or administration issues in doing so.
>
Received on Fri Mar 29 2002 - 05:22:20 CST