ORA-04031: unable to allocate 760 bytes of shared memory
While Running queries in Oracle DB If you get below error, we should know why and how this error gets generated and how to avoid them.
ORA-04031: unable to allocate 760 bytes of shared memory ("shared pool","unknown object","KK2033","ggsls")
04031. 00000 - "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
*Cause: More shared memory is needed than was allocated in the shared
pool.
*Action: If the shared pool is out of memory, either use the
dbms_shared_pool package to pin large packages,
reduce your use of shared memory, or increase the amount of
available shared memory by increasing the value of the
INIT.ORA parameters "shared_pool_reserved_size" and
"shared_pool_size".
If the large pool is out of memory, increase the INIT.ORA
parameter "large_pool_size".
Vendor code 4031
The initial call to a package causes the whole package to be loaded into the shared pool. For large packages this may represent an unacceptable delay for two reasons. First the size of the package causes a delay in load time. Second if the shared pool is already full, several smaller objects may need to be aged out to make room for it. In these circumstances, performance can be improved by pinning large packages in the shared pool.
Under normal circumstances, objects in the shared pool are placed on a least recently used (LRU) list. If the shared pool is full and a new object must be loaded, items on the LRU list are aged out. Subsequent calls to objects that have been aged out result in them being reloaded into the shared pool.
The processes of pinning objects in the shared pool removes them from the LRU list so they are no longer available to be aged out, regardless of usage. The process of pinning objects in the shared pool is achieved using the dbms_shared_pool package.
The dbms_shared_pool package is not loaded by default, so it must be loaded manually by running the dbmspool.sql script as the SYS user.
SQL> conn sys/password as sysdba Connected. SQL> @$ORACLE_HOME/rdbms/admin/dbmspool.sql Package created. Grant succeeded. View created. Package body created.
Pinning and unpinning objects in the shared pool is achieved using the keep and unkeep procedures, both of which accept the same case-insensitive parameters.
PROCEDURE keep ( name VARCHAR2, flag CHAR DEFAULT 'P' ) PROCEDURE unkeep ( name VARCHAR2, flag CHAR DEFAULT 'P' )
The name parameter is used to specify the object to be pinned or unpinned. The flag parameter specifies the object type, of which the following are valid:
P - Package, Procedure or Function. This is the default value. T - Type. R - Trigger. Q - Sequence. C – Cursor. The current documentation does not list “C” as a valid parameter and suggests that for cursors any parameter can be used. In practice this does not appear to be true and the parameter value of “C” must be specified for cursors.
If the object to be pinned is a cursor, it should be identified using the address concatenated to the hash_value from the v$open_cursor view, as shown below.
SQL> conn sys/password as sysdba Connected. SQL> SELECT address || ',' || hash_value FROM v$open_cursor WHERE rownum = 1; ADDRESS||','||HASH_VALUE -------------------------------------------------------------------- 6A524ABC,3792707131 1 row selected. SQL> EXEC DBMS_SHARED_POOL.keep(‘6A524ABC,3792707131’, ‘C’); PL/SQL procedure successfully completed.
Only objects present in the shared pool can be pinned, so you must either wait for them to be loaded or force the load.
The keep_test_api.sql script creates a package specification and body that will be used to demonstrate the keep and unkeep procedure usage. The package contains a reference to a procedure called stub that performs no work. A call to this procedure forces the package to be loaded into the shared pool so it can be pinned.
keep_test_api.sql CREATE OR REPLACE PACKAGE keep_test_api AS PROCEDURE stub; END keep_test_api; / SHOW ERRORS CREATE OR REPLACE PACKAGE BODY keep_test_api AS -- ----------------------------------------------------------------- PROCEDURE stub AS -- ----------------------------------------------------------------- BEGIN NULL; END stub; -- ----------------------------------------------------------------- END keep_test_api; / SHOW ERRORS
The following examples assume this package has been loaded into a schema called TEST, starting with the an example of the keep procedure.
SQL> conn sys/password as sysdba
Connected.
SQL> EXEC test.keep_test_api.stub;
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_SHARED_POOL.keep('test.keep_test_api');
PL/SQL procedure successfully completed.
To pin certain packages at instance startup, it makes sense to add an equivalent stub procedure to each of them, run a script to call the stubs and then pin the packages.
To unpin the package, use the unkeep procedure shown below.
SQL> conn sys/password as sysdba
Connected.
SQL> EXEC DBMS_SHARED_POOL.unkeep('test.keep_test_api');
PL/SQL procedure successfully completed.
Generally I do not pin objects in the shared pool, preferring instead to let Oracle manage the contents of the shared pool for me. If the application is well written and the shared pool is sized correctly, there should be no need to resort to pinning. In the event you are forced to work with badly written applications or in environments where memory is limited, some gains may be seen using this method. Like all tuning, the key is to experiment in a controlled manner, constantly testing the results of the modifications.
The dbms_shared_pool package contains two more procedures that should be mentioned for the sake of completeness.
The sizes procedure lists all objects present in the shared pool whose size in Kb exceeds the size specified in the procedure call.
SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> EXEC DBMS_SHARED_POOL.sizes(minsize => 300); SIZE(K) KEPT NAME ------- ------ ------------------------------------------- 773 YES SYS.oracle/gss/util/NLSLocale (JAVA CLASS) 470 SYS.STANDARD (PACKAGE) 390 SYS.DBMS_STATS (PACKAGE BODY) 355 SYS.DBMS_RCVMAN (PACKAGE BODY) PL/SQL procedure successfully completed.
The aborted_request_threshold procedure can be used to prevent Oracle from flushing objects from the LRU list to make room for large objects. If there is insufficient free memory, an attempt to load an object whose size is above the threshold value will result in an ORA-4031 error. The threshold can be set as follows.
SQL> EXEC DBMS_SHARED_POOL.aborted_request_threshold(40000); PL/SQL procedure successfully completed.
A look back at the output from the dbmspool.sql script reveals that a view was created. The name of that view was dba_keepsizes and is used to display the total space required by an object when it is kept in the shared pool.
SQL> SELECT * 2 FROM dba_keepsizes 3 WHERE owner = 'SYS' 4 AND name = 'STANDARD'; TOTSIZE OWNER NAME ---------- ------------------------------ -------------------------- 205 SYS STANDARD 1 row selected.
This allows the impact of pinning objects in the shared pool to be assessed prior to the operation taking place.
In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.