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/

About these ads

6 Comments »

  1. Nice trick… I have verified that this works on 10.2 RAC using your test case. Thanks for sharing.

    Comment by John — July 13, 2007 @ 5:42 pm

  2. olm, her google aramamda seni bulmak zorunda miyim? :))

    Comment by Tonguc — April 22, 2009 @ 11:35 am

  3. Aratacagina maille sorsan buralara gelmene gerek kalmaz :)

    Comment by coskan — April 22, 2009 @ 11:40 am

  4. it is a good idea to look for book reviews first before buying an expensive book ~

    Comment by Anxiety Depression : — October 31, 2010 @ 12:50 pm

  5. Data Transformation
    superb post. Ne’er knew this, appreciate it for letting me know Data Transformation

    Comment by Data Transformation — January 11, 2012 @ 1:22 pm

  6. Thank for the explanation great help for clearing the basic concept

    Comment by Adnan — December 4, 2012 @ 9:09 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

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 203 other followers

%d bloggers like this: