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')
Pingback: Shared pool tuning due to performance issues | Smart way of Technology