[prev in list] [next in list] [prev in thread] [next in thread] 

List:       odtug-warehouse-l
Subject:    RE: Data Warehouse Initialization Parameters for Oracle and
From:       "Jesse, Rich" <Rich.Jesse () qtiworld ! com>
Date:       2002-07-02 15:43:22
[Download RAW message or body]

You might also want to lookup the STAR_TRANSFORMATION_ENABLED parm.

If you're switching logs every 12 seconds during loads, you can easily
extrapolate new switch times by multiplication.  If you double the size of
your redos, your switch times will approximately double.  Triple the size,
triple the time between switches, etc.  I say "approximately" because
switching every 12 seconds can cause some I/O to overlap during the
archiving process, which may be reduced with fewer log switches.  Then
again, your archiving will take longer with larger redos, so there may be no
improvement.  Also check to see if you are able to use features like direct
path loading and/or nologging (depending on how you get data in there) to
reduce the amount of redo generated.  You must understand the impact these
features have on things like locking and recoverability first!  See the
Oracle docs and take some Oracle DBA classes, too, for best results.  :)

Also, we're using 8.1.7.2.0 on HP/UX 11.0.  I'd suggest patching to at least
8.1.7.3 just for the number of bug fixes.  The final patch is 8.1.7.4, and I
can't imagine why NOT to go there, but I haven't really looked at it.  And
from what I've heard, 9i offers more performance enhancements for DWs, like
multiple block sizes in the same DB.

Good luck!  :)

Rich Jesse                           System/Database Administrator
Rich.Jesse@qtiworld.com              Quad/Tech International, Sussex, WI USA


> -----Original Message-----
> From: Norris,James L. [mailto:Larry.Norris@cnalife.com]
> Sent: Tuesday, July 02, 2002 9:54 AM
> To: Multiple recipients of list ODTUG-WAREHOUSE-L
> Subject: RE: Data Warehouse Initialization Parameters for Oracle and
> 
> 
> A sample initdw.ora is provided with Oracle 8i Enterprise 
> installs in the
> 'dbs' directory and attached here for your perusal:
> 
> 
> 
> -----Original Message-----
> Sent: Tuesday, July 02, 2002 8:38 AM
> To: Multiple recipients of list ODTUG-WAREHOUSE-L
> Configura
> 
> 
> Hi,
> 
> I'm looking for suggested init files for a large data warehouse.  I'm
> interested in knowing the parameters that are used and what 
> the recommended
> settings are, particularly for:
> 
> DB_BLOCK_SIZE
> DB_BUFFERS
> LOG_BUFFER
> SHARED_POOL
> SHARED_POOL_RESERVED
> MTS (when to use and when not to use)
> PARALLEL_EXECUTION
> MULTI_BLOCK_READ_COUNT
> and other parameters you feel pertinent to a data warehouse.
> 
> I'd like  to see others init.ora file that you are using for data
> warehousing if anyone is willing to share this.  Also, does 
> anyone have a
> default data warehouse init.ora file provided by Oracle for UNIX.
> 
> I'm looking for a suggested size of redo log files and why 
> it's switching
> every 12 seconds during loads.
> 
> Any advice on the configuration is appreciated.  The platform is HP-UX
> 11/Oracle 8.1.7.2
> 
> What is the experience of those who have used Oracle 
> Warehouse Builder and
> Informatica?  Do tools really help to 
> design a better performing data warehouse?
> 
> Thanks much.
> 
> Alisa Young
Thanks to everyone for making ODTUG 2002 a great success!  Plan now
for next year's conference: Loews Miami Beach, Florida, June 22-27, 2003.
-- 
Author: Jesse, Rich
  INET: Rich.Jesse@qtiworld.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ODTUG-WAREHOUSE-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).
[prev in list] [next in list] [prev in thread] [next in thread]