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 1000+ times

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 we 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 to Explore

Performance

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