How Parsing Works
While the previous section described the life cycle of cursors, this section focuses on the parse
phase. The steps carried out during this phase, as shown in Figure 2-2, are the following:
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.
OLTP/BATCH et bind variables
Répondre avec citation
mais je débute sur Oracle et c'est assez tentaculaire... alors je reprends: j'ai un buffer cache hit ratio de 57% sur Toad et un buffer hit de 99% sur Statspack.
Envoyé par Saperlapon
Partager