Oracle Hard Parsing: A Closer Look at Permutations

The prior article started a discussion on the hard parsing process.  It left off with the query transformation step.  Please read these articles in the proper order as they each build on the prior article.

The next step in the hard parse process is the permutations, as shown in the Figure below, taken from the Oracle Performance Tuning Guide.

I often refer to this step as the ‘brute force’ approach to finding the most performant SQL execution plan.

Figure 2. Example Oracle Explain Plan

Lets look at the above SQL.  In the explain plan at ID line 0, this is the top line of the explain plan and shows the overall ‘cost’ of this query at 11.  This is the cost number that the permutation process is trying to find the lowest number.

This is a non-partitioned query.  Those are handled a bit different.  These queries I call regular queries and the CBO goal here is to find the execution plan with the lowest overall cost.

The permutation process does many things:

1. Compares the current estimated explain plan over all cost number to the prior permutation overall cost number
   1. If LOWER than the prior…takes this explain plan as the one to beat
   2. If the same or higher, ignores it and goes to the next permutation
2. Permutation on single table SQL tries the various where clause items and index combinations…one per permutation…
3. Permutations on multiple table SQL tries the above step 2 but also tries the tables joined in different combinations
   1. the CBO joins up tables 2 at a time…taking the results of the inner step (see Hash Join at ID step 5 above) as input to the next join condition (see Hash Join at ID step 2 above).
4. There are 3 join types: Nested Loops, Hash Joins, and Merge Joins
   1. Each permutation tries all 3 join conditions.  Brute force.  The CBO does NOT know which one will produce a lower overall cost…so…it tries them all.

The Oracle Trace 10053, the CBO trace, shows the entire hard parse process.  We can look at the 10053 trace in a future article perhaps.

The number of permutations is roughly the number of tables in the WHERE clause (including any sub queries that were rewritten to joins by query transformation) TIMES the number of where clause predicates (table columns in the where clauses).

Once the CBO is satisfied it has tried enough permutations that it is content with the lowest overall cost has been determined, it then passes the results to the final hard parse step of row source generation that then produces the final execution plan.

This final execution plan is then visible by querying the V$SQL_AREA table using this syntax:

 Select * from table(DBMS_XPLAN.Display_Cursor(SQL_ID => <sql id assigned to the SQL>);

The method used to generate the explain plan shown in this article used SQL*Plus with ‘autotrace on explain’…and the SQL executed.  Autotrace will then show the explain plan produced by the hard parse process.

Continue reading this article series to learn more about Oracle explain plans.