Databases Reference
In-Depth Information
7.
Keep PL/SQL anonymous blocks in the shared pool:
DECLARE I NUMBER;
BEGIN
/* BLOCK_TO_KEEP */
I := 26;
END;
/
SELECT ADDRESS, HASH_VALUE
FROM V$SQLAREA
WHERE SQL_TEXT LIKE '%BLOCK_TO_KEEP%'
AND COMMAND_TYPE = 47;
EXEC SYS.DBMS_SHARED_POOL.KEEP ('3F0A8A14,1609869453);
How it works...
In the Shared Pool, it is possible to have fragmentation, because loading large objects requires
more free space, so we need to unload many small objects to free the required space. The
freed space may be not contiguous, leading to fragmentation. To avoid this situation, we can
reserve some space for large objects and keep them in this reserved space.
We can also "pin" some objects in the Shared Pool using the
DBMS_SHARED_POOL.KEEP
procedure. The pinned objects
are removed from the Least Recently Used list, so they are
never aged out and removed from the Shared Pool.
You can experience ORA-4031 error (unable to allocate "x"
bytes of shared memory) if there is no free block with the
required memory in the shared pool, due to fragmentation.
You can find a good place to start investigating ORA-4031
error on Oracle Blogs at the following site:
http://blogs.oracle.com/db/entry/ora-4031_
troubleshooting
