Question:
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?
Answer:
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.
This
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'.
For
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:
SQL> exec
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
executing.(pinned)
Session 1:
=========
Do a massive Merge
Join Cartesian
select * from dba_objects a, dba_objects b,
dba_objects c;
Session 2:
=========
Identify the sql
address and hash value and try to purge the cursor..
exec
dbms_shared_pool.purge('00000003DE825198,3691928467','C',65); ==> This
hangs
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
Session 3
==========
select event,p1,p2
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
_kks_use_mutex_pin
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.
