8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Purge the Shared Pool (DBMS_SHARED_POOL and ALTER SYSTEM FLUSH SHARED_POOL)

This article describes how to use the the DBMS_SHARED_POOL package and the ALTER SYSTEM FLUSH SHARED_POOL statement to remove one or more objects from the shared pool.

Flush the Shared Pool

To clear the whole shared pool you would issue the following command from a privileged user.

ALTER SYSTEM FLUSH SHARED_POOL;

It's a really brutal thing to do as all parsed SQL will be thrown away. The database will have to do a lot of work to warm up the shared pool again with commonly used statements. You should probably avoid doing this if possible.

Purge Individual Code Objects

From 11g Release 1 the DBMS_SHARED_POOL package has included a PURGE procedure to remove code objects from the shared pool.

PROCEDURE PURGE
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
NAME                           VARCHAR2                IN
FLAG                           CHAR                    IN     DEFAULT
HEAPS                          NUMBER                  IN     DEFAULT

This can be used to purge a number of named objects.

The 'p' or 'P' flag is used to purge procedures, functions and packages.

EXEC sys.DBMS_SHARED_POOL.purge('MY_SCHEMA.MY_PROCEDURE', 'P');
EXEC sys.DBMS_SHARED_POOL.purge('MY_SCHEMA.MY_FUNCTON', 'P');
EXEC sys.DBMS_SHARED_POOL.purge('MY_SCHEMA.MY_PACKAGE', 'P');

The 't' or 'T' flag is used to purge types.

EXEC sys.DBMS_SHARED_POOL.purge('MY_SCHEMA.MY_TYPE', 'T');

The 'r' or 'R' flag is used to purge triggers.

EXEC sys.DBMS_SHARED_POOL.purge('MY_SCHEMA.MY_TRIGGER', 'R');

The 'q' or 'Q' flag is used to purge sequences.

EXEC sys.DBMS_SHARED_POOL.purge('MY_SCHEMA.MY_SEQUENCE', 'Q');

There are some undocumented Java flags, which I have never used, but allegedly exist.

You can identify objects in the library cache by querying the V$DB_OBJECT_CACHE view.

SET LINESIZE 150
COLUMN owner FORMAT A30
COLUMN namespace FORMAT A20
COLUMN type FORMAT A10
COLUMN name FORMAT A50

SELECT owner,
       namespace,
       type,
       name,
       sharable_mem
FROM   v$db_object_cache
ORDER BY sharable_mem;

Purge Individual Cursors

The PURGE procedure can also be used to remove individual cursors from the shared pool. To do this the NAME parameter should be specified in the format of 'ADDRESS,HASH_VALUE', using the values from the V$SQLAREA view, and the flag should be anything other than those flags listed previously. You will often see people using the 'c' or 'C' flag to indicate cursor. The flag doesn't actually exist, but it feels appropriate.

EXEC sys.DBMS_SHARED_POOL.purge('000000010182AE70,1862304678', 'C');

We might try to identify a specific cursor with a query like the following.

SELECT sql_id,
       address,
       hash_value,
       sql_text
FROM   v$sqlarea
WHERE  sql_text LIKE 'SELECT empno FROM emp WHERE job%';

We can then feed the ADDRESS and HASH_VALUE values into the PURGE call described above for the resulting statement or statements.

Oracle 11.2 Updates

In 11g Release 2 overloads were added to allow the removal of objects and libraries from the shared pool in a slightly different manner. I can't remember having a need for these, but they are there if you need them.

PROCEDURE PURGE
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
SCHEMA                         VARCHAR2                IN
OBJNAME                        VARCHAR2                IN
NAMESPACE                      NUMBER                  IN
HEAPS                          NUMBER                  IN
EDITION_NAME                   VARCHAR2                IN     DEFAULT

PROCEDURE PURGE
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
HASH                           VARCHAR2                IN
NAMESPACE                      NUMBER                  IN
HEAPS                          NUMBER                  IN

The FULL_HASH_VALUE column was added to the V$DB_OBJECT_CACHE view, which we will need for one of the overloads.

Let's assume we spot some things of interest using the following query.

SET LINESIZE 150
COLUMN owner FORMAT A30
COLUMN namespace FORMAT A20
COLUMN type FORMAT A10
COLUMN name FORMAT A50

SELECT owner,
       namespace,
       type,
       name,
       sharable_mem,
       full_hash_value
FROM   v$db_object_cache
WHERE  type = 'SEQUENCE'
ORDER BY sharable_mem;

We need to determine the namespace number using the following query.

SET LINESIZE 150
COLUMN namespace_text FORMAT A50
COLUMN type_text FORMAT A50

SELECT a.kglstidn AS namespace_no,
       a.kglstdsc AS namespace_text,
       b.kglstdsc AS type_text
FROM   (SELECT kglstdsc, kglstidn FROM x$kglst WHERE kglsttyp = 'NAMESPACE') a,
       (SELECT kglstdsc, kglstidn FROM x$kglst WHERE kglsttyp = 'TYPE') b
WHERE  a.kglstidn = b.kglstidn
ORDER BY 1;

NAMESPACE_NO NAMESPACE_TEXT                                     TYPE_TEXT
------------ -------------------------------------------------- --------------------------------------------------
           0 SQL AREA                                           CURSOR
           1 TABLE/PROCEDURE                                    INDEX
           2 BODY                                               TABLE
           3 TRIGGER                                            CLUSTER
           4 INDEX                                              VIEW
           5 CLUSTER                                            SYNONYM
           6 KGL TESTING                                        SEQUENCE
           7 PIPE                                               PROCEDURE
           8 LOB                                                FUNCTION
           9 DIRECTORY                                          PACKAGE
          10 QUEUE                                              NON-EXISTENT
... Edited for brevity.

We can then combine the hash value with the namespace number, as shown below.

EXEC sys.DBMS_SHARED_POOL.purge('41f2d698b35a49804f10c13b33beb0f0', 5, 65);

Alternatively we can use the object name to purge it using the other overload.

EXEC sys.DBMS_SHARED_POOL.purge('MY_USER', 'MY_SEQUENCE', 5, 65);

For more information see:

Hope this helps. Regards Tim...

Back to the Top.