How To Pin Objects in Your Shared Pool [ID 1012047.6]

Normal 0 7.8 pt 0 2 false false false MicrosoftInternetExplorer4

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

[@more@]