Flush shared_pool with dbms_shared_pool.purge no working
809288Nov 14 2011 — edited Nov 17 2011Hello, i have a question/problem, recently i need to age out from shared_pool one sql statement, i read about dbms_shared_pool.purge and tried it on test environment, it worked.
But when i try to do same thing on production db, its do nothing:
list of sqlarea with high versions:
SQL_ID MB_USED ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT LOADED_VERSIONS OPEN_VERSIONS INVALIDATIONS PARSE_CALLS
*93mfpb7a8fa20* 203.813298 *0000000223EEC2F0 3565627456* 266 268 268 266 0 2 267
trying to purge:
SQL> exec dbms_shared_pool.purge *('0000000223EEC2F0, 3565627456','C')*;
PL/SQL procedure successfully completed.
list of sqlarea (again) with high versions:
SQL_ID MB_USED ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT LOADED_VERSIONS OPEN_VERSIONS INVALIDATIONS PARSE_CALLS
*93mfpb7a8fa20* 203.813298 *0000000223EEC2F0 3565627456* 266 268 268 266 0 2 267
After that i just waited for maintenance window for db and did alter system flush shared_pool to get that sql from shared_pool.
Can any one tell me what i'm doing wrong, why i can't purge only one SQL statemnt?
Thanks
Sorry for such ugly post, can't find better way to copy/paste it here.
P.S. mine db Oracle Database 11g Release 11.1.0.7.0 - 64bit (SE) under Linux with PSU 11.1.0.7.8 applied.
P.P.S. this is not "Bug 11829677 - Child cursors with is_shareable=n are not purged by dbms_shared_pool.purge (Doc ID 11829677.8)"
SQL> select SQL_ID,IS_SHAREABLE from v$sql where sql_id ='93mfpb7a8fa20';
SQL_ID IS_S
*93mfpb7a8fa20 Y*