Oracle 游标的生命周期 和游标的解析

一：游标的生命周期

 

Open cursor: A memory structure for the cursor is allocated in the server-side private memory
of the server process associated with the session, the user global area (UGA). Note that no
SQL statement is associated with the cursor yet.

 

Parse cursor: A SQL statement is associated with the cursor. Its parsed representation that
includes the execution plan (which describes how the SQL engine will execute the SQL
statement) is loaded in the shared pool, specifically, in the library cache. The structure in
the UGA is updated to store a pointer to the location of the shareable cursor in the library
cache. The next section will describe parsing in more detail.

 

Define output variables: If the SQL statement returns data, the variables receiving it must
be defined. This is necessary not only for queries but also for DELETE, INSERT, and UPDATE
statements that use the RETURNING clause.

 

Bind input variables: If the SQL statement uses bind variables, their values must be provided.
No check is performed during the binding. If invalid data is passed, a runtime error will be
raised during the execution.

 

Execute cursor: The SQL statement is executed. But be careful, because the database engine
doesn’t always do anything significant during this phase. In fact, for many types of queries,
the real processing is usually delayed to the fetch phase.

 

Fetch cursor: If the SQL statement returns data, this step retrieves it. Especially for queries,
this step is where most of the processing is performed. In the case of queries, rows might
be partially fetched. In other words, the cursor might be closed before fetching all the rows.

 

Close cursor: The resources associated with the cursor in the UGA are freed and consequently
made available for other cursors. The shareable cursor in the library cache is not
removed. It remains there in the hope of being reused in the future.

 

 

二：游标的解析：

 

Include VPD predicates: If Virtual Private Database (VPD, formerly known as row-level
security) is in use and active for one of the tables referenced in the parsed SQL statement,
the predicates generated by the security policies are included in its WHERE clause.

 

Check syntax, semantics, and access rights: This step makes sure not only that the SQL
statement is correctly written but also that all objects referenced by the SQL statement
exist and the current user parsing it has the necessary privileges to access them.

 

Store parent cursor in library cache: Whenever a shareable parent cursor is not yet available,
some memory is allocated from the library cache, and a new parent cursor is stored inside it.
The key information associated with the parent cursor is the text of the SQL statement.

 

Logical optimization: During this phase, new and semantically equivalent SQL statements
are produced by applying different transformation techniques. In doing so, the amount of
execution plans considered, the search space, is increased. The purpose is to explore execution
plans that would not be considered without such transformations.

 

Physical optimization: During this phase, several operations are performed. At first, the
execution plans related to each SQL statement resulting from the logical optimization
are generated. Then, based on statistics found in the data dictionary or gathered through
dynamic sampling, a cost is associated with each execution plan. Lastly, the execution
plan with the lowest cost is selected. Simply put, the query optimizer explores the search
space to find the most efficient execution plan.

 

 

Store child cursor in library cache: Some memory is allocated, and the shareable child
cursor is stored inside it and associated with its parent cursor. The key elements associated
with the child cursor are the execution plan and the execution environment.

 

三 :相关视图和软解析

        Once stored in the library cache, parent and child cursors are externalized through the
views v$sqlarea and v$sql, respectively. The cursors are identified in three columns: address,
hash_value, and child_number. With address and hash_value, the parent cursors are identified;
with all three values, the child cursors are identified. In addition, as of Oracle Database 10g, it
is also possible, and it is more common as well, to use sql_id instead of the pair address and
hash_value for the same purpose.

 

       When shareable parent and child cursors are available and, consequently, only the first
two operations are carried out, the parse is called a soft parse. When all operations are carried
out, it is called a hard parse.