How to PIN or UNPIN the objects like table,procedure,function in Memory

 In Oracle, you can pin objects into the shared pool using the DBMS_SHARED_POOL package. This package provides procedures to manage objects in the shared pool, including pinning and unpinning objects. Here's how you can pin objects into the shared pool:

1. Identify the Objects: First, identify the objects you want to pin into the shared pool. These could be frequently used packages, procedures, functions, or other database objects.

2. Use DBMS_SHARED_POOL Package:

    Pinning

• To pin an object into the shared pool, you can use the DBMS_SHARED_POOL.KEEP procedure

For example:

EXEC DBMS_SHARED_POOL.KEEP('YOUR_OBJECT_NAME');

Replace 'YOUR_OBJECT_NAME' with the name of the object you want to pin.

    Unpinning

    

EXEC DBMS_SHARED_POOL.UNKEEP('YOUR_OBJECT_NAME');

    

• If you want to unpin multiple objects, you can call the UNKEEP procedure for each object individually.

3.Verify Pinning/Unpinning :

• After pinning/Unpinning the objects, you can verify whether they are pinned/Unpinnned in the shared pool by querying the V$DB_OBJECT_CACHE view. This view provides information about objects cached in the shared pool.

SELECT * FROM V$DB_OBJECT_CACHE WHERE NAME = 'YOUR_OBJECT_NAME';

• Replace 'YOUR_OBJECT_NAME' with the name of the object you pinned.

• If the object is pinned, the IS_PERSISTENT column will have a value of 'YES'.

• If the object is unpinned, the IS_PERSISTENT column will have a value of 'NO'.

4. Monitor Performance:

Monitor the performance of your system after pinning/Unpinning objects into the shared pool to ensure that it meets your expectations.

Pinning objects can improve performance by preventing them from being aged out of the shared pool due to memory pressure.

Unpinning objects may free up memory in the shared pool and could impact performance.

• Be cautious when unpinning objects from the shared pool, as it can affect performance depending on the usage patterns of the unpinned objects.

5. Considerations:

Be cautious when pinning objects into the shared pool, as it can consume memory and potentially lead to contention if not managed properly.

Regularly review the objects pinned in the shared pool and unpin any objects that are no longer needed to free up memory.

Remember to have appropriate privileges (such as the EXECUTE privilege on DBMS_SHARED_POOL package) to execute these procedures.