RE: Data Warehouse configuration

>From what I've seen, under the circumstances I described, Oracle appears to be allocating extents in 'round robin' style across the datafiles. For example, as I insert data into partition 1, 1st extent is in file 1, next extent in file 2, next in file 3, and so on. Now, truly, for this to be beneficial or not depends on how this particular data is commonly accessed. In my case, the table is partitioned by month on a date column and contains data spanning several years. Uniform size is 10M and each partition is 8 to 12 extents, so it ends up striped pretty well across the file systems. The typical query selects data for month to date or year to date this year and the same date range for last year, so with a small degree of parallelism (6 to 10 (12 proc machine)) it seems we should get pretty optimized reads from that table.
Does this really matter with our storage system cache? Don't know yet but it's fun to play with.

<<< DWILLIAMS_at_LIFETOUCH.COM 3/10 4:24p >>> Darrell - Maybe I'm a little slow today (after all it is Monday), but why wouldn't you just create 6 tablespaces and assign each partition to its own tablespace?

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Monday, March 10, 2003 3:36 PM
To: Multiple recipients of list ORACLE-L

One thing I like to take advantage of (when you have disk planning available) is to take a tablespace, which will hold a very large partitioned table, and create it as (for example) 6 files, each on a file system on a different disk and extent management local, uniform allocation. When you create and populate your partitioned table, Oracle will fill each of the six files evenly. You've just striped your I/O.

<<< DWILLIAMS_at_LIFETOUCH.COM 3/10 2:53p >>> E

   I haven't seen anything. OFA is, in my recollection, primarily about putting files in standard locations so another DBA can come on-site and find stuff easily. Before OFA we all tended have our own preferences, since there were no guidelines.

   DW tends to be like OLTP, only more so ;-) You have to remember you are dealing with a lot more data, usually. You need to allow larger areas for landing areas for files that are transferred from another system for you to load. Just like OLTP, you want to get as many disk spindles involved as possible.

   Most data warehouses have two modes of operation, data refresh and queries. During the data refresh, you have the disk storing the data you will be loading, as well as the disks that will receive the data, and perhaps a temp space that will help rebuild indexes.

   Laying out disk for queries is much the same as OLTP systems, except you may have fewer critical queries to optimize for. Often OLTP systems are able to cache more of the data a query needs than you are able for DW queries.

   Another wrinkle is that after you've loaded the data, you may have a phase of building your materialized views.

   Which phase is the most important to optimize will depend on your DW situation, how large (in time) the loading window is.

   Another factor that will vary considerably between sites is the recovery time requirement.

   I don't know if the whole DW scene is less mature than OLTP, but there seems fewer rules of thumb for data warehouses. Or maybe it is just the nature of the beast.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Monday, March 10, 2003 1:04 PM
To: Multiple recipients of list ORACLE-L

Are there any suggested recommendations for setting up the configuration of a data warehouse on new hardware? I understand the old OFA method (seperating data from indexes, etc). What I am wondering is if there are any recommendations specific to data warehouses that should be considered that will help improve performance, recovery, etc.?

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Darrell Landrum
  INET: dlandrum_at_zalecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).