Skip to Main Content
  • Questions

  • How to change the plan of a query in execution??

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Manas.

Asked: October 30, 2019 - 5:51 am UTC

Last updated: December 17, 2020 - 4:42 am UTC

Version: 12.2.0.1

Viewed 10K+ times! This question is

You Asked

So I often face an issue when a query generates an execution plan assuming wrong carnality and since it assumes it as 1 it goes into a M3RG3 CART3SIAN join. Now if I gather the stats the query does not automatically picks up the new plan. Is there a way to force the query to change its execution plan mid-way?

Also sometime it automatically picks up the new plan and executes it correctly.

So I have not been able to identify the discrepancy in behavior.

Best Regards!!

and Connor said...

Is there a way to force the query to change its execution plan mid-way?


Check out Kerry's post on purging a statement from the shared pool to force a reparse.

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

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Reparse a query

A reader, November 01, 2019 - 6:01 am UTC 

 Is there a way to force the query to change its execution plan mid-way >> Don't think that is possible

While gathering stats you can also use NO_INVALIDATE=>FALSE (that is TRUE by default)

Cheers

Connor McDonald
November 05, 2019 - 4:12 am UTC

True, but you then do run the risk of creating a parse storm (dependent on your application)

Another possibility...

J. Laurindo Chiappa, November 01, 2019 - 12:16 pm UTC

.... could be Adaptive Plans , see https://www.orafaq.com/node/2883 : it´s not perfect yet, but in some cases it could work....

Regards,

J. Laurindo Chiappa

Adaptive plans may work

Divines, December 15, 2020 - 5:20 pm UTC

Adaptive plans do work sometime, which is a very good feature. I am looking at something with which I can force a query to be run again fresh without killing the existing session and running the process again.

Say stats were missing, query went slow ... I gathered stats but do not want to restart the process. Just that Oracle creates an execution plan again and runs it again(which would be hopefully better than the one with no or bad stats).

Best regards!!
Connor McDonald
December 17, 2020 - 4:42 am UTC

Same - use DBMS_SHARED_POOL to purge that cursor.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.