How Explain Plan can be deceived.
If you have visited my pages on the Explain Plan functionality in Oracle you will know easy it is to find out how Oracle is going to execute a given SQL statement. There are times, however, when the plan printed by the utility is clearly wrong for no apparent reason. In particular you may get a plan which claims to use a full tablescan when you know that there is an excellent index to use, or you may get a plan which claims to use an index when the actual query performance tells you that Oracle must be doing a tablescan. If you are familiar with the problem already, you might like to go straight to the test case.
In cases like these the fallback position is to run the query - in its production form - with SQL_TRACE=true, and examine the plan that is printed in the trace file (a feature that arrived some time in 7.3), or if that feature is not available then running with a 10053 trace to see the complete optimiser analysis.
Where you don't want to run an entire program with SQL_TRACE=true, or where you can't control the source, you might cut out the offending text and put it in a little PL/SQL wrapper which declares and sets any required bind variables, because the usual reason for the problem is the appearance of bind variables.
The first problem is that the cost based optimiser is not very good with bind variables as it limits the use of available statistics quite dramatically, consider the two statements:
select * from helpdesk_calls where completed = 'N';
select * from helpdesk_calls where completed = :b1;
The stats on the table and any indexes index may tell the optimiser that for every 'N', there are 10,000 'Y' so the first case looks like a good option for using an index, however the bind variable in the second case MIGHT hold an 'N', but it might hold a 'Y', so on average the better bet in the absence of hard values is to use a tablescan. (Do you remember Oracle telling us to use bind variables in our code so that it could be re-used in the shared pool ? Yet another example of two good ideas developed independently and making a mess when they meet, perhaps.)
The second problem is one that Explain Plan itself has with bind variables and this is the problem I will be demonstrating in this note - all bind variables are deemed to be character types. Of course the 3GL or PL/SQL program that executes the production SQL will have declared the variable type and when the code is running live the optimizer will use that declared type to evaluate the plan, but when you cut the code out and stick it into Explain Plan, it's type is always character. This results in type-coercion problems, as the test case below demonstrates.
Create a suitable table, with a character type column which contains number-like information, and index that column. You may need to modify this code to get a much larger table so that the CBO wants to use the index anyway.
create table test_conv
select to_char(rownum,'00000000') v1 from all_objects;
Create unique index on test_conv(v1);
A nice simple select statement with result - do check that this agrees with expectations - mind you, '3' and '00000003' aren't exactly the same thing, but the row returned is clearly 'appropriate'.
alter session set optimizer_goal=first_rows;
select v1 from test_conv where v1 = 3;
Because we are comparing a character column with a numeric constant, Oracle has to apply an implicit to_number() to the column, thus disabling the index. If you run this statement through the explain plan facility, then the path will correctly be reported as:
Id Par Pos Ins Plan
-- --- --- --- -------------------------------------------------------
0 3 SELECT STATEMENT (first_rows) Cost (3,6,162)
1 0 1 1 TABLE ACCESS SYSTEM TEST_CONV (full) Cost (3,6,162)
Imagine then that you had cut this statement from a C program, where there was actually a bind variable being used instead of the value 3 (change the '= 3' to '= :b1'): running the query through explain plan now gives you:
Id Par Pos Ins Plan
-- --- --- --- ----------------------------------------------------------
0 2 SELECT STATEMENT (first_rows) Cost (2,1,27)
1 0 1 1 TABLE ACCESS SYSTEM TEST_CONV (by rowid) Cost (2,1,27)
2 1 1 INDEX UNIQUE SYSTEM TEST_CONV_I (unique scan)
So there you are, one happy bunny having proved that this bit of SQL isn't causing a problem because it is clearly using a highly appropriate index and can't be the bit that is slowing down your application, so off you go trying to find some other reason for the problem. (Of course in real life you would have been using my utility to check the v$sqlarea to see where the work was being done, so you wouldn't really be fooled)
The moral of the story: be very cautious when checking SQL with bind variables. When you cut a piece of SQL out of an application, make sure you modify it to give any embedded bind variables an explict type conversion to their declared 3GL (or PL/SQL) type before running the code through explain plan, or you may be deceiving yourself.
P.S. I'm sure I remember one version of Oracle where an alternative approach to this problem was to use the sqlplus variable declaration to pre-declare the bind variables with the right type (variable b1 number) but it certainly doesn't work on any of the versions that I am currently running.