#***********************************************************************
# Example INIT.ORA file for data-warehousing applications
#***********************************************************************
# This file is provided by Oracle Corporation to help you customize
# your RDBMS installation for your data warehousing application.
# The primary purpose of this file is to provide a starting point 
# for parameter settings in a data-warehouse. This file is designed
# for release 8.1. 
#
# 'Data warehousing' is an expansive term. In general, this file 
# assumes that a data warehouse is characterized by: 
#   - end-users execute only queries (rather than updates)
#   - end-user queries often examine large amounts of data
#   - data-loading and updating occurs only during batch operations, 
#      when the table, index, or partition is unavailable to
#      end-users' queries
#
# Some parameter settings are generic to any data-warehouse application.
# Other parameters depend upon the size of the data warehouse; different
# settings are provided for these parameters, for the following categories
# of data warehouses:
#      Category      Size of raw data      CPUs      Memory
#      SMALL         <50GB                 4         ~1GB
#      MEDIUM        50-200GB              4-12      2-4GB
#      LARGE         >200GB                12-16+    >4GB
# 'Raw data' refers to the size of the actual data, and does not
# include index space or temp space. 
# Further customization of these parameters will likely yield better 
# performance for your particular environment. 
#
# More detailed information on all parameters can be found in the 
# in the documentation. 
#
# INSTRUCTIONS: Edit this file and the other INIT files at 
# your site, either by using the values provided here or by providing
# your own.  If you are using Oracle Parallel Server,place an IFILE= line 
# into each instance-specific INIT file that points at this file.


#***********************************************************************
# Database parameters
#***********************************************************************


# Database blocks should be large in data warehouses. This improves 
# performance for operations involving large amounts of data.
db_block_size = 8192

# When building a new application, the compatibility should be set to the 
# current release to take advantage of all new features. 
compatible = 8.1.5

# Initially, set db_files to a reasonably high number.
#db_files = 1000


#***********************************************************************
# Memory parameters
#***********************************************************************


# Shared pool size should be, in general, equal to approximately 2%
# of the total physical memory. 
shared_pool_size = 20971520                              #SMALL (20M)
#shared_pool_size = 41943040                             #MEDIUM (40M)
#shared_pool_size = 83886080                             #LARGE (80M)

# The numbers of buffers should be approxiately equal to 2% of the
# total physical memory. The actual parameters value can be 
# calculated as: (2% of physical memory) / db_block_size
db_block_buffers = 2500                                  #SMALL
#db_block_buffers = 5000                                 #MEDIUM
#db_block_buffers = 10000                                #LARGE

# The following two parameters, hash_area_size and sort_area_size,
# depend upon the number of anticipated concurrent queries and the
# amount of available memory. Reasonable starting values are given
# below.  Each query will allocate its own memory for sorting and/or
# hashing, up to the maximum amount of memory specified below. Each
# parallel query will allocate up to 2 * (degree of parallelism) *
# area_size.  These values must be set so that there is enough memory 
# to accommodate the anticipated number of concurrent queries.

# Hash joins will perform better with larger hash_area_size. In a
# data warehouse that has a significant number of joins, up to 75%
# of the physical memory may be dedicated for hash joins, so these
# parameters can be increased. 
hash_area_size = 8388608                                 #SMALL (8M)
#hash_area_size = 15728640                               #MEDIUM (15M)
#hash_area_size = 26214400                               #LARGE (25M)

# For queries, sort_area_size should remain relatively small (even for
# large databases). Consider increasing the value of sort_area_size when
# building indexes.
sort_area_size = 1048576                                 #SMALL (1M)
#sort_area_size = 2097152                                #MEDIUM (2M)
#sort_area_size = 4194304                                #LARGE (4M)

# The default for create_bitmap_area_size is 8M. This default is
# adequate for cardinalities up to 1 million. 'Cardinality' is the
# number of distinct values in a given column (not the number of
# rows). For higher cardinalities, it may be necessary to increase this
# parameter.
#create_bitmap_area_size = 8388608

# Bitmap_merge_area_size is used for bitmap range queries and bitmap
# star joins. For larger databases that utilize bitmap indexes, this 
# value should be raised from the default (1M) to a value closer to
# the sort_area_size.
#bitmap_merge_area_size = 1048576


#***********************************************************************
# Parallel Query parameters
#***********************************************************************


# Parallel query parameters have been greatly simplified in release
# 8.1. These new parameters are recommended for both new data-warehouses
# as well as existing data-warehouses that are being upgraded to 8.1
parallel_automatic_tuning = true

# This parameter determines the default number of parallel query 
# processes. Typically, 2 parallel processes per CPU provides good
# performance. However, for systems with a smaller number of CPUs,
# more parallel processes may be desired. 
parallel_threads_per_cpu = 4                                 #SMALL
#parallel_threads_per_cpu = 2 or 4                           #MEDIUM
#parallel_threads_per_cpu = 2                                #LARGE

# Note that the older parallel-query parameters are still
# valid. Existing data warehouses can be upgraded to Oracle8i without
# changing any parameters related to parallel query.


#***********************************************************************
# Optimizer and query parameters
#***********************************************************************


# All data warehouses should use the cost-based optimizer. All advanced
# performance features, such as star-query support, hash joins, and
# bitmap indexes are only accessible via the cost-based optimizer.
optimizer_mode = choose 


# This parameter controls the use of new optimizations and performance
# techniques. When building a new application, this parameter should
# be set to the current release to take advantage of all new
# features. This is the default. When upgrading from an older release,
# it is recommended to set this parameter to the current release,
# although one could choose to set this to older releases for backwards
# compatibility.
#optimizer_features_enable = 8.1.5


# When using a star schema, set this parameter to true.
star_transformation_enabled = true

# The following parameters may provide better performance for certain
# environments. These should be set only after evaluating their
# performance impact.
# always_anti_join = hash
# always_semi_join = hash


#***********************************************************************
# IO parameters
#***********************************************************************


# Multiblock reads allow for the database to retrieve multiple
# database blocks in a single IO. In general, a high multiblock read
# count provides better performance. Oracle supports IO's up to 512k on
# many platforms, but IO size of 64k is generally appropriate. Disk
# striping will also affect the value for multiblock read count, since
# the stripe size should ideally be a multiple of the IO size.
db_file_multiblock_read_count = 8

# hash_multiblock_io_count and sort_multiblock_read_count default to
# db_file_multiblock_read_count. However, when setting the
# db_file_multiblock_read_count to a large value, these two
# parameters should be set to lower values.
#hash_multiblock_io_count = 8
#sort_multiblock_read_count = 8


#***********************************************************************
# Materialized view parameters
#***********************************************************************

# This parameter enables the use of materialized views for improved
# query performance. 
query_rewrite_enabled           = true 


#***********************************************************************
# Other Parameters
#***********************************************************************


# This section lists other parameters that, although not specific
# to data warehousing, are required for any Oracle database. 
#db_name=80 
#control_files=/vobs/oracle/dbs/t_cf1.f 

#db_name = MY_DB_NAME

# Define at least two control files by default
#control_files = (ora_control1, ora_control2)

