Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Oracle data warehouse design tips

Oracle Database Tips by Donald Burleson

I am designing an Oracle Data warehouse, and the Data warehouse will be created from an existing OLTP system.  Three questions:

Question 1: My existing OLTP system has 3 heavily summarized tables from which the existing query analysis reports are generated.  Can these tables by itself be called a Data warehouse, or should each of these 3 tables be broken up into subject-oriented fact & dimension tables like a star schema for implementing an Oracle data warehouse?

Answer 1: I have several hundred pages devoted to Oracle data warehouse design optimization in my book "Oracle Tuning: The Definitive Reference", but here is a quick summary.  A traditional data warehouse is all-about providing a vehicle for reporting from summary and aggregate information (using de-normalized tables, summarized tables and materialized views).  Most data warehouse designers replicate the data warehouse summary data onto another instance to avoid contention with the OLTP database, but this depends on the traffic on your system and the ability of your server to handle additional load (i.e. SMP processor capability).

There is no reason that you cannot keep the data warehouse within the same instance as the OLTP systems, but you need to consider several data warehouse design factors:

1 - Data Warehouse Query Performance - A data warehouse pre-summarizes and pre-aggregates the OLTP data so that the queries can fetch the result sets with only a few data block touches.  Make sure that your OLTP server has enough CPU resources to support Oracle parallel query, as you will need it to roll-up your summaries and aggregates:  See Oracle multiple CPU's and parallel query OPQ.

2 - Data warehouse schema design - If your existing summary tables do not require joins into other OLTP tables, then you will not benefit from a star transformation approach.  See my notes on Oracle star schema queries for details.


Question 2: I am aware that there are data warehousing tools available from Oracle like Oracle Warehouse Builder (OWB) for instance.  Instead of buying a new tool, what if i choose the "Data Warehouse" template name in the Step 2 of 8 of oracle Database creation assistant (DBCA)? Will it not generate a DW for me? how to use/explore this option?

Answer 2:  I would not use the DBCA to create a data warehouse instance.  Remember, the DBCA is a crutch for beginners. Once you become comfortable with Oracle, you should experiment with the custom database creation option, and eventually move-on to manual database creation. 

The data warehouse template in DBCA will simply create a one-size-fits-all starting point for a new data warehouse instance, an instance that is configured for large hash joins, large sorts and batch-related processing that is typical of a data warehouse.

For details on using DBCA, see the great book "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master). 

Here are the initialization parameters that are set in the DBCA for a data warehouse.  As we see, these are not correct for everyone, and you need to customize all data warehouse instances to match your specific processing needs.  For details, see my book "Oracle Tuning: The Definitive Reference":

Name Value
aq_tm_processes 1
db_block_size 8,192
db_cache_size 16m
db_file_multiblock_read_count 32
fast_start_mttr_target 300
hash_area_size 1m
hash_join_enabled TRUE
java_pool_size 33m
job_queue_processes 10
large_pool_size 8m
open_cursors 300
pga_aggregate_target 33m
processes 150
query_rewrite_enabled TRUE
remote_login_passwordfile EXCLUSIVE
shared_pool_size 50m
sort_area_size 1m
star_transformation_enabled TRUE
timed_statistics TRUE
undo_management AUTO
undo_retention 10,800

Question 3:  We have been using the existing OLTP system by creating a database using the "New Database" option...what if we had opted for "Transaction Processing"...would that have made any significant difference?

Answer 3:  No, not really.  See Oracle DBCA templates for database creation to see the differences in parameter values.

Remember, these are only starting points, and you will want to optimize all parameters, based on your workload.

Also see my data warehouse notes in "Oracle Tuning: The Definitive Reference", plus these online supplements:

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.