oracle解析游标,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.