RE: How to use dbms_shared_pool.purge for heap 6 only?
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: Steve Baldwin <stbaldwin@xxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 24 Nov 2009 13:33:52 -0700
Yes, thank you! It's been about 10 years since I've done any bitshifting :-)
Continuing in the direction you pointed me, heap 6 = 1 << 6 = 1000000 = 0x40 =
64
And, here you can see I was able to successfully flush *only* heap 6
(v$sql_plan), while retaining heap 0 (v$sql) by specifying heaps=>64:
KHAMELEON@demo>variable v1 number;
KHAMELEON@demo>begin :v1 := 1000;
2 end;
3 /
PL/SQL procedure successfully completed.
KHAMELEON@demo>select object_name from testtab where object_id=:v1;
no rows selected
KHAMELEON@demo>select hash_value, sql_text from v$sql where sql_text like
'%testtab%';
HASH_VALUE SQL_TEXT
----------
----------------------------------------------------------------------
476618282 select hash_value, sql_text from v$sql where sql_text like
'%testtab%'
1318514152 select object_name from testtab where object_id=:v1
KHAMELEON@demo>select address, loads, loaded_versions, parse_calls, executions
from v$sql where hash_value = 1318514152;
ADDRESS LOADS LOADED_VERSIONS PARSE_CALLS EXECUTIONS
---------------- ---------- --------------- ----------- ----------
0000000072265D08 1 1 1 1
KHAMELEON@demo>select count(*) from v$sql_plan where hash_value = 1318514152;
COUNT(*)
----------
2
KHAMELEON@demo>exec
sys.dbms_shared_pool.purge('0000000072265D08,1318514152','C',64);
PL/SQL procedure successfully completed.
KHAMELEON@demo>select address, loads, loaded_versions, parse_calls, executions
from v$sql where hash_value = 1318514152;
ADDRESS LOADS LOADED_VERSIONS PARSE_CALLS EXECUTIONS
---------------- ---------- --------------- ----------- ----------
0000000072265D08 1 0 1 1
KHAMELEON@demo>select count(*) from v$sql_plan where hash_value = 1318514152;
COUNT(*)
----------
0
-----Original Message-----
From: Steve Baldwin [mailto:stbaldwin@xxxxxxxxxxxxxxxx]
I guess the heaps are represented in a bitmask.
Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do not
consent to Internet email for messages of this kind. Opinions, conclusions and
other information in this message that do not relate to the official business
of this company shall be understood as neither given nor endorsed by it.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
