An Oracle explain plan is very much like a Google map of directions to drive from point-A to point-B (in the old days, before the web, we used to go to a local AAA to get a “Trip Tik” map). However a map is a planned route – not an absolute. You could encounter an accident, construction or even weather that warrants a detour. Hence the map in reality is a plan and approximates your actual route. The same is true for an Oracle explain plan – the database optimizer tells you what it thinks it’s going to do. That’s why it’s called an explain plan – the key word being plan.

So when using Toad for Oracle’s editor you need to be aware of this and work accordingly. So look at Figure 1 below with four very similar queries. Note that pressing the “explain plan” toolbar button for queries with hints BERT1 through BERT3 all result in an explain plan using an index range scan, whereas BERT4 results in a full table scan. But we need to dig much deeper – because some of these are wrong. That’s not a bug with Toad, but rather how Oracle works – remember it’s a plan and not an absolute.


Figure 1: Proposed Explain Plans

The TEST_CASE table has one million rows and was created using the following SQL and PL/SQL script:

drop table test_case;
create table test_case (
col1   integer not null,
col2   integer not null,
col3   integer not null,
col4   integer not null,
col5   integer not null,
col6   integer not null
for i in 1 .. 1000000 loop
   insert into test_case values (i, mod(i,10), mod(i,100), mod(i,1000), mod(i,10000), mod(i,100000));
   if (mod(i,1000) = 0) then
   end if;
end loop;
create unique index test_case_i1 on test_case (col1);
create       index test_case_i2 on test_case (col2);
create       index test_case_i3 on test_case (col3);
create       index test_case_i4 on test_case (col4);
create       index test_case_i5 on test_case (col5);
create       index test_case_i6 on test_case (col6);
ownname => user, -
tabname => 'TEST_CASE', -

Note that COL6 is indexed and that there are lots of unique values – i.e. the index is fairly selective. Look at Figure 2 below, the index on COL6 has 100,000 unique values out of one million total rows resulting in a selectivity value of 10% or .10 (i.e. 100,000 / 1,000,000). As such ¾’s of the above, proposed explain plans favoring index usage seems to make total sense. Of course there is a problem not yet divulged, so we need to dig even deeper.


Figure 2: Index Selectivity

Look at the actual explain plan shown in Figure 3 below using Toad’s SGA Trace/Optimization screen (Box 1). I filtered to show just select statements by user “BERT” and beginning with the hint “/* BERT” (Box2). Now look at the results for the BERT3 query (Box3) – it’s not using the index as planned, but rather it did a full table scan! In fact queries BERT2 through BERT4 used full table scans – so the total opposite of what was projected. How did I run these queries and why did this happen?


Figure 3: Actual Explain Plans

Below is the SQL script that I used to run these queries in Toad Script Runner (i.e. TSR) and/or SQL*Plus. I wanted to run these queries outside Toad first for two reasons: first because many queries are written in Toad and then run somewhere else; second to factor Toad out of the equation (at least for now).

alter system flush shared_pool;
define p1 = 1000
define p2 = 100000
:v1 := 1000;
:v2 := 100000;
set term off
select /* BERT1 */ * from test_case where col6 = 1000;
select /* BERT2 */ * from test_case where col6 between &p1 and &p2;
select /* BERT3 */ * from test_case where col6 between :v1 and :v2;
select /* BERT4 */ * from test_case where col6 between 1000 and 100000;
set term on

The answer is rather simple. Queries BERT2 and BERT3 contains substitution and bind variables rather than hard coded values. So Toad passes the SQL over to the database optimizer as containing items not known until runtime. Hence the explain plans for them assume that since index is selective that it will be utilized. However when you run the script above the substitution and bind variables for queries BERT2 and BERT3 contain the same exact values as the hard coded values in query BERT4. So in fact the last three queries are identical at run time and rightly result in full table scans.

So now back to Toad for one last item – how do you see the actual explain plans rather than just the proposed ones? Look at Figure 4 below – I’ve set the option for explain plans to load/show the cached explain plan if it exists. So now Toad shows the actual plan and not the proposed one. Of course that means you need to run the query – which again is an Oracle issue and not a Toad one, as the database optimizer needs to see the SQL to decide what it actually is going to do. This feature debuted and was blogged about in Toad 11.0 back in September of 2011 – so it’s been around now for two years.


Figure 4: Toad Can Show Cached Explain Plan

About the Author

Bert Scalzo

Bert Scalzo is a guest-blogger for Quest and a renowned database expert, Oracle® ACE, author, database technology consultant, and formerly a member of Dell Software’s TOAD dev team. With three decades of Oracle® database experience to draw on, Bert’s webcasts garner high attendance and participation rates. His work history includes time at both Oracle Education and Oracle Consulting. Bert holds several Oracle Masters certifications and has an extensive academic background that includes a BS, MS and Ph.D. in computer science, as well as an MBA, and insurance industry designations. Bert is a highly sought-after speaker who has presented at numerous Oracle conferences and user groups, including OOW, ODTUG, IOUG, OAUG, RMOUG and many others. Bert enjoys sharing his vast knowledge on data modeling, database benchmarking, database tuning and optimization, "star schema" data warehouses, Linux® and VMware®. As a prolific writer, Bert has produced educational articles, papers and blogs for such well-respected publications as the Oracle Technology Network (OTN), Oracle Magazine, Oracle Informant, PC Week (eWeek), Dell Power Solutions Magazine, The LINUX Journal,, Oracle FAQ, Ask Toad and Toad World.

Start the discussion at