Flush A Single SQL Statement

http://kerryosborne.oracle-guy.com/2008/09/flush-a-single-sql-statement/



set verify off
set pagesize 999
col username format a13
col prog format a22
col sql_text format a41
col sid format 999
col child_number format 99999 heading CHILD
col ocategory format a10
col avg_etime format 9,999,999.99
col etime format 9,999,999.99

select sql_id, child_number, plan_hash_value plan_hash, executions execs, elapsed_time/1000000 etime,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, u.username,
sql_text
from v$sql s, dba_users u
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like '%from v$sql where sql_text like nvl(%'
and sql_id like nvl('&sql_id',sql_id)
and u.user_id = s.parsing_user_id
/  
DECLARE
  name varchar2(50);
  version varchar2(3);
BEGIN
  select regexp_replace(version,'\..*') into version from v$instance;
  if version = '10' then
    execute immediate 
      q'[alter session set events '5614566 trace name context forever']'; -- bug fix for 10.2.0.4 backport
  end if;
  select address||','||hash_value into name
  from v$sqlarea 
  where sql_id like '&sql_id';
  sys.dbms_shared_pool.purge(name,'C',1);
END;

/


Comments

Popular Posts