Oracle Tips by Burleson Consulting
This chapter deals with the tuning of Oracle
internal structures. Oracle uses memory to speed processing by use of
the System Global Area, or Shared Global Area (depending on who you
ask), or, the SGA. The SGA consists of several cache areas and pool
areas, and each needs to be tuned to obtain optimal performance. An
additional complexity, the capability to have SGA areas with
different-sized db_block_buffers, makes SGA tuning even more fun.
Database internals tuning is a complex topic.
Just when you think you have Oracle internals figured out, Oracle
slips in some new features, takes old ones away, or, just for some
perverse form of fun, changes the structures of tried-and-true tables
and views. Actually it is all a secret plot between senior Oracle Remote DBAs
and Oracle to maintain job security.
This section will cover one of the more
challenging and critical aspects of the Remote DBA job: analyzing,
diagnosing, and fixing database internals performance problems. The
last chapter discussed application tuning. You will get a majority of
the performance gains in an application from proper database
configuration and application tuning. However, where you will be most
exposed will be in the area of internals tuning. Squeezing that last
bit of performance from the database seems to be the one area managers
like to focus on (they forgot the bit about application tuning and now
expect you to work miracles) when there are problems.
Steps to Internals Tuning
As was said at the end of the last chapter,
once the application has been tuned, the Remote DBA's job really begins. Now
you can begin tuning the Oracle system itself to take advantage of the
tuned application. This step of the tuning process is typically a
1. Review and set all initialization
parameters for your application and operating system.
2. Tune memory allocation.
3. Eliminate I/O bottlenecks.
4. Tune resource contention.
5. Tune sorts, freelists, and checkpoints.
The first step involves reading the operating
system-specific release manual and database readme files for any new,
changed, or improved initialization parameters. Using your knowledge
of the number of users, size of the system memory, number and
configuration of disks, sizing of tables, and other system and
application parameters, you must do your best to set all of the
initialization parameters that will help your system perform better.
Parameters can be reviewed by looking at initialization file or by
querying V$PARAMETER file. (Note: The Wiley Web site includes a script
to report on changed initialization parameters (param9.sql).) You
should also perform a control file dump using the command:
DATABASE BACKUP CONTROLFILE TO TRACE;
The second step requires an up-and-operating
database against which you run various performance-monitoring scripts
and tools; then you readjust the initialization parameters. You should
also examine database and session level waits using queries against
V$WAITSTAT and V$SESSION_WAIT dynamic performance views.
The third step requires monitoring your disk
assets and their performance. Your system administrator will be
critical to assuring the success of this step. Hopefully, if you were
able to have a hand in designing the system layout, you won’t have
much I/O-related tuning. An inherited database (especially those from
aftermarket products) usually requires extensive file movements and
optimizations, so this step could actually give the most performance
In one system I inherited, a well-meaning Remote DBA
had rebuilt the application indexes, by disabling and then re-enabling
the primary keys, without specifying the location for the indexes. Of
course, you will remember what this causes: all of the indexes were in
the same tablespace as the data tables. Simply moving the indexes to
their (empty) tablespace resulted in an over 300 percent performance
gain (one 30-minute query dropped to less than a minute). I was an
instant hero. What this anecdote should tell you is to carefully
examine any inherited database for badly placed indexes, tablespaces,
rollback segments, and redo logs. Just putting everything where it
should be can provide dramatic improvements in performance for badly
Step 4 involves more monitoring with tools or
scripts. Contention for system resources (latches, rollbacks, logs,
memory, etc.) can be a real performance drain. Always review the alert
log for all databases you inherit, as they will tell you if there are
some forms of contention such as for redo logs. The scripts that
follow will help determine if there are other types of contention.
Step 5 will involve monitoring system
statistics on a running application. There are numerous tools, as well
as the scripts included in this section, that will tell you if you
have problems with sorts, freelists, and checkpoints. Tuning sorts is
especially important in DSS and reporting databases. In one case, a
10-minute sort dropped to less than a minute by bumping up the
SORT_AREA_SIZE parameter 2 to 3 megabytes, thus preventing disk sorts.
Each of the following sections addresses a
specific area, and includes reports and scripts to help you monitor
Code Depot for Full Scripts
||This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.