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

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


  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

  7. […] table. Of impetus we would wish to select a list that has minimal impact on other queries. See also this page for some-more information and a illustration […]

    Pingback by Is there a way to make Oracle recalculate a query plan for each query invocation? | Yunt Answers — December 12, 2014 @ 3:20 am

  8. I really enjoy reading on this site, it holds good content.

    Comment by sdorttuii plmnr — June 28, 2015 @ 6:35 pm

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

Create a free website or blog at

%d bloggers like this: