Friday, August 24, 2012

Oracle Initialization Parameters Best Practices

Best Practice
Reasoning
Oracle recommends that you use a binary server
parameter file (spfile)
Use whichever type of initialization
parameter file you’re comfortable with. If
you have a requirement to use an spfile,
then by all means implement one.
don’t set initialization parameters if you’re
not sure of their intended purpose. When in doubt, use
the default
Setting initialization parameters can have
far-reaching consequences in terms of
database performance. Only modify
parameters if you know what the resulting
behavior will be
For 11g, set the memory_target and memory_max_target
initialization parameters
Doing this allows Oracle to manage all
memory components for you.
For 10g, set the sga_target and sga_target_max
initialization parameters.
Doing this lets Oracle manage most memory
components for you
For 10g, set pga_aggregate_target and
workarea_size_policy
Doing this allows Oracle to manage the
memory used for the sort space
Starting with 10g, use the automatic UNDO feature. This is
set using the undo_management and undo_tablespace
parameters
Doing this allows Oracle to manage most
features of the UNDO tablespace
Set open_cursors to a higher value than the default. 
typically set it to 500. Active online transaction
processing (OLTP) databases may need a much higher
value
The default value of 50 is almost never
enough. Even a small one-user application
can exceed the default value of 50 open
cursors.
Use at least two control files, preferably in different locations using different disks
If one control file becomes corrupt, it’s
always a good idea to have at least one other
control file available

No comments:

Post a Comment