| |
 |
|
Oracle Tips by Burleson |
What to Pin
In all of the rules stated so far, I mention
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.
Packages, cursors, sequences, triggers,
procedures, and functions may be pinned in Oracle databases using
the DBMS_SHARED_POOL package. The DBMS_SHARED_POOL package may have
to be built in earlier releases of Oracle. This is done using the
DBMSPOOL.SQL and PRVTPOOL.PLB scripts, located in (UNIX) $ORACLE_HOME/rdbms/admin
or (NT) x:\ora9i\rdbms\admin, where x: is the home drive for your
install.
How do you determine which packages,
procedures, or functions to pin?
Actually, Oracle has made this easy by
providing the V$DB_OBJECT_CACHE view that shows all objects in the
pool, and, more importantly, how they are being utilized. The report
in Source 13.9 provides a list of objects that have been loaded more
than once and have executions greater than 1. Some example output
from this script is shown in Listing 13.8. A rule of thumb is that
if an object is being frequently executed and frequently reloaded,
it should be pinned into the shared pool.
This is an excerpt by Mike Ault’s book “Oracle9i
Administration & Management” . If you want more current Oracle
tips by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s
Oracle Scripts Download. |
