Database Reference
In-Depth Information
Step 1: Create a Cursor
A cursor can be considered as an association between the data area in a client program and the Oracle server's data
structures. When a program is required to process an SQL statement, it is required to open a cursor. In Oracle, the
cursor contains a pointer to the current row; and, as the rows are fetched, the pointer moves to the next row until all
the rows specified by the condition in the statement have been processed. Although the cursor is associated with
the statement, it operates independently of the statement. The cursor is created by a program interface call (OPI) in
expectation of an SQL statement.
SQL statement processing in general is complex in nature. It has to allocate and inspect various areas of the
memory before the results are collected and returned to the user. The various physical components that the SQL
statement has to iterate through are
•
Client-side runtime memory
•
Server-side runtime memory
•
Server-side private SQL area
•
Server side shared SQL area or the Library Cache
Step 2: Parse the Statement
This is the most complicated and expensive phase of the operation. During this phase, the statement is passed from
the user's process to Oracle and is loaded into the shared SQL area. Before loading into the SQL shared area, the
following steps have to be completed: the SQL statement is translated and verified, the table and columns checks
are performed, and Oracle places a parse lock to prevent definitions from changing. While verifying the definitions,
Oracle also checks the user privileges against the referenced objects. Oracle generates the query plan to determine the
optimal execution path, followed by loading of the statement into the shared SQL area.
During this phase of operation, Oracle has to determine if the statement being executed is not identical to a
previously executed statement. Based on the findings, Oracle will determine whether to use the previous parse
information from a previous execution or if the statement has to be parsed before being executed. This is the
advantage of the library cache feature. The library cache feature was introduced in version 7.0 of Oracle, which
brought about the concept of sharing SQL statements.
There are two types of parses: soft parse and hard parse. Based on the repeated usage of the statement and Oracle
finding the parse information in the library cache, it determines if this is a hard parse or a soft parse.
Hard Parse
When a statement is executed for the first time and Oracle does not find any information pertaining to this statement
in the library cache, Oracle has to do a complete parse operation, which is also referred to as the hard parse. Hard
parsing comprises of the following steps:
•
Syntax checks on the statement
•
Semantic checks on the statement
•
Data dictionary validation for the objects and columns
•
Name translation of the objects (with reference to synonyms)
•
User privileges for the objects referenced in the SQL statement
•
Generation of an execution plan, with the help of the Optimizer
•
Loading of the statement into the library cache
Search WWH ::
Custom Search