Explain Plans Can Be Deceiving

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
);
 
begin
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
     commit;
   end if;
end loop;
end;
/
 
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);
 
exec DBMS_STATS.GATHER_TABLE_STATS ( -
ownname => user, -
tabname => 'TEST_CASE', -
method_opt => 'FOR ALL INDEXED COLUMNS SIZE SKEWONLY' -
);

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;
 
VARIABLE v1 NUMBER
VARIABLE v2 NUMBER
 
define p1 = 1000
define p2 = 100000
 
BEGIN
:v1 := 1000;
:v2 := 100000;
END;
/
 
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