Pin and unpin the Queries with DBMS SHARED POOL Package

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 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')

1 thought on “Pin and unpin the Queries with DBMS SHARED POOL Package

  1. Pingback: Shared pool tuning due to performance issues | Smart way of Technology

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.