Previously on the 10g database, if the binding snooping caused the execution plan is slow, want to clear the execution plan of a SQL, let it hard parsing, for a long time did not find a direct operation share pool method (total cannot 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 under 11g, but on 10g need alter session set events ' 5614566 Trace name Context forever '.
Sql> select * from V$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-production
CORE 11.2.0.1.0 Production
TNS for Linux:version 11.2.0.1.0-production
Nlsrtl Version 11.2.0.1.0-production
sql> drop table test purge;
Sql> CREATE TABLE Test as SELECT * from Dba_objects;
sql> exec dbms_stats.gather_table_stats (user, ' test ');
Sql> Select/*gg*/count (*) from test;
COUNT (*)
----------
79747
sql> Col sql_text format A35
sql> Col ADDRESS format A18
sql> Col hash_value format A10
Sql> Select S.sql_text, s.address, s.hash_value| | "
From V$sqlarea S
Where Sql_text like ' select/*gg*/count (*) from test% ';
Sql_text ADDRESS s.hash_value| | "
----------------------------------- ------------------ --------------------------------
Select/*gg*/count (*) from Test 0000000300b06d70 728448230
sql> exec sys.dbms_shared_pool.purge (' 0000000300b06d70,728448230 ', ' C ');
The PL/SQL process has completed successfully.
Sql> Select S.sql_text, s.address, s.hash_value| | "
From V$sqlarea S
Where Sql_text like ' select/*gg*/count (*) from test% ';
Row not selected
How Oracle 11g clears the execution plan for a SQL in share pool