1, SQL resolution
The first stage of SQL processing is SQL parsing. When an application issues a SQL statement, the application issues a parsing call to the database to prepare for execution. The parsing call opens or creates a cursor that is a handle to a session-specific private SQL area that contains Parsed SQL statements, and other processing information. Cursors and private SQL fields are located in the PGA.
During the parsing call, the database performs the following checks:
Semantic check - whether the object and column exist
Shared pool check
The database performs a shared pool check to determine when to skip statement processing steps that take up a lot of resources. To do this, the database uses a hashing algorithm to generate a hash for each SQL statement. The hash value of the statement is the SQL_ID shown in V $ SQL.SQL_ID (see note: distinguishing four sql-related fields: hash_value, sql_hash_value, plan_hash_value, and sql_id). When a user submits a SQL statement, the database search share SQL zone to see if there is an existing parsed statement that has the same hash value. The hash value of the SQL statement is different from the following:
The statement of the memory address value (V $ sql address field value)
The statement executes the plan hash value (V $ SQL_PLAN view of the plan_hash_value field value)
Based on the type of statement being submitted and the results of the hash check, the resolution operation is divided into the following categories:
If the database can not reuse existing code, it must generate a new executable version of the application code, the second operation is called a hard parse, or the library cache misses. The database always performs a hard parse of DDL.
During hard resolution, the database accesses the library cache and data dictionary cache multiple times to check the data dictionary. When the database accesses these areas, it uses a serialization device called a latch on the desired object so that their definitions are not altered. The contention of the latch increases the execution time of the statement and reduces concurrency.
Analysis of any discomfort is a soft analytical soft analysis. If the submitted statement is the same as a reusable SQL statement in the share, the database reuses the existing code. Reuse code is also called library cache hits.
In general, soft parsing is preferable to hard parsing because the database can skip the optimization and row source generation steps and go directly to the straight-line phase. The following figure is a simplified representation of a shared pool check for an update statement in a dedicated server architecture.
(It appears that the hash of the SQL text is generated in the PGA).
If you check that a statement in the shared library has the same hash value, then the database performs semantic and environmental checks (workspace size or optimizer settings, etc.) and, of course, the statement itself (case, space, comments, etc.) ).
Details can be found in the note: "Oracle performance tuning hardware analysis and soft analysis"
2, SQL optimization
Query optimization is the process of selecting the most efficient means of executing SQL statements. The database is optimized for queries based on statistical information about the actual data being accessed. The optimizer uses the number of rows, dataset size, and other factors to generate a variety of possible execution plans and assign a cost value to each plan. The database uses the plan with the lowest cost.
The database must perform at least one hard resolution for each unique DML statement and perform optimizations during hard resolution. DDL will never be optimized unless it includes DML components that need to be optimized, such as subqueries.
3, SQL line source generation
A line-source generator is software that receives an optimized execution plan from the optimizer and generates an iteration plan called a query plan that is used by the rest of the database. A query plan takes the form of combining multiple steps, each returning a rowset. The rows in the collection can be used in the next step, and the last step of the fire is returned to the application that issued the SQL statement.
A row source is a set of rows returned from a step in the execution plan, with a control structure that iterates over the rowset, which can be the result of a table, view, or join or grouping operation.
The row source generator generates a row source tree, which is a collection of row sources. (That is, we see the implementation plan)
4, SQL implementation
During execution, the SQL engine executes each row source in the number generated by the row source generator. This step is the only mandatory step in DML processing. In the implementation plan, we often see is an implementation of the tree, showing the line from one source to another. Often, the order in which steps are performed is almost the reverse of the order, so we should read the plan from the bottom up. The initial space in the operation column represents the hierarchical relationship. For example, if the name of an operation is preceded by two spaces, this operation is a sub operation of an operation that precedes a space. In front of a blank space is the select statement itself sub-operation.