Pin and unpin the Queries with DBMS SHARED POOL Package

Pin the SQL in the Shared Pool area

Following are the steps to pin the SQL in Shared pool:

1. Run the Statement which you want to pin.

SQL> SELECT 'Example TEST' FROM dual;

'EXAMPLETEST
------------
Example TEST


2. Check the Query in V$SQL.


select substr(sql_text,1,15) Text,address,hash_value,KEPT_VERSIONS
from v$sql where sql_text like '%Example%';

TEXT             ADDRESS          HASH_VALUE KEPT_VERSIONS
---------------- ---------------- ---------- -------------
SELECT 'Example  00007FFED12C1210 2929179549 0

Note: KEPT_VERSIONS tell that query is in keep shared pool or not

3. Pin the upper SQL with ADDRESS and HASH_VALUE parameter:

exec DBMS_SHARED_POOL.KEEP('00007FFED12C1210,2929179549','C');


4. Check the Status of KEPT_VERSIONS

select substr(sql_text,1,15) Text,address,hash_value,KEPT_VERSIONS
from v$sql where sql_text like '%Example%';

TEXT             ADDRESS          HASH_VALUE KEPT_VERSIONS
---------------- ---------------- ---------- -------------
SELECT 'Example  00007FFED12C1210 2929179549 65

UNPIN the Query from Shared pool
1. Unpin the upper query which is pinned in first example.

-- Unkeep the upper Query
exec DBMS_SHARED_POOL.UNKEEP('00007FFED12C1210,2929179549','C');


2. Check status of keep query.
Note: KEPT_VERSIONS again becomes 0.

select substr(sql_text,1,15) Text,address,hash_value,KEPT_VERSIONS
from v$sql where sql_text like '%Example%';

TEXT             ADDRESS          HASH_VALUE KEPT_VERSIONS
---------------- ---------------- ---------- -------------
SELECT 'Example  00007FFED12C1210 2929179549 0

Pin and unpin the Procedures objects

-- For Pin procedure , function and package
exec dbms_shared_pool.keep('', 'p')

-- For unpin Sequence
exec dbms_shared_pool.unkeep('', 'q')