Ari,
What does the initdw.ora file look like? I heard that Oracle provides a
sample one that is specific to Data Warehouses but have not been able to
find it.
thanks,
-Kevin
---------------------------------------------------------------------------
Kevin,
Here it is. I got it from Oracle 8.1, so it may be slightly different for
previous versions. The 8.1 features are noted, and parameters like
shared_pool_size and db_block_size should be the same for Oracle8.0 or 8.1.
Enjoy!
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 280+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
#***********************************************************************
# 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, and this IO size 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 = 64
# 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 (as above), 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)
Back to Ari Kaplan's Home Page