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

_____________________________________________________________________________________________________________________

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.

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

_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

acehints.com Copyright 2011-19 All Rights Reserved | Site Map | Contact | Disclaimer | Google