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.
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/
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
olm, her google aramamda seni bulmak zorunda miyim? :))
Comment by Tonguc — April 22, 2009 @ 11:35 am
Aratacagina maille sorsan buralara gelmene gerek kalmaz :)
Comment by coskan — April 22, 2009 @ 11:40 am
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
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
Thank for the explanation great help for clearing the basic concept
Comment by Adnan — December 4, 2012 @ 9:09 am
[…] 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