Oracle Hard Parsing: A Closer Look

The prior article discussed the Oracle library cache, the difference between a hard parse and a soft parse, and a bit on bind variable processing as well.

To review, Oracle hard parsing reads in statistics, utilizes index information and creates an execution plan. Soft parsing already has the execution plan and doesn’t need to revisit the statisticsand so on. The illustration below (taken from the Oracle Performance Tuning Guide) shows the steps the CBO (cost-based optimizer) takes to arrive at an execution plan.

Figure 2. How Oracle Arrives at an Explain Plan

This is the hard parse process.  At the end of this discussion, you will see why Oracle always looks to see if the SQL is already in the library cache first! (soft parse).

The CBO runs three to four SQL statements of its own during the hard parse process.  The SQL submitted by Oracle are called Dictionary SQL or Recursive SQL.  I’ll go with dictionary SQL for the remainder of this series.

These dictionary SQL statements are used to:

1. Check the syntax of the table names and column names
2. Pull in statistics
3. Check permissions to all the objects involved
4. Save the where clause predicates for future statistic runs
   1. the future statistics runs will produce histograms on these where clause items…another useful statistic that helps the CBO when there is skewed data…

The part in the dashed lines happens for both soft parsing and hard parsing.  The semantic checking, the permissions, and the lookup in the library cache.

The first thing in the hard parse is to do the query transformation part.  This is where the CBO can do subtle and not so subtle rewrites of your SQL.  Query transformation came to be in Oracle9 but it really has been around like forever because Oracle has always added functions to where clause items where the data types don’t match (to make them match…).  This can show up with the SQL not using available indexes because indexes are not used when there are functions on where clause items.

The CBO likes to change sub queries to inline views (coding techniques) when the statistics tells the CBO that the  sub query might produce a larger number of rows.  Join conditions are better at handling larger volumes of rows (the inline view then becomes a join condition).  The CBO also likes to add additional where clause items that it thinks might help the processing process.

It evaluates columns such as this 3 way join condition:  IF col from table A = col from table B and col from table B = col from table C…therefore the col from table C = col from table A…and will add this line.

Figure 2. Example Oracle Explain Plan

Notice the SQL submitted for execution does not have an A.SATUS = OPEN syntax but it shows up in the explain plan!  This is query transformation at work.  The A B C SQL has seven lines of code.  The resulting explain plan also has seven lines of code.  The ‘*’ in the ID column indicates there is a where clause item from the SQL associated with this explain plan line.  Notice ID line 7 in the explain plan.  Look up the Predicate Information in the chart underneath…it shows for line 7 Filter (A.STATUS = ‘OPEN’).  Check the SQL syntax–there is no such line!  Query transformation added this line because B.STATUS = ‘OPEN’ (line 5 of the SQL) and A.STATUS = B.STATUS (line 3 of the SQL).

The next article will complete this discussion on hard parsing.