How to Pin objects to shared Pool?

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.