Stored Packages Tuning

Stored Packages Tuning :
Exploit the Power of SQL and PL/SQL
All of the SQL tuning and PL/SQL tuning tips and techniques detailed in the previous sections apply directly to database packages.

Group Like Procedures into a Stored Package
It is often a good idea to group common procedures into a single package procedure. This is most appropriate when you have a number of procedures that are always referenced together or that physically call each other. Rather than having to call each procedure into memory as required, a common set of procedures is loaded into memory on the initial reference of any procedure within that group (package).

Pinning Procedures into Memory
Once a stored procedure has been loaded into the shared buffer pool, it can be "pinned" into the buffer pool via a special Oracle database package procedure. Pinning large, frequently accessed procedures into memory can be very beneficial. Pinned procedures can never be swapped out of memory until the database is shut down or until they are explicitly unpinned. (Note that flushing the shared pool will not release a pinned object.)

sqlplus> execute dbms_shared_pool.keep  ('scott.Chk_Value_Prc');
         execute dbms_shared_pool.unkeep('scott.Chk_Value_Prc');

Be careful not to pin too many of your application procedures and functions into memory, or you will use up all available shared buffer space and the performance of the remainder of the application will suffer.