— fsp.sql
rem ———————————————————-
rem Created by Navneet Upneja
rem
rem fsp.sql (Have to be executed as SYS User)
rem
rem Purpose: Flush Shared Pool just for 1 SQL ID in connected Instance
rem analyzed
rem
rem ————————————————————-set define on
define Enter_SQL_ID = ‘&1’;
declare
v_sql_id varchar2(20) := ‘&Enter_SQL_ID’;
begin
dbms_output.put_line(‘&1′);
for i in (select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID =v_sql_id)
loop
DBMS_SHARED_POOL.PURGE (i.address||’,’||i.hash_value,’C’);
end loop;
end;
/undefine Enter_SQL_ID
Syntex to use this script is to provide SQL_ID as input to the sql script.
For Example
SQL>@fsp 3b07b0t0xv4c5
PL/SQL procedure successfully completed.
In case of RAC environment, script has to be run on all the nodes where particular SQL statement has been previously executed and are still part of shared pool.
This approach is helpful in the scenarios where you have multiple Execution plans for a single statement and you want to flush this SQL from shared pool and get it parsed again.