Stored Procedures and Function Tuning

Stored Procedures and Function Tuning :
Exploit the Power of SQL and PL/SQL
All of the SQL tuning and PL/SQL tuning tips and techniques detailed in this knowledge base apply directly to database procedures.

Increase the Size of the Shared Pool
All executing stored procedures are resident (in parsed pcode form) in the shared buffer pool. Once a procedure has finished executing, it is eligible to be swapped out of memory. Increasing the size of the shared pool will also increase the likelihood of a common stored procedure remaining in memory from one execution to the next. Monitor the shared buffer pool and increase it (physical memory permitting) if you think that it is not retaining commonly used SQL and PL/SQL code segments long enough.

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.