RE: Execution plan changing
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <Christopher.Taylor2@xxxxxxxxxxxx>, <mschmitt@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 14 Sep 2012 08:41:38 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Christopher.Taylor2@xxxxxxxxxxxx
Sent: Friday, September 14, 2012 8:25 AM
To: mschmitt@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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;
/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
