 |
|
Oracle Tips by Burleson |
Memory Tuning
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 DBAs and Oracle to maintain job security.
This section will cover one of the more
challenging and critical aspects of the 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 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 five-part process:
-
Review and set all initialization
parameters for your application and operating system.
-
Tune memory allocation.
-
Eliminate I/O bottlenecks.
-
Tune resource contention.
-
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:
ALTER 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 gains.
In one system I inherited, a well-meaning
DBA had rebuilt the application indexes, by disabling and then
reenabling 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 laid-out systems.
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
your database.
See Code Depot
www.oracle-script.com |
