I know that it is possible to flush the entire shared pool, but I need a
procedure to flush a specific SQL statement?
Does a procedure exist that will flush the shared pool contents
for a specific session?
Yes, is it possible to kill any session with the "alter
system kill session" syntax, but you can also use the dbms_shared_pool.purge procedure to remove all shared pool contents for
a specific cursor session.
article by Fairlie Rego notes a method for
purging the RAM used by a specific
cursor session using the dbms_shared_pool.purge procedure:
In case the first argument is a cursor address and hash-value,
the parameter should be set to any character except 'P' or 'p' or 'Q' or
'q' or 'R' or 'r' or 'T' or 't'.
example, if heap 0 and heap 6 are to be purged:
1<<0 | 1<<6 => hex
0x41 => decimal 65, so specify heaps =>65.Default is 1, that is, heap 0
which means the whole object would be purged
This feature was
introduced via the fix in bug 5614566 and I actually know a customer who
has this applied on top of 10.2.0.3.
Here is an example is using
dbms_shared_pool.purge to remove RAM for a specific cursor from
the shared pool library cache:
dbms_shared_pool.purge('00000003DE576D40,353632309','C',65); ==> purge
heap 0 and heap 6
PL/SQL procedure successfully completed.
This would actually not work against a cursor which is currently
Do a massive Merge
select * from dba_objects a, dba_objects b,
Identify the sql
address and hash value and try to purge the cursor..
dbms_shared_pool.purge('00000003DE825198,3691928467','C',65); ==> This
and this session is waiting on "cursor: pin X" requesting
an exclusive mutex pin for the cursor object whilst it has already been
pinned by session 1
from v$session where username='SYS' and type='USER';
EVENT P1 P2
----------------------------------------- ---------- ----------
cursor: pin X 3691928467 1
The p1 value here is the Hash
value of the cursor we are trying to flush.
From the short stack
of the process which is executing the purge API a function called
kxsPurgeCursor is called which would try to take a mutex (since
is TRUE by default)
The purge completes only after you cancel the sql in session 1 and
exit from the same or kill the session executing the SQL.