ORACLE 7.3 FAQ's (a few of them)

Here's my contribution to this Newsgroup... Satar Naghshineh
SatarNag_at_aol.com

ORACLE WORLDWIDE SUPPORT FAQs (7.3 Tuning Manual)

Problem: Determining When to Pin Objects in Your Shared Pool Scenario
Pinning objects in the shared pool is a key to tuning this memory structure. Pinning objects in the shared pool reduce fragmentation and help prevent Oracle from encountering the ORA-04031 error. In addition to these problems, another indication that objects will benefit from being pinned in the shared pool is a high number of RELOADS listed in either the 'report.txt' output of UTLBSTAT/UTLESTAT or the RELOADS column in V$SQLAREA. The question of what objects need to be pinned in particular to your database enviroment, the application you are running, the size of your database, and the activity on your database. Depending upon the type of object, there may be various solutions.

Solution
* Find out which application is causing this error. Zero down on which
package/procedure is loaded and try to keep it in the shared pool by pinning it.
* Sometimes, the application may not give the errors. In which case, set
an event parameter in 'init.ora', as follows and generate a trace file.

        event = "4031 trace name errorstack level 10"
* The trace file contains a dump of state objects, when the error occurs.
In the trace file, look for 'load=X' a few lines below that 'name=[name of object]. This error occurs at the time this object is loaded, therefore pin the object in the shared pool, thereby keeping it.
* Query the X$KSMLRU fixed table. This table keeps track of the objects
and the corresponding number of objects flushed out of the shared pool based upon the Least Recently Used (LRU) algorithm.
* This is a fixed table and once queried, Oracle will automatically reset
the table, thus, you can only query the table once. Spool the output to a file so you can capture the output for analysis.
* Describe X$KSMLRU.

NAME          NULL?     TYPE
SSE                            RAW (4)
INDX                            NUMBER
KSMLRCOM                VARCHAR2 (20)
KSMLRSIZ                   NUMBER
KSMLRNUM                NUMBER

Note: The two important columns are: KSMLRNUM [stores the number of objects that were flushed to load the large object] and KSMLRSIZ [stores the size of the object that was loaded contiguous memory allocated].

Example
Issue this query to find all the objects that are larger than size 5k which you may want to pin.

SELECT * FROM x$ksmlru WHERE ksmlrsiz > 5000;

***END*** I hoped this info was helpful to you Satar Naghshineh Received on Fri Aug 22 1997 - 00:00:00 CDT