Oracle Training Oracle Support Development Oracle Apps

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

   

Oracle Business Intelligence, OLAP and BI training and consulting tips . . .

Click here for more
Oracle News Headlines

 

 

 

 

 


 

Differences Between 9iR2 DBCA Database Templates

When you install an Oracle database, or create a new database using the Database Configuration Assistant, you're given the choice of four templates to use when creating the database;

  • Data Warehouse
  • General Purpose
  • New Database
  • Transaction Processing

So what are the differences between the three templates?

'New Database' presents a series of default values, allowing you to change these and add/remove database options as required. The other options create  the database using a set of predefined values, and you can use the online help provided with DBCA to view the different values used. To help compare the values used in each template, I've pulled together the individual help pages and put them on one page, together with notes on what each option does differently. You can view the MS Excel worksheet here;

  • Differences Between the Default, Data Warehouse, General Purpose and Transaction Processing DBCA Templates.

With the General Purpose template, on a Windows 2000 server platform, the init.ora settings used in database creation are as follows;

  • aq_tm_processes = 1
  • background_dump_dest {ORACLE_BASE}\admin\{DB_NAME}\bdump
  • compatible = 9.2.0.0.0
  • control_files = ("{ORACLE_BASE}\oradata\{DB_NAME}\CONTROL01.CTL", "{ORACLE_BASE}\oradata\{DB_NAME}\CONTROL02.CTL", "{ORACLE_BASE}\oradata\{DB_NAME}\CONTROL03.CTL")
  • core_dump_dest = {ORACLE_BASE}\admin\{DB_NAME}\cdump
  • db_block_size = 8192
  • db_cache_size = 25165824
  • db_file_multiblock_read_count = 16
  • dispatchers =  (PROTOCOL=TCP) (SERVICE={SID}XDB)
  • fast_start_mttr_target = 300
  • hash_join_enabled = TRUE
  • java_pool_size = 33554432
  • job_queue_processes = 10
  • large_pool_size = 8388608
  • open_cursors = 300
  • pga_aggregate_target = 25165824
  • processes = 150
  • query_rewrite_enabled = FALSE
  • remote_login_passwordfile = EXCLUSIVE
  • shared_pool_size = 50331648
  • sort_area_size = 524288
  • star_transformation_enabled = FALSE
  • timed_statistics = TRUE
  • undo_management = AUTO
  • undo_retention = 10800
  • undo_tablespace = UNDOTBS1
  • user_dump_dest = {ORACLE_BASE}\admin\{DB_NAME}\udump

and the database options installed are;

  • Example Schemas
  • Oracle Data Mining
  • Oracle Intermedia
  • Oracle JVM
  • Oracle Label Security
  • Oracle OLAP
  • Oracle Spatial
  • Oracle Text
  • Oracle Ultra Search
  • Oracle XML DB

Taking the 'General Purpose' template as a baseline, the changes made by the Data Warehousing template are as follows;

  1. DB_CACHE_SIZE reduced to 16777216.
  2. DB_FILE_MULTIBLOCK_COUNT set to 32.
  3. HASH_AREA_SIZE incread to 1048576.
  4. PGA aggregate target increased to 33554432
  5. QUERY REWRITE ENABLED set to TRUE
  6. STAR TRANSFORMATION ENABLED set to TRUE

Whereas with the Transaction Processing (OLTP) tempate, the following changes are made;

  1. The Oracle OLAP option is not installed
  2. HASH_JOIN_ENABLED has been set to false, and the HASH_AREA_SIZE parameter has not been set.
  3. DB_BLOCK_SIZE reduced to 4k
  4. DB_FILE_MULTIBLOCK_COUNT (number of blocks returned each fetch) reduced to 8
  5. DB Cache size increased to 33554432.
  6. PGA Aggregate target reduced to 16777216
  7. CWMLITE schema not installed due to OLAP Option not being installed.

One thing that immediately struck me about the OLTP vs Data Warehousing template is the different block sizes (8k vs. 4k). In a nutshell, OTLP systems generally make lots of 'random' small requests for data, and a smaller block size reduces the incidence of I/O contention. Data Warehouses, in contrast, often experience lots of full table scans, and a bigger block size means that less blocks have to be retrieved to return all the table's data. For more details and background on this, check out this article.


 

 

   
��  
 
 
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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational