Configuring Undo Space

Configuring Undo Space
======================
Oracle needs undo space to keep information for read consistency, for recovery, and
for actual rollback statements. This information is kept in one or more undo
tablespaces.

Oracle provides automatic undo management, which completely automates the
management of undo data. A database running in automatic undo management mode
transparently creates and manages undo segments. Oracle Corporation strongly
recommends using automatic undo management, because it significantly simplifies
database management and removes the need for any manual tuning of undo (rollback)
segments. Manual undo management using rollback segments is supported for
backward compatibility reasons.

Adding the UNDO TABLESPACE clause in the CREATE DATABASE statement sets up the
undo tablespace. Set the UNDO_MANAGEMENT initialization parameter to AUTO to
operate your database in automatic undo management mode.

The V$UNDOSTAT view contains statistics for monitoring and tuning undo space.
Using this view, you can better estimate the amount of undo space required for the
current workload. Oracle also uses this information to help tune undo usage in the
system. The V$ROLLSTAT view contains information about the behavior of the undo
segments in the undo tablespace.

Important Initialization Parameters With Performance Impact in 10g

COMPATIBLE :=
Specifies the release with which the Oracle server must maintain compatibility. It lets you take advantage of the maintenance improvements of a new release immediately in your production systems without testing the new functionality
in your environment. If your application was designed for a specific release of Oracle, and you are actually installing a later release, then you might want to set this parameter to the version of the previous release.

DB_BLOCK_SIZE :=
Sets the size of the Oracle database blocks stored in the
database files and cached in the SGA. The range of values depends on the operating system, but it is typically 8192 for transaction processing systems and higher values for database warehouse systems.

SGA_TARGET :=
Specifies the total size of all SGA components. If SGA_TARGET
is specified, then the buffer cache (DB_CACHE_SIZE), Java pool (JAVA_POOL_SIZE), large pool (LARGE_POOL_SIZE), and shared pool (SHARED_POOL_SIZE) memory pools are
automatically sized.

PGA_AGGREGATE_TARGET :=
Specifies the target aggregate PGA memory available to all
server processes attached to the instance.

PROCESSES :=
Sets the maximum number of processes that can be started by
that instance. This is the most important primary parameter to set, because many other parameter values are deduced from this.

SESSIONS :=
This is set by default from the value of processes. However, if
you are using the shared server, then the deduced value is likely to be insufficient.

UNDO_MANAGEMENT:=
Specifies which undo space management mode the system
should use. AUTO mode is recommended.

UNDO_TABLESPACE :=
Specifies the undo tablespace to be used when an instance
starts up.

Important Initialization Parameters Without Performance Impact in 10g

Necessary Initialization Parameters Without Performance Impact
==============================================================

DB_NAME := Name of the database. This should match the ORACLE_SID environment
variable.
DB_DOMAIN := Location of the database in Internet dot notation.

OPEN_CURSORS := Limit on the maximum number of cursors (active SQL
statements) for each session. The setting is
application-dependent; 500 is recommended.

CONTROL_FILES := Set to contain at least two files on different disk drives to
prevent failures from control file loss.
DB_FILES := Set to the maximum number of files that can assigned to the
database.