Pinning Oracle Packages for Performance
It seems that a lot of people either aren't aware of the process of "pinning" Oracle packages to increase performance, or if they are, they only have a hazy understanding of it. Marking packages as non-swappable ("pinned") prevents paging, which is the tedious process of swapping the code in and out of memory from disk. By marking a package as pinned you eliminate the costly overhead of
- initially loading the package from the hard drive or disk array and then
- unloading the package from memory when the memory space is needed,
- having the system determine that the package is needed again,
- locating the package on the hard drive or disk array,
- loading the code back into memory and reinitializing it
- lather, rinse, repeat
It's not hard to see where this could eat up a significant number of processor cycles that could be used for something else (like getting real work done). In a case like this you're far better off just locking the package into memory and leaving it there. Memory access is cheap and fast, hard drive access is not. Hard drive access is painfully slow and always has a significant cost in I/O.
Oracle provides a standard procedure named "dbms_shared_pool.keep" that allows pinning one or more packages. (To unpin a package, use the "dbms_shared_pool.unkeep" procedure.)
To pin a package or packages at runtime instead of at startup, Oracle provides the procedure "dbmspool.keep". You can use dbmspool.keep to conditionally pin a package as needed.
To pin or not to pin?
The decision whether or not to explicitly pin a package should take into account the size of the object and how often it's likely to be called- if it's called frequently then Oracle may already be keeping it in memory, so pinning it may not make any significant difference. If you have the memory to spare, try pinning it and then watch the overall performance for improvement. If you don't see any, chances are Oracle was keeping it loaded as a result of calls to package.
Deciding which packages to pin is part science and part experience. With enough memory you can opt to be more inclusive in what packages you pin, but in a typical server environment you will need to pick and choose carefully. The value of the shared_pool_size parameter in init.ora will ultimately limit what you can and cannot include. Be aware that loading too many packages will starve the system of memory and will actually result in reduced performance as the system desperately tries to make use of the meager bit of memory that you've left for it. This "memory starvation" almost always results in a quite a bit of disk-thrashing and unnecessary I/O traffic, inevitably forcing system performance down- the very opposite of what you want.
Oracle recommends that you always pin the following packages:
The script to load and pin these packages is as follows:
In general, pin a package, exercise the system, and measure the overall performance. If the change is negligible, unpin the package and free up the memory to be used by other processes.