Pin packages is Oracle Apps
                                                 Last update (2008-05-04 17:20:55)
                                                                                                                   Date added (2008-05-04 17:15:53)

Summary
Oracle Applications has a lot of packages under the APPS schema. Pinning the most frequently used is a good performance improvement. This script gives a rough idea of how well pinning is working for you. If you get a lot of results, then you may need to make adjustments to where clause.

You can look at the loads and executions and sharable memory to gauge if you are pinning effectively.
select substr(owner, 1,20) owner,substr(name,1,40) name, substr(type,1,15) type, 
to_char(sharable_mem/1024,'9,999.9') "SPACE(K)",
loads,executions execs from v$db_object_cache where kept='NO' and 
type in ('PACKAGE','PACKAGE BODY','PROCEDURE','TRIGGER','TYPE BODY','FUNCTION','SEQUENCE','CURSOR','JAVA CLASS')
and loads > 1 and executions >1 and sharable_mem/1024 > 25
order by loads desc;

select substr(owner, 1,20) owner,substr(name,1,40) name, substr(type,1,15) type, 
to_char(sharable_mem/1024,'9,999.9') "SPACE(K)",
loads,executions execs from v$db_object_cache where kept='YES' and
type in ('PACKAGE','PACKAGE BODY','PROCEDURE','TRIGGER','TYPE BODY','FUNCTION','SEQUENCE','CURSOR','JAVA CLASS')
and owner not in ('SYS','SYSTEM')
order by owner,name;
Reviews
Filters
Search