DBA Hub

📋Steps in this guide1/4

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.

oracle miscconfigurationintermediate
by OracleDba
49 views
1

Flush the Shared Pool

To clear the whole shared pool you would issue the following command from a privileged user. 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.

Code/Command (click line numbers to comment):

1
ALTER SYSTEM FLUSH SHARED_POOL;
2

Purge Individual Code Objects

From 11g Release 1 the package has included a procedure to remove code objects from the shared pool. This can be used to purge a number of named objects. The 'p' or 'P' flag is used to purge procedures, functions and packages. The 't' or 'T' flag is used to purge types. The 'r' or 'R' flag is used to purge triggers. The 'q' or 'Q' flag is used to purge sequences. There are some undocumented Java flags, which I have never used, but allegedly exist. - 'jc' or 'JC' : Java Class - 'jr' or 'JR' : Java Resource - 'js' or 'JS' : Java Source - 'jd' or 'JD' : Java Data You can identify objects in the library cache by querying the view.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
PROCEDURE PURGE
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
NAME                           VARCHAR2                IN
FLAG                           CHAR                    IN     DEFAULT
HEAPS                          NUMBER                  IN     DEFAULT

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');

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

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

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

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;
3

Purge Individual Cursors

The procedure can also be used to remove individual cursors from the shared pool. To do this the parameter should be specified in the format of , using the values from the 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. We might try to identify a specific cursor with a query like the following. We can then feed the and values into the call described above for the resulting statement or statements.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
EXEC sys.DBMS_SHARED_POOL.purge('000000010182AE70,1862304678', 'C');

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

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. The column was added to the view, which we will need for one of the overloads. Let's assume we spot some things of interest using the following query. We need to determine the namespace number using the following query. We can then combine the hash value with the namespace number, as shown below. Alternatively we can use the object name to purge it using the other overload. For more information see: - DBMS_SHARED_POOL Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
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

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;

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.

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

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!