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
- Tuning the PGA_AGGREGATE_TARGET
- 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:
- SGA_MAX_SIZE
- PGA_AGGREGATE_TARGET
- DB_CACHE_SIZE
- SHARED_POOL_SIZE
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:
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 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:
- spfile<SID>.ora
- spfile.ora
- 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.
