RE: Execution plan changing

From: <Christopher.Taylor2_at_parallon.net>
Date: Fri, 14 Sep 2012 08:41:38 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E885158F42B5_at_NADCWPMSGCMS10.hca.corpad.net>

Ack! I just realized I didn't give Kerry Osborne due credit for his script in my earlier email. My apologies - didn't mean to plagiarize there...

Check out Kerry Osborne's blog for dbms_pool purge and you can see how he uses it. I modded it for my own purposes.

Chris

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christopher.Taylor2_at_parallon.net Sent: Friday, September 14, 2012 8:25 AM To: mschmitt_at_uchicago.edu; oracle-l_at_freelists.org Subject: RE: Execution plan changing

---snip

Run this to just flush your sql and objects from the pool (generates a new plan everytime)

DECLARE
 name varchar2(50);
 version varchar2(3);
cursor c1 is
select /*+ ALL_ROWS */ address||','||hash_value as name from v$sqlarea where upper(sql_text) like 'SELECT%QB_NAME%OUTER%' -- Put your SQL_TEXT here that you want to flush, use % for spaces to get better matches and upper(sql_text) not like '%V$SQLAREA%' and parsing_schema_name = '&username'; -- Put your username here rec_c1 c1%rowtype;
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;

for rec_C1 in c1 loop
begin
dbms_output.put_line('Name = '||rec_c1.name); sys.dbms_shared_pool.purge(rec_c1.name,'C',1); end;
end loop;
END;
/

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Sep 14 2012 - 08:41:38 CDT