Toad World Blog

Toad Explain Plan Tip - returning actual SQL execution explain plan

Dec 13, 2016 3:08:00 PM by Dan Hotka

If you have been in my tuning class or read my tuning blogs, I carry on at length about the importance of reviewing the explain plan with which the SQL executed, not an explain plan because you needed to have one.

The explain plan button on Toad’s main editor screen is just that: an explain plan because you need to have one, and is not the one it executed with.

3755.Hotka actual explain plan with red circle.JPG-640x480

It is, however, easy for Toad to return the actual explain plan with which the SQL executed.

1207.Hotka explain plan with red oval.JPG-640x480

Right click in the explain plan panel and select “Load cached plan if possible.” This will look up the most-recently-executed SQL’s explain plan in the library cache and use that as input to the explain plan panel.

I recommend always keeping this on, as it is the actual explain plan executed.

3364.Hotka actual explain plan fig 3.jpg-640x480

From this point forward, when you execute your SQL, the actual explain plan executed will be populated into the Explain Plan window.

Read my other Toad blogs on tracing, in which I covered how to run a SQL trace, directly from this SQL. That trace file contains the explain plan the SQL executed with along with other useful information.

Tags: Oracle

Dan Hotka

Written by Dan Hotka

Dan Hotka is an Author/Instructor/Expert/Oracle ACE Director Alumni who uses TOAD regularly in his web-based and on-line courses. He is well published with many books still available on Amazon or www.DanHotka.com. Dan is available for user groups and company educational events. He regularly speaks at user groups around the world. Check his website for course listings, references, and discount codes for his online courses based in Safari.

Dan's most recent book is Toad for Oracle Unleashed