Checked for relevance on 10-Nov-2008


***Checked for relevance on 25-Jul-2010***
For later versions of the Oracle database, please refer to the following documents:
Note.257643.1 Ext/Pub Oracle Database 10g Automated SGA Memory Tuning 
Note.443746.1 Ext/Pub Automatic Memory Management(AMM) on 11g 


HOW TO PIN OBJECTS IN YOUR 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.  Here are some general guidelines to keep in mind 
when pinning objects: 
 
I. If you have encountered the ORA-04031 already and need to resolve it: 
 
  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. 
 
II. 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. 
   
III. You can also pin large packages frequently used by the users. Suggestion: 
     pin the objects immediately after starting up your database. 
 
IV. How to pin a package?  
    
    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: 
    	Eg:-                                                                   

         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);                      
 
V. How to pin a stored procedure/functions ?   
   
    1.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') 
 

Search Words:
=============

ORA-4031