Load Oracle Package in SGA (Memory) at Startup

Load Oracle Package in SGA at Startup

Normally whenever you call package for the first time, the entire package will be loaded in the memory. Memory is a shared area called share pool. Whenever there is a call to another stored procedure/function, oracle will try to find space in shared pool. If memory is available, object will be loaded into the memory. And if memory is not available then oracle will page out old procedures in memory using LRU (Least Recently Used) algorithm. If the large package which fit into the memory initially may not fit into contiguous memory when it is reloaded after paging out. It may give error ORA-4031.

Pin Oracle Package

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.

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_UTILITY');
execute dbms_shared_pool.keep('STANDARD');

You can call this script at database startup. It will load all the required packages and procedures in the memory at startup.

Reference: http://www.dba-oracle.com/art_proc.htm