Flush SQLID or SQL Statement, Object from Shared Pool - DBMS_SHARED_POOL.PURGE

advertisements

_____________________________________________________________________________________________________________________

Package Name: DBMS_SHARED_POOL.PURGE

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

Flush SQLID / Statement from Shared Pool. 
Syntax:-

EXEC sys.DBMS_SHARED_POOL.purge('ADDRESS,HASH_VALUE','C');
where 'C' flag to indicate cursor.
and address and hash_value from GV$sqlarea 

Example:- 

Find out address, hashvalue from gv$sqlarea. You will have connect to the respective instance and execute the purge package. In below example I am going flush highlighted one from the shared pool. Its address and hash_value '0000000066D9E250, 1720692145' respectively.

SQL>select inst_id,sql_id, address, hash_value,sql_text from gv$sqlarea
where sql_text like '%employee%'

   INST_ID SQL_ID	 ADDRESS	  HASH_VALUE
---------- ------------- ---------------- ----------
SQL_TEXT
---------------------------------------------------------------------
	 1 fk4ccjmb56d2w 00000000619D9430 3595777116
select inst_id,sql_id, address, hash_value,sql_text from gv$sqlarea where sql_text like '%employee%'

	 1 1yf9ymxm8zbdj 0000000066D9E250 1720692145
select * from employee

	 1 18rayjm47gpmy 00000000638AA4C0 3363296894
select sql_id, address, hash_value from gv$sqlarea where sql_text like '%employee%'

SQL> EXEC sys.DBMS_SHARED_POOL.purge('0000000066D9E250,1720692145','C');

PL/SQL procedure successfully completed.

advertisements

 

Particular SQLID has been removed from the shared POOL

SQL> select inst_id,sql_id, address, hash_value,sql_text from gv$sqlarea
where sql_text like '%employee%'  2  ;

   INST_ID SQL_ID	 ADDRESS	  HASH_VALUE
---------- ------------- ---------------- ----------
SQL_TEXT
------------------------------------------------------------------------
	 1 fk4ccjmb56d2w 00000000619D9430 3595777116
select inst_id,sql_id, address, hash_value,sql_text from gv$sqlarea where sql_text like '%employee%'

	 1 18rayjm47gpmy 00000000638AA4C0 3363296894
select sql_id, address, hash_value from gv$sqlarea where sql_text like '%employee%'

Purge Objects from Shared Pool. 
Syntax:- Procedures, Functions & Packages

EXEC sys.DBMS_SHARED_POOL.purge('OWNER.PROCEDURE', 'P');
EXEC sys.DBMS_SHARED_POOL.purge('OWNER.FUNCTON', 'P');
EXEC sys.DBMS_SHARED_POOL.purge('OWNER.PACKAGE', 'P');
Type
EXEC sys.DBMS_SHARED_POOL.purge('OWNER.TYPE', 'T');
Triggers
EXEC sys.DBMS_SHARED_POOL.purge('OWNER.TRIGGER', 'R');
Sequence
EXEC sys.DBMS_SHARED_POOL.purge('OWNER.SEQUENCE', 'Q');

Example:-

SQL> conn test/test
Connected.
SQL> create or replace function fun_test return  number is
begin
return 1;
end;
SQL> /

Function created.

SQL> select fun_test from dual; 

  FUN_TEST
----------
	 1

SQL> conn / as sysdba
Connected.
SQL> SET LINESIZE 150
COLUMN owner FORMAT A30
COLUMN namespace FORMAT A20
COLUMN type FORMAT A10
COLUMN name FORMAT A50
SQL> SELECT owner,
       namespace,
       type,
       name,
       sharable_mem
FROM   v$db_object_cache
where  owner='TEST'
and   name='FUN_TEST'
ORDER BY sharable_mem; 

OWNER			       NAMESPACE	    TYPE       NAME						  SHARABLE_MEM
------------------------------ -------------------- ---------- -------------------------------------------------- ------------
TEST			       TABLE/PROCEDURE	    FUNCTION   FUN_TEST 						 16192

SQL> EXEC sys.DBMS_SHARED_POOL.purge('TEST.FUN_TEST', 'P');

PL/SQL procedure successfully completed.

After purging the amount shared memory used is 0

SQL>  SELECT owner,
       namespace,
       type,
       name,
       sharable_mem
FROM   v$db_object_cache
where  owner='TEST'
and   name='FUN_TEST'
ORDER BY sharable_mem;  
OWNER			       NAMESPACE	    TYPE       NAME						  SHARABLE_MEM
------------------------------ -------------------- ---------- -----------------------
TEST			       TABLE/PROCEDURE	    FUNCTION   FUN_TEST 						     0

_____________________________________________________________________________________________________________________

Website Stats