How to solve this error?
1. Find out which application is causing this error. Zero down on which package/procedure is loaded and try to keep it in shared pool by pinning it.
2. Sometimes, the application may not give the errors. In which case, set an event in init.ora, as follows and generate a trace file.
event = "4031 trace name errorstack level 2"
or with 9i and higher and spfiles you can issue
alter system set events='4031 trace name errorstack level 2';
What to look for in the trace?
The trace contains a dump of state objects, when the error occurs.
Look for 'load=X' a few lines below that 'name='[name of the object]. So, this error occurs at the time loading this object. Pin that object in the shared pool, thereby keeping it.
How to Pin objects to shared Pool?
Pinning objects to the shared pool is a key to tuning your shared pool. Having objects pinned will reduce fragmentation and changes of encountering the ORA-04031 error.
You must determine which objects to pin. These are particular to your own database, the application you are running, the size of your database, and the activity on your database.
You need to pin objects when any of these happen:
1. If you have encountered the ORA-04031 already and need to resolve it or
2. You can also pin large packages frequently used by the users. (It is better to pin necessary objects while startup of database.
Pinning a package to shared pool
1. Oracle automatically loads SYS.STANDARD, SYS.DBMS_STANDARD and SYS.DIUTIL. Here is an example:
pk1 is a package with a variable called dummy. Assigning dummy to a value and then executing the package will load it into the shared pool:
Example:-
begin
pk1.dummy := 0 ; /* THIS ASSIGNMENT TO THE DUMMY VARIABLE IS BY */
end; /* EXECUTING THE PACKAGE. */
2.Then you must pin the package. Here is an example:
execute dbms_shared_pool.keep(owner.pk1);
Pinning stored procedure/function to shared pool
You can pin procedures and triggers with the dbms_shared_pool procedure. Either procedures or packages can be pinned with the 'P' flag, which is the default value (so you can leave it out). Triggers are pinned with 'R' and anonymous plsql blocks need any letter other than [p,P,r,R] as a flag. Refer to dbmspool.sql for more documentation.
Here is an example:
execute dbms_shared_pool.keep(owner.trigger, 'R');
What objects should I pin?
You can check the x$ksmlru fixed table. This table keeps track of the objects and the corresponding number of objects flushed out of the shared pool to allocate space for the load. These objects are stored and flushed out based on the Least Recently Used (LRU) algorithm.
Because this is a fixed table, once you query the table, Oracle will automatically reset the table, thus, you can only query the table once. Suggestion for workaround: spool the output to a file so you can capture the output for analysis.
describe x$ksmlru
Table or View x$ksmlru
Name Null? Type
------------------------------- -------- -------------- ADDR RAW(4)
INDX NUMBER
KSMLRCOM VARCHAR2(20)
KSMLRSIZ NUMBER
KSMLRNUM NUMBER
KSMLRNUM stores the number of objects that were flushed to load the large object. KSMLRISZ stores the size of the object that was loaded (contiguous memory allocated)
We do not need the other columns.
Here is an example of a query you issue to find all the objects that are larger than size 5k which you may want to pin:
select * from x$ksmlru where ksmlrsiz > 5000;
In general, pinning SYS.STANDARD ,SYS.DBMS_STANDARD & SYS.DIUTIL which are large packages used by Oracle, should help.
SYS.DIUTIL is used only during generations of SQL*forms so it may not be necessary to pin this package in your production database.
