Oracle Tools for Tuning Memory
Oracle Tips by Burleson Consulting
Memory contention will cause the best-tuned
application to perform poorly. If the application constantly has to go
to disk to get data dictionary and actual data, then performance will
suffer. Remember, the SGA is divided into three major areas: the
shared pool, the redo log buffer, and the database buffers. Under
ORACLE8, Oracle8i, and Oracle9i, there are these additional areas in
the SGA: the large pool (Oracle8), for databases using the
multithreaded server, parallel query and rman, and the Java Pool
(Oracle8i and Oracle9i) for all databases.
Tuning the Shared Pool
Missing a get on the data dictionary or shared
pool area of the SGA is more costly than missing a get on a data
buffer or waiting for a redo buffer. Therefore, here we will look at
an SQL script that allows the Remote DBA to examine the current status of the
data dictionary or shared pool area. This SQL script is shown in
Notice the following in Source 13.1: First,
the script only selects statistics that have been used more than 100
times and where getmisses occurred. Obviously, if the parameter has
had no getmisses, it should be satisfactory. (The factor of 100 gets
was selected to ensure that the parameter has had enough activity to
generate valid statistics.) Also notice that the percentage of misses
is automatically calculated and reported for each parameter. If the
Remote DBA desires, the percent value could be used to generate a decoded
value of RAISE if the percent is greater than 10, or LOWER if the
value is less than a predetermined value. An example of this script’s
output is shown in Listing 13.2.
SOURCE 13.1 Data dictionary cache report.
REM NAME : DD_CACHE.SQL
REM FUNCTION : GENERATE REPORT ON DATA DICTIONARY CACHE
REM USE : FROM SQLPLUS
REM Limitations : None
REM Date Modified By Reason For change
REM 21-AUG-1991 MIKE AULT INITIAL CREATE
REM 27-NOV-1991 MIKE AULT ADD % CALCULATION TO REPORT
REM 28-OCT-1992 MIKE AULT ADD CALL TO TITLE PROCEDURE
REM 21-Jun-1997 MIKE AULT Updated to ORACLE8
REM 07-nov-2001 MIKE AULT Tested on 9i, reformatted
REM SET FLUSH OFF
REM SET TERM OFF
SET HEAD ON
SET PAGESIZE 59
SET LINESIZE 79
COLUMN parameter FORMAT A20
COLUMN type FORMAT a11
COLUMN percent FORMAT 999.99 HEADING "%";
COLUMN gets FORMAT 999,999 HEADING 'Gets'
COLUMN getmisses FORMAT 999,999 heading 'Get|Misses'
COLUMN count FORMAT 999,999 heading 'Count'
COLUMN usage FORMAT 999,999 HEADING 'Usage'
ttitle "DATA DICTIONARY CACHE STATISTICS"
( getmisses / gets * 100) percent,
gets > 100 AND
getmisses > 0
ORDER BY parameter;
LISTING 13.2 Example output from DD_CACHE
07 page 1
DATA DICTIONARY CACHE STATISTICS
PARAMETER TYPE Gets Misses % Count Usage
-------------------- ----------- ------ ------ ----- ----- --------
dc_constraints PARENT 1,004 503 50.10 509 503
dc_files PARENT 178 2 1.12 11 2
dc_free_extents PARENT 2,413 825 34.19 30 23
dc_global_oids PARENT 114 30 26.32 35 30
dc_histogram_defs PARENT 1,124 165 14.68 166 165
dc_object_ids PARENT 11,498 1,516 13.18 1,397 1,391
dc_objects PARENT 12,232 2,696 22.04 2,749 2,746
dc_rollback_segments PARENT 738 12 1.63 18 13
dc_segments PARENT 7,915 1,070 13.52 1,076 1,068
dc_sequences PARENT 277 53 19.13 58 53
dc_tablespace_quotas PARENT 3,013 2 .07 24 2
dc_tablespaces PARENT 10,281 5 .05 6 5
dc_used_extents PARENT 805 804 99.88 811 801
dc_user_grants SUBORDINATE 13,576 16 .12 16 14
dc_usernames PARENT 4,993 14 .28 21 14
dc_users PARENT 20,200 26 .13 29 26
In reviewing this report, check the
* Review Count and Usage columns: If Usage is
equal to Count, the cache area is being fully utilized.
* If Usage is consistently low compared to
Count, consider reducing the INIT.ORA parameter that controls the
* If Count and Usage are nearly equal and the
percents are greater than 10, consider increasing the INIT.ORA
parameter that controls the caches (SHARED_POOL_SIZE).
Since we are actually concerned only with an
aggregate look at the cache area performance, the following query can
be substituted into the report to give you an overall health
(SUM(getmisses) / SUM(gets)) 'DD CACHE MISS RATIO'
This substitution simplifies the report into:
07 page 1
DATA DICTIONARY CACHE STATISTICS
The usual guidelines state that, if this ratio
gets above 10 percent, take action by increasing the shared pool size
parameter; that said, I have not seen this ratio greater than 1
percent on a properly tuned instance, so I would recommend earlier
action. In our example instance, therefore, we may need to increase
the shared pool size; however, we first need to look at other factors
such as frequency of shared pool flushes (if any) and how the pool is
actually being used before we just start adding to it.
Advanced Tuning of the Shared Pool
Perhaps one of the least-understood aspects of
Oracle Shared Global Area tuning is tuning the shared pool. The
generally accepted tuning methodology involves throwing memory into
the pool until either the problem goes under or the problem is masked.
Here we will examine the shared pool and define a method for tuning it
that uses measurement, not guesswork, to drive the tuning
methodologies. Numerous scripts for examining the shared pool are
What is the shared pool?
Many people know that the shared pool is a
part of the Oracle SGA but little else, so to begin this discussion
it’s necessary to answer exactly,What is the shared pool? The shared
pool contains several key Oracle performance-related memory areas. If
the shared pool is improperly sized, then overall database performance
will suffer, sometimes dramatically. Figure 13.1 diagrams the shared
pool structure located inside Oracle 8i and 9i SGAs.
Shared pools in Oracle8i and Oracle9i.
As you can see from the structures pictured in
Figure 13.1, the shared pool is separated into many substructures. The
substructures of the shared pool fall into two broad areas: the
fixed-size areas, which, for a given database at a given point in time
stay relatively constant in size, and the variable-size areas, which
grow and shrink according to user and program requirements.
In Figure 13.1, the areas inside the library
caches’ substructure are variable in size, while those outside the
library caches (with the exception of the request and response queues
used with MTS) stay relatively fixed in size. The sizes are determined
based on an Oracle internal algorithm that ratios out the fixed areas
based on overall shared pool size, a few of the initialization
parameters, and empirical determinations from previous versions. In
early versions of Oracle (notably 6.2 and earlier), the dictionary
caches could be sized individually allowing a finer control of this
aspect of the shared pool. With Oracle 7, the internal algorithm for
sizing the data dictionary caches took control from the Remote DBA.
The major difference between the shared pools
in Oracle8i and Oracle9i is that any excess memory specified by the
SGA_MAX_CACHE parameter and not used in the actual cache and buffer
definitions will be placed in the miscellaneous area of the shared
The shared pool is used for objects that can
be shared among all users, such as table definitions, reusable SQL
(although non-reusable SQL is also stored there), PL/SQL packages,
procedures, and functions. Cursor information is also stored in the
shared pool. At a minimum, the shared pool must be sized to
accommodate the needs of the fixed areas, plus a small amount of
memory reserved for use in parsing SQL and PL/SQL statements. If this
is not done, ORA-04031 and ORA-07445 errors will result.
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.