This is a related topic to my recent Explain Plan 101 post. Watch for advanced Explain Plan topics in Explain Plan 201 and Explain Plan 301 articles.
The CBO hard-parses a submitted SQL statement when:
The submitted SQL for execution has a check-sum run on it. A check sum will almost certainly produce a unique number. This number is then hashed and used to look and see if the SQL is in the library cache already. IF it is not, or, there is not a child cursor for this SQL (removed by the statistics auto-invalidate process).
A soft-parse is when the SQL is both found and there is an execution plan for the SQL. The SQL is then submitted for execution.
IF the SQL is not in the library cache or it does not have an execution plan, it is then submitted for hard-parse.
The CBO runs up to 4 dictionary SQL to:
Query Transformation is part of the SQL hard-parse process. This blog will discuss how query transformation works and when it occurs. Next week I’ll review the remainder of the hard-parse process.
When the CBO performs a hard-parse on a SQL statement, it first does query transformation, or, as Oracle puts it…opens access paths.
Oracle has always done some SQL rewrite such as changing ‘OR’ clauses on same columns to IN clauses, adding functions to WHERE clauses to make data types the same, etc.
Oracle9 introduced the concept of opening access paths. The CBO likes to ‘unnest’ sub queries, turning these into inline views. The Oracle9 CBO can also ‘push predicates’, the where clause items into the sub queries. If you review V$SQL_HINT in Oracle11, you will see many hints that encourage this behavior and discourage this behavior.
Oracle11 has added additional query transformations for NOT IN and NOT EXISTS sub queries (called null-aware anti joins). Oracle12 will transform GROUP BY clauses into inline views.
The NO_QUERY_TRANSFORMATION will inhibit the ‘opening of access paths’…but not the original work of converting OR clauses and making data types the same in WHERE clauses.
In the SQL below, notice the use of the QB_NAME hint to display query blocks in TOAD. Notice that the first sub query does not appear in this list even though there is a QB_NAME hint. This is because the CBO query transformed this first sub query into an inline view.
The converted SQL does appear in the CBO Trace, the 10053 trace.
Notice the NO_QUERY_TRANFORMATION hint and now the EMP1 Qblock Name now appears.
Next week I’ll review what the CBO permutations involve and complete the hard-parse discussion.
Dan HotkaOracle ACE DirectorInstructor/Author/CEO
So what is bullet #4: • And ???