Bu Blogda Ara

13 Eylül 2012 Perşembe

Purge SQL from shared pool


  • Find SQL_ID which one you want to purge SQL.

SELECT   a.cursors, a.sql_id, b.sql_text

  FROM   (  SELECT   COUNT ( * ) AS cursors, ssc.sql_id
              FROM   gv$sql_shared_cursor ssc
          GROUP BY   ssc.sql_id
          ORDER BY   cursors DESC) a, (SELECT   sa.sql_id, sa.sql_text
                                         FROM   gv$sqlarea sa) b
 WHERE   a.sql_id = b.sql_id AND a.cursors > 10;


  • Purge related SQL from shared_pool.
DECLARE
   SQ_ADD        VARCHAR2 (100) := '';
   SQ_HASH_VAL   VARCHAR2 (100) := '';
BEGIN
   EXECUTE IMMEDIATE 'select address,hash_value from v$sqlarea where sql_id=''fg9ymndtmypa8'''
      INTO   SQ_ADD, SQ_HASH_VAL;
   DBMS_SHARED_POOL.purge (SQ_ADD || ',' || SQ_HASH_VAL, 'C');
--dbms_output.put_line(SQ_ADD||','||SQ_HASH_VAL);
END;

Hiç yorum yok:

Yorum Gönderme