Oracle Flushing-Pinning PL/SQL packages, FLUSH SHARED POOL

Oracle Flushing-Pinning PL/SQL packages, FLUSH SHARED POOL

http://www.remote-dba.cc/oracle_tips_flushing_secrets.htm
1. Pinning all commonly used PL/SQL packages.

The initial call to a package causes the whole package to be loaded into the shared pool. For large packages this may represent an unacceptable delay for two reasons. First the size of the package causes a delay in load time. Second if the shared pool is already full, several smaller objects may need to be aged out to make room for it. In these circumstances, performance can be improved by pinning large packages in the shared pool.

Under normal circumstances, objects in the shared pool are placed on a least recently used (LRU) list. If the shared pool is full and a new object must be loaded, items on the LRU list are aged out. Subsequent calls to objects that have been aged out result in them being reloaded into the shared pool.

The processes of pinning objects in the shared pool removes them from the LRU list so they are no longer available to be aged out, regardless of usage. The process of pinning objects in the shared pool is achieved using the dbms_shared_pool package.

http://www.dba-oracle.com/plsql/t_plsql_pinning_pkgs.htm
http://www.oracle-training.cc/oracle_tips_pinning_packages.htm



SQL> conn sys/password as sysdba
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/dbmspool.sql


Pinning and unpinning objects in the shared pool is achieved using the keep and unkeep procedures, both of which accept the same case-insensitive parameters.

PROCEDURE keep (
name VARCHAR2,
flag CHAR DEFAULT 'P'
)

PROCEDURE unkeep (
name VARCHAR2,
flag CHAR DEFAULT 'P'
)

P - Package, Procedure or Function. This is the default value.
T - Type.
R - Trigger.
Q - Sequence.
C – Cursor.


SQL> conn sys/password as sysdba
Connected.
SQL> SELECT address || ',' || hash_value FROM v$open_cursor WHERE rownum = 1;



SQL> EXEC DBMS_SHARED_POOL.keep(‘

,2. FLUSH SHARED POOL

Periodically issuing the--
ALTER SYSTEM FLUSH SHARED POOL;
alter system flush buffer_pool;
----------
This will "flush" out, or clear, all SQL statements that are in the Shared Pool Area. Oracle keeps track of each SQL statement that users execute.
It is stored parsed in memory so that if a SQL statement already has been executed then Oracle does not need to re-parse it. The exception is if the shared pool area is not large enough, then the least recently used SQL statements (except for pinned packages) will be removed from memory.

By flushing the shared pool, all SQL statements are removed from memory.

----------