For Oracle7, version 7.3, there are 154 initialization
parameters, for Oracle8, version 8.0.5, there are 184. In Oracle8i
there are 194. In Oracle9i version 9.0.1 there are 251 and in 9iR2,
257. In 10g the number of parameters actually dropped to 254 but the
number of undocumented parameters increased. In 9iR2 the number of
undocumented parameters was 583 up to 918 in 10gR1.
Fortunately there are very few that you need to adjust
to tune Oracle. Table 2 lists the major tuning parameters, but is not
supposed to be a complete list by any means.
|
Parameter |
Definition |
|
create_bitmap_area_size |
This sets the memory area
for bitmap creation |
|
bitmap_merge_area_size |
This
is the memory area used for bitmap merge |
|
create_stored_outlines |
This allows Oracle to
create stored outlines |
|
cursor_sharing |
This
sets for automated literal replacement |
|
db_file_multiblock_read_count |
This
sets the read size for full table and index scans |
|
filesystemio_options |
This
is used to set direct or AIO options for filesystem reads |
|
optimizer_index_caching |
Used to tune index access |
|
optimizer_index_cost_adj |
Used
to tune index access |
|
query_rewrite_enabled |
Sets for queries to be
rewritten to use materialized views or FBIs |
|
query_rewrite_integrity |
Sets
the criteria for when MVs are used. |
|
session_cached_cursors |
Sets
the number of cached cursors at the session level |
|
sga_max_size |
Sets the maximum SGA memory
size |
|
sga_target |
Sets
the baseline SGA memory size |
|
star_transformation_enabled |
Allows
Oracle to use star transformation |
|
transactions_per_rollback_segment |
Sets
the number of transactions that will use a single rollback (undo)
segment |
|
pga_aggregate_target |
Sets
the total PGA memory usage limit |
|
workarea_size_policy |
Determines how workareas (sort and hash) are determined |
|
buffer_pool_keep |
Sets the size of the keep
buffer pool for tables and indexes |
|
buffer_pool_recycle |
Sets
the size of the recycle buffer pool for tables and indexes |
|
cursor_space_for_time |
Sacrifices memory for
cursor storage space |
|
db_16k_cache_size |
Sets the size of the 16K
cache size |
|
db_2k_cache_size
|
Sets the size of the 2K
cache size |
|
db_32k_cache_size
|
Sets the size of the 32K
cache size |
|
db_4k_cache_size
|
Sets the size of the 4K
cache size |
|
db_8k_cache_size
|
Sets the size of the 8K
cache size |
|
db_block_size |
Sets the default block size
for the database |
|
db_cache_size |
Sets
the default cache size |
How to determine proper setpoints for all of these is
beyond the scope of this paper. However the Oracle tuning guides
provide many good tips as does the Burleson Consulting web site:
www.remote-Remote DBA.net.
