1. DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD (threshold_size NUMBER);
threshold_size单位为byte, 当申请超过此大小空间时将直接报错而不是free unpinned memory within the shared pool.
The range of threshold_size is 5000 to ~2 GB inclusive.
The procedures provided here may be useful when loading large PL/SQL objects.
在请求多大空间的shared pool时会直接报错而不是freeing objects from the LRU list
shared pool缓存大对象会影响所有已缓存对象，相当于执行了ALTER SYSTEM FLUSH SHARED_POOL，影响整体缓存命中率
Because this impacts all users on the system, this procedure "localizes" the impact to the process failing to find a piece of shared pool memory of size greater than thresh_hold size. This user gets the 'out of memory' error without attempting to search the LRU list.
2. KEEP & UNKEEP
可以设置PL/SQL objects, triggers, sequences, and types， keep后不会aging out
对于常使用的trigger，一般要keep到shared pool，另外如果需要也可以把sequence设置为keep
Sequence numbers are lost when a sequence is aged out of the shared pool.
DBMS_SHARED_POOL is useful for keeping sequences in the shared pool and thus preventing the loss of sequence numbers.
exec DBMS_SHARED_POOL.KEEP('scott.hispackage');
exec DBMS_SHARED_POOL.UNKEEP ('scott.hispackage');
3. 查看大空间使用并清除
SET SERVEROUTPUT ON
DBMS_SHARED_POOL.SIZES (minsize NUMBER); --查看超过指定bytes的缓存对象
关于purge:
DBMS_SHARED_POOL.PURGE (
name VARCHAR2,
flag CHAR DEFAULT 'P',
heaps NUMBER DEFAULT 1);
DBMS_SHARED_POOL.PURGE (
schema VARCHAR2,
objname VARCHAR2,
namespace NUMBER,
heaps NUMBER);
DBMS_SHARED_POOL.PURGE (
hash VARCHAR2,
namespace NUMBER,
heaps NUMBER);
name: Name of the object to purge. The value for this identifier is the concatenation of the address and hash_value columns from the v$sqlarea view.
This is displayed by the SIZES procedure.Currently, TABLE and VIEW objects may not be purged.
flag: (Optional) If this is not specified, then the package assumes that the first parameter is the name of a package/procedure/function and resolves the name.
Set to 'P' or 'p' to fully specify that the input is the name of a package/procedure/function.
Set to 'T' or 't' to specify that the input is the name of a type.
Set to 'R' or 'r' to specify that the input is the name of a trigger.
Set to 'Q' or 'q' to specify that the input is the name of a sequence.
In case the first argument is a cursor address and hash-value, the parameter should be set to any character except 'P' or 'p' or 'Q' or 'q' or 'R' or 'r' or 'T' or 't'.
heaps: Heaps to be purged. For example, if heap 0 and heap 6 are to be purged:
1<<0 | 1<<6 => hex 0x41 => decimal 65, so specify heaps =>65.Default is 1, that is, heap 0 which means the whole object would be purged
namespace Parameter is a number indicating the library cache namespace in which the object is to be searched
hash 16-byte hash value for the object