Understanding DBMS_SHARED_POOL Package

The DBMS_SHARED_POOL package provides access to the shared pool, which is the shared memory area where cursors and PL/SQL objects are stored.

SUBPROGRAMS

KEEP :::

This procedure keeps an object in the shared pool. Once an object has been kept in the shared pool, it is not subject to aging out of the pool. This may be useful for frequently used large objects. When large objects are brought into the shared pool, several objects may need to be aged out to create a contiguous area large enough.

UNKEEP :::

This procedure unkeeps the named object.

MARKHOT :::

This procedure marks a library cache object as a hot object.

UNMARKHOT :::

This procedure unmarks a library cache object as a hot object.

SIZES :::

This procedure shows objects in the shared_pool that are larger than the specified size.

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

SQL> SHOW USER
USER is "SYS"

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

--It's a really bad 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.

--To identify objects in the library cache, query the V$DB_OBJECT_CACHE view.

SQL> SELECT COUNT(*) FROM v$db_object_cache WHERE owner = 'COURSE' ORDER BY sharable_mem;

  COUNT(*)
----------
         0

From 11g Release 1 the DBMS_SHARED_POOL package has included a PURGE 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.

SYS.DBMS_SHARED_POOL.PURGE(‘.’, ‘P’);
SYS.DBMS_SHARED_POOL.PURGE(‘.’, ‘P’);
SYS.DBMS_SHARED_POOL.PURGE(‘.’, ‘P’);

The ‘t’ or ‘T’ flag is used to purge types.

SYS.DBMS_SHARED_POOL.PURGE(‘.’, ‘T’);

The ‘r’ or ‘R’ flag is used to purge triggers.

SYS.DBMS_SHARED_POOL.PURGE(‘.’, ‘R’);

The ‘q’ or ‘Q’ flag is used to purge sequences.

SYS.DBMS_SHARED_POOL.PURGE(‘.’, ‘Q’);

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 ‘c’ or ‘C’ flag to indicate cursor.The flag doesn’t actually exist,but it feels appropriate.

SQL> SHOW USER
USER is "SYS"

SQL> SELECT empno FROM course.emp WHERE job = 'ANALYST';

     EMPNO
----------
      7788
      7902

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

SQL_ID        ADDRESS          HASH_VALUE   SQL_TEXT
------------- ---------------- ------------ --------------------------------------------------
23gf0v84wy321 00007FF7BBE9C7A0  164564033  SELECT empno FROM course.emp WHERE job = 'ANALYST'

SQL> EXEC SYS.DBMS_SHARED_POOL.PURGE('00007FF7BBE9C7A0,164564033','C');

PL/SQL procedure successfully completed.

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

no rows selected

The KEEP & UNKEEP procedure can be used to pin or unpin certain SQL statements/cursors along with DB objects in SGA as shown below :-

SQL> SHOW USER
USER is "SYS"

SQL> SELECT 'Example TEST' FROM dual;

'EXAMPLETEST
------------
Example TEST

SQL> SELECT SUBSTR(sql_text,1,15) text,address,hash_value,kept_versions FROM v$sql WHERE sql_text LIKE '%Example%';

TEXT                ADDRESS          HASH_VALUE KEPT_VERSIONS
----------------- ------------------ ----------- ---------------
SELECT 'Example   00007FF7D2FED748    2929179549     0

--KEPT_VERSIONS tell that query is in keep shared pool or not.
SQL> EXEC DBMS_SHARED_POOL.KEEP('00007FF7D2FED748,2929179549','C');

PL/SQL procedure successfully completed.

SQL> SELECT SUBSTR(sql_text,1,15) text,address,hash_value,kept_versions FROM v$sql WHERE sql_text LIKE '%Example%';

TEXT                ADDRESS          HASH_VALUE KEPT_VERSIONS
----------------- ------------------ ----------- ---------------
SELECT 'Example   00007FF7D2FED748    2929179549     65

SQL> EXEC DBMS_SHARED_POOL.UNKEEP('00007FF7D2FED748,2929179549','C');

PL/SQL procedure successfully completed.

SQL> SELECT SUBSTR(sql_text,1,15) text,address,hash_value,kept_versions FROM v$sql WHERE sql_text LIKE '%Example%';

TEXT                ADDRESS          HASH_VALUE KEPT_VERSIONS
----------------- ------------------ ----------- ---------------
SELECT 'Example   00007FF7D2FED748    2929179549     0

--PIN PROCEDURE,FUNCTION AND PACKAGE
EXEC DBMS_SHARED_POOL.KEEP('', 'P')
-- PIN TRIGGERS
EXEC DBMS_SHARED_POOL.KEEP('', 'R')
--PIN SEQUENCE
EXEC DBMS_SHARED_POOL.KEEP('', 'Q')
--PIN TYPE
EXEC DBMS_SHARED_POOL.KEEP('', 'T')
--PIN CURSOR
EXEC DBMS_SHARED_POOL.KEEP('', 'C')

--UNPIN PROCEDURE,FUNCTION AND PACKAGE
EXEC DBMS_SHARED_POOL.UNKEEP('', 'P')
-- UNPIN TRIGGERS
EXEC DBMS_SHARED_POOL.UNKEEP('', 'R')
--UNPIN SEQUENCE
EXEC DBMS_SHARED_POOL.UNKEEP('', 'Q')
--UNPIN TYPE
EXEC DBMS_SHARED_POOL.UNKEEP('', 'T')
--UNPIN CURSOR
EXEC DBMS_SHARED_POOL.UNKEEP('', 'C')