Hard Parsing and Soft Parsing
The process of compiling a new cursor is referred to as hard parsing (and is worthy of a book all its own); within the context of this chapter it can be simplified to four steps:
- Validation
The SQL syntax of the cursor is validated for correctness, and the objects (tables and columns) to which it refers are verified.
- Compilation
The cursor is compiled into executable form and is loaded into the database’s shared pool. Its location in the shared pool is referred to as its address.
- Execution plan calculation
Oracle’s cost-based optimizer (CBO) determines the best execution plan for the cursor and attaches it to the cursor.
- Hash calculation
The ASCII value of each character in the cursor is totaled up and sent to a hash algorithm. The algorithm calculates a value to make the cursor easy to find for subsequent reuse. This value is referred to as the cursor’s hash value. We’ll look more at ASCII values later in this section.
A good deal of database latch activity takes place during these operations because Oracle cannot allow any underlying objects (tables and columns) to change while the cursor is being validated and compiled. The activity is almost entirely CPU-driven, so a portion of your database server’s CPU will be consumed while compilation occurs. More importantly, the really important work of fetching records will be delayed. Subsequent executions of the same cursor (either by the same program or by another) can avoid incurring the costly hard-parse ...