Re: How to use dbms_shared_pool.purge for heap 6 only?

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: