V$SHARED_POOL_RESERVED.REQUEST_FAILURES [or SUM(X$KGHLU.KGHLUNFU)] gives the number of ORA-04031 errors that have occurred since the instance was started. If any ORA-04031 errors are occurring, then SHARED_POOL_SIZE and/or JAVA_POOL_SIZE are too small, the shared pool is fragmented, or application code may not be being shared optimally. The query in this listing checks the ORA-04031 errors that have occurred since the instance was started.
If any ORA-04031 errors have occurred, then some SHARED_POOL_SIZE, JAVA_POOL_SIZE, and/or application tuning is in order. Consider one or more of the following:
- Pin large, high-use [high values for X$KSMLRU.KSMLRSIZ, COUNT(X$KSMLRU.KSMLRHON), and/or X$KSMLRU.KSMLRNUM] PL/SQL packages in memory with DBMS_SHARED_POOL.KEEP:
- Pin large, high-use Java classes with DBMS_SHARED_POOL.KEEP. You can pin a Java class by enclosing it in double quotes:
TIP
Enclose the class in double quotes if it contains a slash (/); otherwise, you will get an ORA-00995 error.
- Increase the size of the shared pool by increasing the SHARED_POOL_SIZE initialization parameter if the percentage of SHARED_POOL free memory is low and there is contention for Library Cache space allocation and/or more than zero occurrences of the ORA-04031 error. The earlier section “Shared Pool” notes that increasing the shared pool is not always recommended if a low amount of shared pool memory is observed. If you are increasing the size of the shared pool, you might also need to raise the value of the parameter MEMORY_TARGET, MEMORY_MAX_TARGET, and/or SGA_TARGET.
- Increase the size of the shared pool reserved area by increasing the SHARED_POOL_RESERVED_SIZE initialization parameter (the default is 5 percent of SHARED_POOL_SIZE).
- Promote the sharing of SQL, PL/SQL, and Java code by application developers.
Leave a Reply