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

Tips for Using Oracle Stored Procedures

Oracle stored procedures and triggers are faster than traditional code, which means they are becoming increasingly popular. As application code moves away from external programs and into the database engine, DBAs need to understand the related memory requirements for Oracle stored procedures and know how to manage stored procedures for optimal database performance.

Oracle stored procedures and triggers are becoming more popular, and more application code will move away from external programs and into the database engine. Oracle DBAs must be conscious of the increasing memory demands of Oracle stored procedures, however, and carefully plan for the days when all of the database access code (PL/SQL) resides within the database. Today, most Oracle Server databases have only a small amount of code in stored procedures, but this is rapidly changing. There are many compelling benefits to putting all Oracle SQL inside stored procedures, including:


Better performance.
Oracle stored procedures load once into the shared pool and remain there unless they become paged out. Subsequent executions of the stored procedure are far faster than executions of external code.

Coupling of data with behavior.
DBAs can use naming conventions to couple relational tables with the behaviors associated with a table (using Oracle stored procedures as "methods"). If all behaviors associated with the employee table are prefixed with the table name--employee.hire, employee.give_raise, for example--the data dictionary can be queries to list all behaviors associated with a table (select * from dba_objects where owner = 'EMPLOYEE'), and it's easy to identify and reuse code.

Isolation of code.
Since all SQL is moved out of the external programs and into the Oracle stored procedures, the application programs become nothing more than calls to stored procedures. As such, it becomes very simple to swap out one database and swap in another one.

One of the foremost reasons stored procedures and triggers function faster than traditional code is related to caching in the Oracle SGA. After an Oracle stored procedure has been loaded into the shared pool of the SGA, it remains there until it is paged out of memory to make room for other stored procedures. Items are paged out based on a least recently used (LRU) algorithm. Once loaded into the RAM memory of the shared pool, procedures will execute very quickly, and the trick is to prevent pool thrashing as many procedures compete for a limited amount of shared-pool memory.

Source www.rampant-books.com