How to clear an execution plan for a SQL

If encountering a binding snooping causes a slow execution schedule, want to clear a SQL execution plan, let it hard parse, for a long time did not find a direct operation share pool method (unless alter system flush Shared_pool), SQL can only be hardened by parsing the table DDL. Now finally found, using Sys.dbms_shared_pool.purge

Can be used directly at 11g, but on 10g requires alter session set events ' 5614566 Trace name Context forever '

--Specific Cursor object

SELECT ADDRESS, Hash_value, executions, parse_calls
From V$sql
WHERE sql_id = ' 7q9wc51p34my2 ';

00000007a4e9e040 1781682114 289583 289583

ALTER SESSION SET EVENTS ' 5614566 Trace name context forever ';

BEGIN
Dbms_shared_pool.purge (' 00000007a4e9e040,1781682114 ', ' C ');
END;
/

ALTER SESSION SET EVENTS ' 5614566 trace name context off ';

--Update the statistics of the table to invalidate the related cursor object
BEGIN
Dbms_stats. Gather_table_stats (ownname = ' username ',
tabname = ' tabname ',
estimate_percent = 100,
Method_opt = ' For all columns size skewonly ',
CASCADE = TRUE,
No_invalidate = FALSE); --Invalidates the relevant cursor

END;
/

For SQL Server

More than R2 versions can use the DBCC command to clear the specified schedule directly: DBCC FREEPROCCACHE (plan_handle);

2005 relatively cumbersome, using system stored procedures sp_create_plan_guide and Sp_control_plan_guide processing

How to clear an execution plan for a SQL