Databases Reference
In-Depth Information
We need to keep this value above 85 percent.
Please note that the first time objects need to be loaded into the
cache, so there can never be a 100 percent value for the Hit Ratio.
To improve the Dictionary Cache, we can reduce
DDL
activities and, if we use sequences,
use the
CACHE
option to avoid a get for each
NEXTVAL
call to the sequence.
The size of the Dictionary Cache cannot be changed; it's a part of the Shared Pool and is
automatically maintained by the database. The database uses an algorithm that prefers to
keep dictionary data than library cache data in the shared pool, because the performance
benefits achieved by using the former approach are more significant. We can only size the
Shared Pool using the
SHARED_POOL_SIZE
initialization parameter.
See also
F
The
Tuning
the
Library
Cache
recipe in this chapter
F
The
Reducing
the
number
of
requests
to
the
database
using
sequences
recipe
in
Chapter
2
,
Optimizing Application Design
, for more details on sequences
Tuning the Program Global Area and the
User Global Area
In this recipe, we will see the Program Global Area (PGA) and the User Global Area (UGA)
and how to tune them for maximum performance.
The PGA is used to store real values of bind variables, sort areas, and cursor state
information. In a dedicated server environment this area is in private user memory.
Only in a shared-server environment the session stack space remains in the PGA,
while session data and cursor state are moved into the shared pool.
