Tuning Memory - Oracle Database 11gR2 Performance Tuning - page 389

The SIZES procedure of the same package allows us to identify the objects that exceed the
defined size in the Shared Pool.
A problem may arise when there are large PL/SQL anonymous blocks. In these situations,
we have two alternatives—the first, as explained in the recipe, is to keep the anonymous block
in the Reserved Pool, using the ADDRESS and HASH_VALUE to identify the statement to keep;
these values are obtained from the V$SQLAREA dynamic performance view.
The second alternative, to improve performance when we have large PL/SQL anonymous
blocks, is to divide large blocks into smaller blocks that execute stored procedures.
We can use the V$SHARED_POOL_ADVICE dynamic performance
view to obtain information about estimated parse time in the shared
pool for different shared pool sizes, with a range from 10 percent to
200 percent of the current shared pool size, in equal intervals.
The column ESTD_LC_TIME_SAVED indicate the estimated elapsed
parse time saved in seconds, while the ESTD_LC_LOAD_TIME
column contains estimated elapsed time in seconds for parsing.
Tuning the Dictionary Cache
Another task to tune the Shared Pool is to tune the Dictionary Cache. This is the memory
structure, where the statements related to the Data Dictionary, the logical structures stored
in the database, are cached. The Data Dictionary is queried often, for example, to retrieve
information about the database objects involved in a query, grants to the user, this data
should be accessed very fast.
The V$ROWCACHE dynamic performance view enables us to query for updated statistics
on the Dictionary Cache. The data in this view is cumulative since instance start-up. The
PARAMETER column identifies the data dictionary item, the total number of requests,
GETMISSES identifies the number of requests not satisfied by the cache, MODIFICATIONS
identifies the number of times the data (related to the item) was updated, and FLUSHES
identifies the number of times the item was flushed to the disk.
An instance and a database are two different items. The database
is a collection of physical files or disks, while an instance is a set
of Oracle background processes/threads and a shared memory
area. An instance can mount and open only a single database; a
database may be mounted and opened by one or more instances
at a time (as in Oracle Real Application Cluster).
In this recipe, we also calculate the Hit Ratio for each item. We can also calculate a
cumulative Hit Ratio for the Dictionary Cache, as done for the Library Cache:
SELECT SUM(GETS—GETMISSES) / SUM(GETS) AS "Hit Ratio"
FROM V$ROWCACHE;