Purge cursors given an object or schema

User51642 Yong Huang

Purging library cache objects can be done by flushing the whole shared pool or using dbms_shared_pool.purge to purge a specific cursor. But often times, a flush with an intermediate granularity is desired so as to free up some shared pool memory with less impact to the running database. Use cases are e.g. dealing with an application that creates lots of literal cursors, or just temporarily so. It would be nice for dbms_shared_pool.purge to purge a group of cursors (1) given an object name to purge all SQLs referencing that object, or (2) given a schema name to purge all SQLs parsed by that schema. The overloaded form of the purge procedure

PROCEDURE PURGE

Argument Name                  Type                    In/Out Default?

------------------------------ ----------------------- ------ --------

SCHEMA                         VARCHAR2                IN

OBJNAME                        VARCHAR2                IN

NAMESPACE                      NUMBER                  IN

HEAPS                          NUMBER                  IN

appears to do (1). But it's not working.

In Oracle 10g or lower, a DDL on an object purges all the SQLs referencing this object. In 11g or higher, a DDL on it invalidates the SQLs referencing the object but the SQLs remain in library cache, i.e., not being purged. This behavior is fine as long as dbms_shared_pool.purge can be enhanced.

Sven W.

And what excatly is your suggested idea for improvement?

jgarry

Also, remember this is a shared system, and things won't be purged while they are still in use.  When checking to see if things are in the library cache, be sure to keep that in mind.  They're invalidated for the next use.  As well, since not everything gets cleaned out right away because of this, fragmentation can happen, and being more specific about what group to purge could conceivably make that worse when the associated objects are scattered about.

Not a bad idea, but could have complex consequences, especially with complicated dependencies.

User51642 Yong Huang

And what excatly is your suggested idea for improvement?

The suggested improvement is making the existing overloaded purge procedure that takes schema and objname work, and either adding one more procedure that only takes schema, or allowing the existing one to make objname nullable (so effectively the purge is done to the whole schema). Alternatively, provide a new SQL command e.g. alter system flush shared_pool {for schema xxx | for object xxx}.