Monday, December 14, 2009

Pinning Objects into Shared Pool

Why Pinning?
                    Apps needs more space in SGA for packages and functions.
If SGA is fragmented some of them may not get enough free space.

     IF a package doesn't get contiguous free memory , Database will try to free the memory by deleting some objects using LRU algorithm……..
 Even then, if it unable to get enough memory , then ORA-04031 error is thrown….

Set SGA_TARGET parameter to use Automatic shared Memory Management-----this resolves the error to some extent...

 So……. Pinning objects into shared pool increases database performance to a great extent.
This is because , these pinned objects need not be loaded and parsed from database…….as they reside in the SQL and PL/SQL area of SGA.
                                     
NOTE : Care has to be taken while choosing which objects have to be pinned.
Generally, large and frequently used packages are pinned……….whereas pinning
Unnecessary or rarely used objects may degrade the DB performance.

To identify the objects to be pinned, we need to know which objects are executed more frequently.

This is done by running the script below……… which creates a ADXCKPIN.lst file---------- which is the list of objects in the shared pool.

$sqlplus system/password@AD_TOP/sql/ADXCKPIN.sql


Or Simply----


select owner,name,type,sharable_mem from v$db_object_cache where kept='YES';


 And then do

SQL>
select * from x$ksmlru;

Gives current shared pool objects and the corresponding number of objects flushed out of the shared pool to allocate space.

Once you query the table, the database will reset the table.

How to pin?

Run the scripts ADXGNPIN.sql for packages and functions
and ADXGNPNS.sql for sequences…

$sqlplus sys/password@AD_TOP/sql/ADXGNPIN.sql APPS

 
T
his, by default, pins all objects under Apps Schema….so it is good to edit the script and pin the objects chosen….
Manually-----This can be done by executing a script…………..

SQL> exec dbms_shared_pool.keep('schema.object');


Some of the packages in SYS schema which are most used in Apps environment…….

DBMS_STANDARD

DBMS_UTILITY
DBMS_DESCRIBE

DBMS_OUTPUT

STANDARD

DBMS_ALERT
DBMS_DDL
DBMS_LOCK

DBMS_SESSION

DBMS_SHARED_POOL

No comments:

Post a Comment