Tuning initialization parameters

The init.ora file in oracle database determines Oracle operating system environment attributes, such as memory allocated for statements, memory allocated for data,and other crucial performance-related parameters. In Oracle 10g there are now 1381 different initialization parameters .We will focuses on the key parameters that affect database performance. The key to an optimized Oracle database is often the architecture of the system and the parameters that set the environment for the database.

The key initialization parameters in Oracle are

DB_CACHE_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.

see How to Tune DB_CACHE_SIZE

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.Before you increase the size of your SGA, you must understand the effects on the physical memory of your system.

see more about  SGA_TARGET

PGA_AGGREGATE_TARGET

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

see  How to TUNE  PGA_AGGREGATE_TARGET

SHARED_POOL_SIZE

The shared_pool_size parameter governs the RAM size of the shared pool, including the library cache, used to hold SQL executable code and starting in Oracle9i shared_pool_size can be modified with “alter system” commands.

In general, if the library cache miss ratio is greater than one, the DBA should consider adding to the shared_pool_size.  You can pin package objects into the shared_pool_size region with the dbms_shared_pool.keep procedure.

The following Table includes the important parameters to set with performance implications:

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.
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.

Changing the Initialization Parameters Without a Restart

With each version of Oracle, more and more parameters can be altered without needing to
restart the database. This has greatly reduced the need for scheduled downtime to implement
system tuning changes. The next example shows changing the SHARED_POOL_SIZE to 128M
while the database is running:
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE = 128M;

In addition to being able to dynamically change parameters, Oracle 10g provides for the use of a SPFILE to persistently store dynamic changes to the instance parameters. Prior to Oracle 9i,any dynamic changes were lost when the database was restarted unless the parameters were added to the initialization parameter file manually. As of Oracle 9i and continuing into Oracle10g Release 2, dynamic changes can be stored in a server parameter file (spfile).

The default order of precedence when an instance is started is to read parameter files in the following order:
1. spfile.ora
2. spfile.ora
3. init.ora
Parameters can be dynamically modified at a system-wide or session-specific scope. In addition, parameters can be changed in memory only or persist across restarts via an SPFILE.

Written by admin on December 6th, 2010 with comments disabled.
Read more articles on Oracle Tuning.

Related articles

Comments disabled

Comments on this article have been disabled.