Toad “Explains” It Best

Bert Scalzo — Wed, 14 Jan 2009 15:31:00 GMT

Oracle explain plans – they are considered by many as critical when optimizing or tuning SQL statements (I however prefer to look at trace data in conjunction with explain plans). For those who prefer primarily to rely on explain plan interpretation, comprehension and improvement – Toad offers numerous capabilities to make the entire process simpler and more productive. Because let’s face it, explain plans are like much proofs in mathematics. Some people are naturally good at proofs, while the rest of us struggle with them. That’s where Toad comes in – Toad makes explain plan work trivial. In this blog I’ll just focus on how the base edition of Toad can help. For even more, Toad offers the SQL Optimizer add-on product that’s the key enabling component of the Toad for Oracle Xpert Edition.

Let’s start with a fairly simple query that’s based off the SCOTT/TIGER demo tables, specifically the EMP table. So, we have a single table query – that should not be too tough, right? Look at the code below – it contains both “AND” and “OR” operations, two similar non-correlated sub-queries, and one correlated sub-query. The explain plan should not therefore be trivial. But Toad can make working with it just that. J

select *
from emp aaa
where
( active = 'Y'
 and job <> 'PRESIDENT'
)
and

(
 (
 sal+nvl(comm,0) > ( select avg(sal+nvl(comm,0))
                      from emp 
                      where active = 'Y'
                        and job <> 'PRESIDENT'
                        and hiredate < sysdate-90)
 and
 sysdate-hiredate < ( select avg(sysdate-hiredate)
                       from emp
                      where active = 'Y'
                        and job <> 'PRESIDENT'
                        and hiredate < sysdate-90)
 )
or
 (
  sal+nvl(comm,0) > ( select sal+nvl(comm,0)
                      from emp bbb
                      where bbb.active = 'Y'
                        and bbb.job <> 'PRESIDENT'
                        and bbb.empno = aaa.mgr)
 )
);

So let’s look at the query in the Toad Editor – and what we see by default when we press the ambulance toolbar icon to generate the explain plan for the editor content. Most users know about or have done this numerous times – but please read on, because there’s more!

OK – the cost of just 14 looks good (for those who focus on cost – which may not always be the best metric in isolation). But look at that tree-view of the explain plan statements. So has Toad offered anything unique or special (so far)? No (look at the same function performed in Oracle’s (SQL Developer below). Yes, Toad also displays the explain plan’s step numbers in the order to read them – but that’s still not too easy to read in this “tree-view” format.

So just how should one read the explain plan – and is there an easier/better way? Of course the answer is an emphatic “yes” – and Toad makes explain plan usage both the easiest and best possible experience, if you know where to look. So let’s dig deeper…

First, let’s examine how one should read the explain plan for either of the prior product’s screen snapshots. Look for the most indented operations. They are performed first. Then for those that are at the same level. They execute in their hierarchical order. Another way to say that is to read the explain plan backwards using a “rightmost uppermost” order. Of course that was somewhat easier to read in Toad, because it displayed the line numbers.

But that’s just too difficult and too much work for this lazy guy. What I really want is a graphical display that communicates all that information without the line numbers even being really needed – i.e. my brain “just sees it correctly”. There has to be an easier way. Of course there is – it’s called Toad. Simply “right-mouse” click anywhere on your Toad explain plan area to display the following options:

The “Adjust Content” choice permits you to pick what output Toad will display in your explain plan – such as which “explain plan table” columns to display or not. But it’s the “Display Mode” options that are going to make your day – because both the “Graphic” and “MS Graphic” options make reading explain plans a snap. And for those of you who also do Microsoft SQL Server development, the “MS Graphic” option means the exact same display style of execution plans as Microsoft SQL Server does! Here they are:

I personally find both these styles much easier to read, and thus I can focus on making the query better – rather than spending far too much energy just reading them. Thanks Toad!

Bert Scalzo's Blog

Toad “Explains” It Best