How to invalidate execution plan

When you are using bind variables for you sqls sometimes you can be caught to bind variable peeking and your queries should start to work with an unwanted or wrong execution plan. To solve this problem you can execute a simple ddl statement over any table of the query. When you issue a simple “grant select table to user” command your plan will become invalidated. You have to be careful on high loaded system when you are invalidating a plan. Try to choose a table which will have minimal effect on other running and already parsed queries.

Code Listing 3

Update 20/04/2009
Better way explained by Harald van Breederode here

http://prutser.wordpress.com/2009/04/19/flushing-a-cursor-out-of-the-library-cache/

Update 12/12/2009
Automate via scripting by Kerry Osborne

http://kerryosborne.oracle-guy.com/2008/09/flush-a-single-sql-statement/

Advertisements