Re: How to use dbms_shared_pool.purge for heap 6 only?
- From: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
- To: martin.a.berger@xxxxxxxxx
- Date: Wed, 25 Nov 2009 11:26:55 -0600
A simple way to deal with this is just do power(2,&heap).
Heap 0 flushes the whole statement, heap 6 flushes the plans (although
I've never had a reason to flush just the plans. By the way, when
heap=6, plans for all children are flushed.
The backport to 10.2.0.4 seems to work fine as well, but it doesn't
appear to work in versions prior to 10.2.0.4 (10.2.0.3 for example).
Here's the script I use (because I don't like the funky
"address,hash_value" format that the purge procedure requires:
-- flush_sql.sql
DECLARE
name varchar2(50);
version varchar2(3);
BEGIN
select regexp_replace(version,'\..*') into version from v$instance;
if version = '10' then
execute immediate
q'[alter session set events '5614566 trace name context
forever']'; -- bug fix for 10.2.0.4 backport
end if;
select address||','||hash_value into name
from v$sqlarea
where sql_id like '&sql_id';
dbms_shared_pool.purge(name,'C',1);
END;
/
If you want to have the flexibility to select the heap - change the
call to purge like this:
dbms_shared_pool.purge(name,'C',power(2,&heap));
Then just put in 0 or 6 when prompted for the heap.
Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com
On Nov 25, 2009, at 2:43 AM, Martin Berger wrote:
Brandon,
see it as a bit field:
0x41 (hex)
==
65 (dec)
==
1000001 (bin)
^ ^^
| ||
| |+-- Bit 0
| +--- Bit 1
| ...
+-------- Bit 6
In this case, bit 0 and bit 6 are set, all the others are unset.
just for the records:
<< is a 'bit shift' operation,
| is a 'bit or' operation
hth,
Martin
Other related posts:
