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

Breadcrumb

Announcement

Forty years

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.

Question and Answer

Connor McDonald

Thanks for the question, Manas.

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

Answered by: Connor McDonald - Last updated: November 05, 2019 - 4:12 am UTC

Category: SQL - Version: 12.2.0.1

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Tip for excellent presentations: don't be afraid to appear ridiculous!

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.

and you rated our response

  (2 ratings)

Reviews

Reparse a query

November 01, 2019 - 6:01 am UTC

Reviewer: A reader

 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

Followup  

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...

November 01, 2019 - 12:16 pm UTC

Reviewer: J. Laurindo Chiappa from Sao Paulo, SP Brazil

.... 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