1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238
|
#***********************************************************************
# Database parameters
#***********************************************************************
# Database blocks should be large in data warehouses. This improves
# performance for operations involving large amounts of data.
db_block_size = 8192
# For a large data warehouse, db_files should be set to a large value.
#db_files = 1000
#***********************************************************************
# Memory parameters
#***********************************************************************
# In a data warehouse, the majority of physical memory will be
# allocated for the one of the following two purposes:
# Runtime memory: used for sorting and hashing data during query processing
# (governed by the parameter pga_aggregate_size)
# Data caching: used to accelerate performance by avoid disk accesses
# (governed by the parameter db_cache_size)
# Additionally, a significant amount of memory may need to be allocated for:
# Shared pool: used for storing shared memory constructs
# (governed by the parameter shared_pool_size)
# Large pool: used during parallel-execution processing
# (governed by the parameter large_pool_size)
#
# Memory is managed globally. The DBA should first determine how much
# memory is available for Oracle to use. Then, the DBA should choose
# memory parameters so that pga_aggregate_size + db_cache_size +
# shared_pool_size + large_pool_size is roughly equal to the amount
# of memory available for the Oracle database.
#
# For example, suppose that a DBA is managing a small data mart. The
# data mart server has 1GB of physical memory. The DBA has determined
# that 500M of memory will be used by the operating system and other
# applications, so that 500M is available for Oracle.
#
# The DBA may choose the following settings:
# shared_pool_size = 50M
# pga_aggregate_size = 200M
# db_cache_size = 200M
# large_pool_size = <default>
#
# The total memory utilization is 450M plus a system-determined value
# for the large pool.
#
# The following sections discuss each of these memory-related
# parameters in more detail. These examples assume that the data
# warehouse server has 1GB, 8GB, and 16GB respectively for small,
# medium, and large configurations.
# Runtime memory (the memory used for sorting and hashing during query
# execution) is automatically and globally managed when the
# pga_aggregate_target parameter is set. For data warehouse workloads
# which involve sorts and joins of large volumes of data, the
# pga_aggregate_target should be set to a large value.
#
# pga_aggregate_target should, in general, be equal to 20-80% of the
# available memory, depending on the workload. The values below assume
# a mixed data-warehouse workload.
#
# This parameters (introduced in Oracle9i) replaces all of the
# following parameters: hash_area_size, sort_area_size,
# create_bitmap_area_size, and bitmap_merge_area_size
pga_aggregate_target = 30M #DEMO
#pga_aggregate_target = 200M #SMALL
#pga_aggregate_target = 3000M #MEDIUM
#pga_aggregate_target = 6000M #LARGE
# The database cache is also a globally-managed portion of memory. The
# database cache should be set to a large value for data warehouse
# workloads which involves short-running queries and/or the access of
# small tables and indexes.
#
# db_cache_size should, in general, be equal to 20-80% of the
# available memory, depending on the workload. The values below assume
# a mixed data-warehouse workload.
#
db_cache_size = 30M #DEMO
#db_cache_size = 200M #SMALL
#db_cache_size = 3000M #MEDIUM
#db_cache_size = 6000M #LARGE
# Shared pool size should be, in general, equal to 5-10% of the
# available memory. Data warehouses typically do not require as much
# memory for shared pool as OLTP systems.
shared_pool_size = 20M #DEMO
#shared_pool_size = 50M #SMALL
#shared_pool_size = 400M #MEDIUM
#shared_pool_size = 800M #LARGE
# The default for large_pool_size should appropriate for most
# environments.
#
# The Large Pool is used for several purposes. In a data warehouse the
# majority of the space in the Large Pool will be used for
# parallel-execution internal message buffers. The amount of memory
# required by parallel-execution is proportional to the product of the
# number of concurrent parallel-execution users and the square of the
# number of CPU's.
#
# The documentation describes in detail how to estimate the default size
# of the Large Pool, and the conditions under which this parameter
# should be set explicitly.
#
# Here are some very general estimates on the amount of memory required
# for the Large Pool based on the number of CPU's:
# 4 cpus: 5M (with parallel_threads_per_cpu = 4)
# 8 cpus: 5M (with parallel_threads_per_cpu = 2)
# 8 cpus: 20M (with parallel_threads_per_cpu = 4)
# 16 cpus: 20M (with parallel_threads_per_cpu = 2)
# 32 cpus: 80M (with parallel_threads_per_cpu = 2)
#
# The Large Pool is only used for parallel-execution message buffers
# when parallel_automatic_tuning is enabled. If
# parallel_automatic_tuning is not utilitized, then parallel-execution
# message buffers are stored in the shared pool, and the
# shared_pool_size parameter should be adjusted appropriately.
#***********************************************************************
# Parallel Execution parameters
#***********************************************************************
# Parallel execution parameters were greatly simplified in Oracle8i.
# Data warehouses developed on older releases of Oracle may use
# different init.ora parameters. While these older parameters continue
# to be supported, these parameters below are recommended for all new
# data warehouses, and should be considered when upgrading data
# warehouses from previous releases.
# Setting parallel_automatic_tuning will result in the database
# configuring itself to support parallel execution.
parallel_automatic_tuning = true
# This parameter determines the default number of parallel execution
# processes. Typically, 2 parallel processes per CPU provides good
# performance. However, for systems with a smaller number of CPUs or
# for systems in which the IO subsystem is slow relative to the the
# CPU's, more parallel processes may be desired and the value of this
# parameter may be increased.
parallel_threads_per_cpu = 4 #SMALL
#parallel_threads_per_cpu = 2 or 4 #MEDIUM
#parallel_threads_per_cpu = 2 #LARGE
#***********************************************************************
# Optimizer and query parameters
#***********************************************************************
# All data warehouses should use the cost-based optimizer. All basic
# data warehouse performance features, such as star-query support,
# hash joins, parallel execution, and bitmap indexes are only
# accessible via the cost-based optimizer.
optimizer_mode = choose
# When using a star schema, set this parameter to true.
star_transformation_enabled = true
#***********************************************************************
# 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, particularly for operations on
# large volumes of data. Oracle supports IO's up to 1MB on many
# platforms. Disk striping will also affect the value for multiblock
# read count, since the stripe size should ideally be a multiple of
# the IO size.
# If you are gathering optimizer system statistics (see DBMSSTAT.SQL
# for more information), then you should set this parameter to a high
# value.
#db_file_multiblock_read_count = 64
# If you are not gathering optimizer system statistics, then you
# should set this parameter to a lower value.
db_file_multiblock_read_count = 16
#***********************************************************************
# Materialized view parameters
#***********************************************************************
# This parameter enables the use of materialized views for improved
# query performance.
query_rewrite_enabled = true
# This parameter determines the degree to which Oralce enforces
# integrity rules during query rewrite. In most data-warehouse
# environment, 'trusted' is the appropriate setting.
query_rewrite_integrity = trusted
#***********************************************************************
# Compatibility
#***********************************************************************
# When building a new application, both compatibility and
# optimizer_features_enabled should be set to the current release to
# take advantage of all new features. If you are upgrading an existing
# application to Oracle9i, then you may want to consider setting one
# or both of these parameters to an earlier release.
#compatible = 9.0
#optimizer_features_enabled = 9.0
#***********************************************************************
# Other Parameters
#***********************************************************************
# This section lists other parameters that, although not specific
# to data warehousing, are required for any Oracle database. By
# uncommenting these parameters, this parameter file can be used
# as a complete stand-alone init.ora file.
db_name = HRMS89
# Define at least two control files by defaultms
control_files = (c:\oracle\oradata\HRMS89\cntrl1HRMS89.ctl, c:\oracle\oradata\HRMS89\cntrl2HRMS89.ctl)
undo_management=AUTO |
Partager