Coskan’s Approach to Oracle

March 1, 2007

How to invalidate execution plan

Filed under: How To — coskan @ 9:39 am

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/

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 199 other followers