Databases Reference
In-Depth Information
The result obtained in step 2 is now clear. We were presented with the number of
GETS
,
PINS
,
RELOADS
, and
INVALIDATIONS
, grouped according to the type of statement (the
NAMESPACE
column). The
GETHITRATIO
and
PINHITRATIO
are the parameters to inspect
when checking the use of the Library Cache.
The query in step 3 summarized all the ratios mentioned earlier in a single parameter—the
Library Cache Hit Ratio that is used to determine the average wealth of the Library Cache
immediately.
Even after using bind variables, shared SQLs, and so on,
we still see heavy reloads, so now what do we do? You
need to increase the size of the Library Cache. There is not
a parameter to size the Library Cache, but we can increase
the size of the
SHARED_POOL_SIZE
parameter.
How to minimize misses
To minimize misses in Library Cache (or reparsing, it's the same), we need to modify the
applications, as shown in previous chapters, to make sure the statements are shared;
for example, using bind variables and composing dynamic SQL statements in the same
way. If we cannot modify our application, we can try the
CURSOR_SHARING
parameter,
to determine when SQL statements are considered as identical, hence sharing the
corresponding execution plan in the Library Cache.
See also
F
The
Improving
performance
sharing
reusable code
recipe in
Chapter
2
,
Optimizing Application Design
F
Using
bind
variables
in
Chapter
4
,
Optimizing SQL Code
F
Minimizing
latches
using
bind
variables
in
Chapter
11
,
Tuning Contention
Tuning the Shared Pool
In the previous recipe, we have seen how to inspect and tune the Library Cache, which
is a part of the Shared Pool. In this recipe, we will see the memory structures in the
Shared Pool and how we can tune it by keeping PL/SQL blocks in it.
