How to pin the objects in shared_pool?

For pinning the objects in the shared_pool, we need to use DBMS_SHARED_POOL package. To create this package we can use $ORACLE_HOME/rdbms/admin/dbmspool.sql script.

Procedure DBMS_SHARED_POOL.KEEP is used to pin the objects.

We need to pass 2 parameters to this procedure.

1. name of the object
2. flag (type of object)

Flag argument can have following values

-> for Packages – P or p (default)
-> for Types – T or t
-> for trigger – R or r
-> for sequence – Q or q

Example:

SQL> exec sys.dbms_shared_pool.keep(‘PKG_LOGIN’);

Here flag parameter is not specified. So default value will be taken.

SQL> exec sys.dbms_shared_pool.keep(‘LOGIN_TR’,’R’);

Objects currently kept in shared_pool can be queried as

SQL> SELECT OWNER, TYPE, NAME, KEPT, LOADS, INVALIDATIONS, SUM(EXECUTIONS), SUM(SHARABLE_MEM) MEM_USED
FROM GV$DB_OBJECT_CACHE WHERE TYPE IN (‘PROCEDURE’,’PACKAGE BODY’,’PACKAGE’,’TRIGGER’) AND OWNER = ‘SCOTT’ GROUP BY OWNER, TYPE, NAME, KEPT, LOADS, INVALIDATIONS ORDER BY NAME
/

Advertisements
This entry was posted in Oracle Database Architecture and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s