How to pin and unpin the SQL Query with DBMS SHARED POOL package in Oracle Database

Leave a reply

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 and other objects present in Oracle

-- For Pin procedure , function and package
exec dbms_shared_pool.keep('', 'p')
-- For Pin Triggers
exec dbms_shared_pool.keep('', 'r')
-- For Pin Sequence
exec dbms_shared_pool.keep('', 'q')
--For unpin procedure, function and package
exec dbms_shared_pool.unkeep('', 'p')
-- For unpin Triggers
exec dbms_shared_pool.unkeep('', 'r')
-- For unpin Sequence
exec dbms_shared_pool.unkeep('', 'q')