Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 22 July 2012

How to Report on object pinned in database object cache

The DBMS_SHARED_POOL package provides access to the shared pool, which is the shared memory area where cursors and PL/SQL objects are stored. DBMS_SHARED_POOL enables us to display the sizes of objects in the shared pool, and mark them for keeping or unkeeping in order to reduce memory fragmentation.
DBMS_SHARED_POOL is also useful for frequently executed triggers. You may want to keep compiled triggers on frequently used tables in the shared pool.
Additionally, DBMS_SHARED_POOL supports sequences. Sequence numbers are lost when a sequence is aged out of the shared pool. DBMS_SHARED_POOL is useful for keeping sequences in the shared pool and thus preventing the loss of sequence numbers.
To create DBMS_SHARED_POOL, run the DBMSPOOL.SQL script. Genrally you will find it in ORACLE_HOME under RDBMS directory.

   name VARCHAR2,  flag CHAR      DEFAULT 'P');

   name VARCHAR2,  flag CHAR     DEFAULT 'P');
The above two procedure will keep/unkeep an object in shared pool. It is useful for frequently used large objects. When large objects are brought into the shared pool, several objects may need to be aged out to create a contiguous area large enough.

SELECT   owner, TYPE, NAME, sharable_mem
    FROM v$db_object_cache
    WHERE kept = 'YES' AND owner LIKE UPPER ('&&enter_owner_name')
    ORDER BY 1, 2, 3 DESC;
Sample Output
Enter value for enter_owner_name: HRMS
21-JUL-12       Objects Pinned By DBMS_SHARED_POOL.KEEP Procedure
                Object                  Object                                    Shared
Schema     Type                    Name                                     Memory
---------- ------------------- ---------------------------- ---------------------------------------

The script will provide the Report on objects pinned in database cache with the dbms_shared_pool.keep procedure. the object will be marked KEP automatically by oracle under the following circumstances:
1. Any object required during bootstrapping (starting up) the database.
2. Any object created during the CREATE DATABASE command.
3. Any in-memory temporary tables (feature in later Oracle versions).


Post a Comment