Databases Reference
In-Depth Information
In step 7, we execute a (simple) anonymous PL/SQL block and then query
V$SQLAREA
to
identify the
ADDRESS
and
HASH_VALUE
of the statement. We then use these values as
parameters for the
KEEP
procedure of
DBMS_SHARED_POOL
package to pin the anonymous
block in the Shared Pool.
The complete execution of the steps mentioned earlier is represented in the following
screenshot:
There's more...
Due to the LRU algorithm, blocks of code can be aged out of the shared pool. When a
large block is aged out to make room for a small piece of code, and is needed again, then
the large block is reloaded. There can be fragmentation in the Shared Pool, which causes
performance degradation.
To avoid fragmentation, we can separate the memory required to store frequently used
large blocks of code from other blocks, using the Shared Pool Reserved Space.
We need to set the
SHARED_POOL_RESERVED_SIZE
initialization parameter. Querying
the
V$SHARED_POOL_RESERVED
dynamic performance view, we can inspect the Reserved
Pool statistics.
In this view, we need to lower the value for
REQUEST_MISSES
and
REQUEST_FAILURES
.
Using the
V$DB_OBJECT_CACHE
, we can inquire for large objects that are not kept in the
Shared Pool and decide to keep them in the reserved pool using the
KEEP
procedure of the
DBMS_SHARED_POOL
package. Doing so also prevents flushing of the pinned object when
executing the
ALTER
SYSTEM
FLUSH
SHARED_POOL
command.
