Tuning the Oracle database with initialization parameters

This chapter excerpt contains tips designed to achieve the greatest performance gain in your Oracle database with the least effort by focusing on the initialization parameters that yield the biggest impact.

This is an excerpt from Chapter 4 of Oracle Database 10g Performance Tuning Tips & Techniques by Rich Niemiec,...

copyright 2007 from Oracle Press, a division of McGraw-Hill. Click here to download the full chapter.

The init.ora file (and spfile) determines many Oracle operating system environment attributes, such as memory allocated for data, memory allocated for statements, resources allocated for I/O, and other crucial performance-related parameters. Each version of Oracle continues to add to the total number of initialization parameters. In Oracle 10g Release 2 there are now 1381 (257 documented and 1124 hidden) different initialization parameters (these numbers vary slightly on different versions of Oracle and platforms). As you might expect, an entire book could be written on how to set and tune each parameter; this book 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. Setting four key initialization parameters (SGA_MAX_SIZE, PGA_AGGREGATE_TARGET, DB_CACHE_SIZE, and SHARED_POOL_SIZE) can be the difference between sub-second queries and queries that take several minutes. There is also a new SGA_TARGET parameter that can replace some of the key parameter that can be set as well that is covered in this chapter. This chapter will focus on the crucial initialization parameters but also list the top 25 initialization parameters near the end of the chapter. The chapter concludes with a look at typical server configurations for various database sizes.

This chapter contains the following tips and techniques designed to achieve the greatest performance gain with the least effort by focusing on the parameters that yield the biggest impact:

  • Crucial initialization parameters in Oracle
  • Modifying the initialization parameter file without a restart
  • Viewing the initialization parameters via Enterprise Manager
  • Tuning DB_CACHE_SIZE and monitoring hit ratios
  • Tuning the SHARED_POOL_SIZE
  • Checking library cache and dictionary cache
  • Querying the X$KSMSP table to get another picture of SHARED_POOL_SIZE
  • Using multiple buffer pools
  • User, session, and system memory use
  • Cost- vs. rule-based optimization
  • The top 25 performance-related initialization parameters to consider
  • Undocumented initialization parameters (more in Appendix A)
  • Typical server setups with different size databases

Identifying crucial initialization parameters

While tuning specific queries alone can lead to performance gains, the system will still be slow if the parameters for the initialization file are not set correctly because the initialization file plays such an integral role in the overall performance of an Oracle database. While you can spend time setting all the initialization parameters, there are just four main parameters that need to be set correctly to realize significant performance gains:


TIP: The key initialization parameters in Oracle are SGA_MAX_SIZE, PGA_AGGREGATE_TARGET, DB_CACHE_SIZE, and SHARED_POOL_SIZE.

There is also a new parameter, SGA_TARGET, which can be set so that Oracle manages the shared memory on your system (Automatic Shared Memory Management); Metalink Note 295626.1 describes this in detail. While this is a new parameter, the Oracle Application Development team recommends this for 10g (I included these recommendations at the end of this chapter). I would like to see this mature a bit more before I hand the "keys to the car" to Oracle, but I like the approach to simplicity, especially for beginners. The following query can be used to find the current settings of the key initialization parameters on your database (if SGA_TARGET is set to a non-zero value, then some of these parameters will be set to zero):

Col name for a25
Col value for a50

select   name, value
from     v$parameter
where    name in ('sga_max_size', 'pga_aggregate_target',
                   'db_cache_size', 'shared_pool_size');

NAME                  VALUE
--------------------- --------------------
shared_pool_size      50331648
sga_max_size          135338868
db_cache_size         25165824
pga_aggregate_target  25165824

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:


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 Oracle 10g 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<SID>.ora
  2. spfile.ora
  3. init<SID>.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.

TIP: If you can't figure out why your system isn't using the value in your init.ora file, you probably have an spfile overriding it. And, don't forget, you can also use a hint to override parameters at the query level in 10gR2.

Click here to read the rest of this chapter.

About the author

Richard J. Niemiec is CEO of TUSC, an Oracle Expert-Level consulting firm. He is the former president of the International Oracle Users Group and the president of the Midwest Oracle Users Group.

Dig Deeper on Oracle database performance problems and tuning