From
the bestselling Oracle10g
book, Oracle Database
10g New Features by Mike Ault, Madhu Tumma, and
Daniel Liu, published by Rampant TechPress and can be
purchased directly from Rampant TechPress for 10%-off by
clicking
here.
by
Mike Ault
The
following discusses the initialization parameters in Oracle
10g.
-
Simplified Initialization Parameters - Initialization parameters are now divided into two groups,
basic and advanced. In the vast majority of cases, it is
necessary to set and tune only the basic parameters, of
which there are 20 to 30, to get reasonable performance from
the database. In rare situations, modification of the
advanced parameters may be needed to achieve optimal
performance.
-
Old
Initialization Parameters - Oracle 10g has deprecated 25 old parameters.
-
New
Initialization Parameters - Oracle 10g has introduced more than 20 new
parameters.
Basic
Parameters
In
previous release of Oracle, all parameters were considered
equally important. This made the administration and tuning
of the database very difficult because database
administrators need to become familiar with over 200
parameters. Oracle 10g
introduces two classes of parameter: basic and advanced. In
most cases, you need only set up the basic parameters for an
Oracle 10g
instance.
These basic parameters include:
-
cluster_database
-
compatible
-
control_files
-
db_block_size
-
db_create_file_dest
-
db_create_online_log_dest_n
-
db_domain
-
db_name
-
db_recovery_file_dest
-
db_recovery_file_dest_size
-
instance_number
-
job_queue_processes
-
log_archive_dest_n
-
log_archive_dest_state_n
-
nls_language
-
nls_territory
-
open_cursors
-
pga_aggregate_target
-
processes
-
remote_listener
-
remote_login_passwordfile
-
rollback_segments
-
sessions
-
sga_target
-
shared_servers
-
star_transformation_enabled
-
undo_management
-
undo_tablespace
The
following is an example of the parameter file generated by
DBCR utility (The basic parameters are in bold):
###########################################
# Archive
###########################################
log_archive_dest_1='LOCATION=/u03/arch/grid'
log_archive_format=%t_%s_%r.dbf
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=25165824
db_file_multiblock_read_count=16
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Database Identification
###########################################
db_domain=world
db_name=grid
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/u01/app/oracle/admin/grid/bdump
core_dump_dest=/u01/app/oracle/admin/grid/cdump
user_dump_dest==/u01/app/oracle/admin/grid/udump
timed_statistics=TRUE
###########################################
# File Configuration
###########################################
control_files=("/u02/ctl/grid/control01.ctl",
"/u02/ctl/grid/control02.ctl")
###########################################
# Job Queues
###########################################
job_queue_processes=10
###########################################
# Compatibility
###########################################
compatible=10.1.0.0.0
###########################################
# Optimizer
###########################################
query_rewrite_enabled=FALSE
star_transformation_enabled=FALSE
###########################################
# Pools
###########################################
java_pool_size=104857600
large_pool_size=8388608
shared_pool_size=104857600
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300
###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=25165824
sort_area_size=524288
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1
In
Oracle9i (9.2.0.3), there are 258 parameters in the
v$parameter view. In Oracle 10g (10.1.0.0), there are
more than 250 parameters in the v$parameter view. There are
233 parameters in both Oracle9i Release 2 and Oracle 10g
Release 1.
Old
Parameters
Twenty-five of the 258 parameters no longer exist in Oracle
10g's v$parameter view; these are:
New
Parameters
There are twenty more new parameters in Oracle 10g
Release 1:
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
