Pinning Memory Objects
The concept of memory fencing and pinning objects into memory has been around since the 1960's.
Any time a piece of data or program is used frequently, the database should have the ability to make that object ineligible for a page-out. IBM first introduced this concept with the introduction of the MVS operating system, but now has extended it to work with databases such as Oracle.
The idea is that any PL/SQL, SQL, or data blocks that are frequently referenced by the application should be given special status, so that it will always remain in memory when needed.
The trick, of course, is to identify what SQL or data should be kept in memory at the expense of other items in the shared pool or data buffer. That is the real challenge of Oracle tuning.
In all of the guidelines stated so far it has been mentioned that the memory is usually allocated above and beyond that needed for
- fixed size areas and
- pinned objects.
How do you determine what to pin? Generally speaking any package, procedure, function or cursor that is frequently used by your application should be pinned into the shared pool when the database is started. I suggest adding a null startup function
to every in house generated package. It looks like the following piece of code.
FUNCTION start_up RETURN number IS
null startup function
The purpose of the null startup function is to provide a touch point to pull the entire package into the shared pool.
This allows you to create a startup SQL procedure that pulls all of the application packages into the pool and pins them using the DBMS_SHARED_POOL package.
The DBMS_SHARED_POOL package may have to be built in earlier releases of Oracle.
The DBMS_SHARED_POOL package is built using the DBMSPOOL.SQL and PRVTPOOL.PLB scripts located in (UNIX) $ORACLE_HOME/rdbms/admin or (NT)
(where x: is the home drive for your install).