How To Pin Oracle Packages

To prevent paging, you can mark packages as non-swappable, telling the database that after their initial load they must always remain in memory. This is called pinning, or memory fencing. Oracle provides the procedure dbms_shared_pool.keep for pinning a package. You can unpin packages by using dbms_shared_pool.unkeep. In addition to pinning packages at database startup time, you can call the dbmspool.keep procedure at runtime to pin a package of stand-alone stored procedures.

The choice of whether to pin a procedure in memory is a function of the size of the object and the frequency in which it is used.

Very large Oracle stored procedures that are called frequently might benefit from pinning, but you might never notice any difference in that case, because the frequent calls to the procedure will have kept it loaded into memory anyway.

In an ideal world, the init.ora shared_pool_size parameter would be large enough to accept every package, stored procedure, and trigger your applications might invoke. Reality, however, dictates that the shared pool cannot grow indefinitely, and you need to make wise choices regarding which objects you pin. You can query the sharable_mem column of the v$db_object_cache table to see how much memory each package consumes in the library cache.

Oracle Corporation recommends that you always pin the STANDARD, DBMS_STANDARD, DBMS_UTILITY, DBMS_DESCRIBE, and DBMS_OUTPUT packages in the shared pool. You can use the following code:

connect internal;
@/usr/oracle/rdbms/admin/dbmspool.sql
execute dbms_shared_pool.keep('sys.standard');

You can write a standard procedure to pin all of the recommended Oracle packages into the
shared pool. Here is the script:
execute dbms_shared_pool.keep('DBMS_ALERT');
execute dbms_shared_pool.keep('DBMS_DDL');
execute dbms_shared_pool.keep('DBMS_DESCRIBE');
execute dbms_shared_pool.keep('DBMS_LOCK');
execute dbms_shared_pool.keep('DBMS_OUTPUT');
execute dbms_shared_pool.keep('DBMS_PIPE');
execute dbms_shared_pool.keep('DBMS_SESSION');
execute dbms_shared_pool.keep('DBMS_SHARED_POOL');
execute dbms_shared_pool.keep('DBMS_STANDARD');
execute dbms_shared_pool.keep('DBMS_UTILITY');
execute dbms_shared_pool.keep('STANDARD');

Source www.rampant-books.com