JL Computer Consultancy

Explain Plan - The oldest bug in Oracle

January 1999


Yes it is a slightly pretentious title, but before you object too much can you tell me the difference between the two following execution plans, and is it obvious to you that one of them is wrong ? Both plans were produced by passing an SQL statement through the explain plan tool, then dumping out the contents of the plan_table using a query outlined by Oracle Corp.



    SELECT STATEMENT (choose)     Cost (8,2,84)
      HASH JOIN     Cost (8,2,84)
        TABLE ACCESS (analyzed)  INVOICES (full)  Cost (3,1,27)
        TABLE ACCESS (analyzed)  INVOICE_LINES (full)  Cost (4,9,189)

    SELECT STATEMENT (choose)     Cost (8,2,84)
      HASH JOIN     Cost (8,2,84)
        TABLE ACCESS (analyzed)  INVOICE_LINES (full)  Cost (4,9,189)
        TABLE ACCESS (analyzed)  INVOICES (full)  Cost (3,1,27)

If you are familiar with Hash Joins, you will know that the first table listed in the Hash Join is the one turned into a hash table (in memory if possible) and the second table is scanned and hashed into it. If the Costing statistics are accurate Oracle will choose the table which is supposed to produce the smaller data set as the first table .

Clearly then the two plans above are saying different things. The first statement appears to hash the INVOICES table, then feed the INVOICE_LINES table through the result, the second statement appears to hash the INVOICE_LINES table and feed the INVOICES table through the result..

The catch in this case is that the plan_table held exactly the same set of rows for both the print-outs shown. The problem is that there has been an error in the Oracle manuals that appeared with the original documentation of explain plan. In this example it is easy to see that the second plan is wrong, since it claims that the more expensive table (cost 4, rows 9, bytes 189) is the one being used first in the hash. (Okay, I admit it, I did rig the dice a little bit to make the output go wrong).

The query used to report on the contents of the plan_table is usually quoted as something like the following, which was copied from the Oracle 8.0 Tuning Reference manual, p.23-15 (see below) then modifed cosmetically for extra columns.


    SELECT 
               LPAD (’ ’,2*(LEVEL-1))||operation||’ ’
               ||options ||’ ’||object_name
               ||’ ’||DECODE(id, 0, ’Cost = ’||position) "Query Plan"
    FROM
                plan_table
    START WITH 
               id = 0 AND statement_id = ’Emp_Sal
    CONNECT BY PRIOR
               id = parent_id AND statement_id =’Emp_Sal’;

The error is the omission of the line 'order by id'. As it stands this query could take the 4 rows listed in the plans above and produce either version. The id is critical to getting the correct plan - in fact the whole connect by - start with bit exists simply to let you produce some pretty indentation from the level pseudo-column. (Strangely the manual points out on the previous page the problems and work-arounds of ordering when using connect by before offering this erroneous SQL for reporting on the plan table).

Fortunately the output from the query is usually correct despite the absence of the order by. However, if your strategy for the PLAN_TABLE is for multiple users to use the same table then the chance of an incorrect plan being produced goes up.

Unfortunately, there are cases when the query produces the wrong results even from a freshly truncated plan_table, and in these cases you are probably trying to analyze a complicated SQL statement, so it is not immediately obvious that something has gone wrong !!

Bonus Tip: In the days of Oracle 7.3 and partition views, execution plans can be very large. A simple query on a 100-table partition view will usually produce a plan of around 300 lines. The connect by can make it quite expensive to produce this scale of plan - consider creating an index on (id, parent_id) to speed things up.


Related Pages

Explain7 - My usual script for handling path analysis on oracle 7

Explain8 - My usual script for handling path analysis on oracle 8


Back to Main Index of Topics