DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 (Doc ID 751876.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.




Goal

This article is intended for DBAs who manually want to purge library cache heaps on a 10.2.0.4 database.

Session 1
===========

conn scott/tiger

SQL> select * from dept where deptno=10;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK



Session 2
===========

conn / as sysdba

SQL> select address, hash_value, executions, loads, version_count,
invalidations, parse_calls
from v$sqlarea where sql_text = 'select * from dept where deptno=10';

ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS
-------- ---------- ---------- ---------- ------------- -------------
PARSE_CALLS
-----------
28F1FCB4 911274289 1 1 1 0
1

SQL> exec dbms_shared_pool.purge ('28F1FCB4,911274289','C');

PL/SQL procedure successfully completed.


SQL> select address, hash_value, executions, loads, version_count,
invalidations, parse_calls
from v$sqlarea where sql_text = 'select * from dept where deptno=10';

ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS
-------- ---------- ---------- ---------- ------------- -------------
PARSE_CALLS
-----------
28F1FCB4 911274289 1 1 1 0
1


====== Here no rows should have been selected.
.
.
Again go to session 1

.
.
SQL> select * from dept where deptno=10;
.
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK

Now go to session 2
===================

ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS
-------- ---------- ---------- ---------- ------------- -------------
PARSE_CALLS
-----------
28F1FCB4 911274289 2 1 1 0
2

Here, you can see the DBMS_SHARED_POOL.PURGE is not working as expected.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms