In a previous article, I discussed the PLAN_TABLE and its role in SQL tuning for Oracle. This article will discuss using this PLAN_TABLE to display SQL execution plans, or explain plans.
Technically, the Oracle Hard Parse process produces an execution plan. The explain plan is the visualization of this execution plan. I use the terms synonymously.
How the Oracle Kernel Processes SQLs
When SQL is submitted to the Oracle kernel for processing, the first thing that happens is a checksum is run on the SQL text itself.
This checksum is then run thru a hash algorithm to produce a unique address, or location.
This address is used to see if the SQL is already in the library cache, or has it been processed already today or recently (technically up to the last time statistics were run…statistics purges the library cache and all SQL submitted subsequently has to re-establish an execution plan).
There are two views into the library cache. V$SQL and V$SQL_AREA. There are other views as well, but these two can be used to find useful information about SQL currently in the library cache.
- The V$SQL is the parent cursor; it contains statistics and information about the last time the SQL was executed, and so on.
- The V$SQL_AREA is the child cursor; it actually contains the execution plan for the particular SQL statement. There might be more than one entry for a SQL statement in this child cursor area (I’ll leave that for a later time).
Soft and Hard Parses: Which Happens When and Why
There are two important events that can happen when a SQL statement is presented to the Oracle kernel:
- Soft Parse…the SQL is found in the library cache (using its hash value from the check sum), it has an execution plan, and its ready to be executed
- Hard Parse…the SQL is NOT found in the library cache, or it is found but there are no child cursors assigned (ie: no execution plan). These child cursors are purged from the library cache when the DBA staff collects statistics.
The goal of this checking is to speed the time to execution of the submitted SQL. IF the SQL already has an execution plan and is ready to be executed, then there is no reason to go throughall the work of the hard-parse process.
The goal of the hard parse process is to produce the execution plan. This process uses the statistics collected (usually over the weekend) to gain knowledge about the row counts and granularity of the where clause table column items (called predicates). This information is used to arrive at an execution plan that Oracle thinks would have the fewest number of physical I/O processes. The Oracle optimizer (the Cost-Based Optimizer …CBO) uses math and educated guesses as to the possible number of rows being returned by each Where clause statement of the submitted SQL.
IF the SQL contains bind variables, then the checksum will always produce the same hash address, or, the same location in the library cache. This greatly aids in Oracle’s ability to avoid a hard parse and go right to SQL execution using the information already in the library cache (soft parse).
Bind variables can cause particular execution heartburn for Oracle (poorly performing SQL) and this was addressed in Oracle 11.
Leave a Reply