Shows the general phase of SQL internal processing in Oracle: parsing, optimizing, generating row sources, and execution. The database may ignore certain steps, depending on the specific statement.
The first phase of 1,SQL parsing SQL processing is SQL parsing. When an application issues an SQL statement, the application issues a parse call to the database to prepare to execute the statement, and the parse call opens or creates a cursor that is a handle to a private, session-specific SQL area that contains the parsed SQL statement and other processing information. cursors and private SQL zones are located in the PGA. During a parse call, the database performs the following checks: syntax check semantic check--whether the object and column exist shared pool check database perform shared pool check to determine when you can skip the resource intensive language Sentence processing steps. To do this, the database uses a hashing algorithm to generate a hash value for each SQL statement. The hash value of the statement is in V$sql. sql_id shown in sql_id (refer to note: Distinguish between 4 SQL-related fields: Hash_value, Sql_hash_value, Plan_hash_value, and sql_id), when the user submits an SQL statement, The database searches the shared SQL area to see if there is a ready-made parsed statement with the same hash value. The hash value of the SQL statement differs from the following value: The statement's memory address value (the Address field value of V$sql) This statement executes a planned hash value (the Plan_hash_value field value of the V$sql_plan view) based on the type and hash check of the statement being submitted , the parsing operation is divided into the following categories: Hard parsing if the database cannot reuse existing code, it must generate a new executable version of the application code, which is called a
Hard ParseOr
Library Cache misses。 The database always performs hard parsing on the DDL. During hard parsing, the database accesses the library cache and the data dictionary cache multiple times to examine the data dictionary. When the database accesses these zones, it uses a serialized device called a latch on the desired object so that their definition does not paste and be changed. Contention for a latch increases the execution time of the statement and reduces concurrency. Soft parsing any ill-resolved parsing is
Soft parsing。 If you submit a statement that is the same as a reusable SQL statement in a shared type, the database reuses that existing code. Reuse code is also known as
Library Cache Hit。 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. is a simplified representation of the shared pool check for an UPDATE statement in a dedicated server architecture. (it appears that the hash value of the SQL text is generated in the PGA). If you check that a statement in the shared library has the same hash value, the database performs semantic and environmental checks (workspace size or optimizer settings, etc.) and, of course, the writing of the statement itself (case, space, comment, and so on). Details see note: Oracle Performance tuning hard and soft parsing 2,sql optimization Query optimization is the process of choosing the most efficient way to execute SQL statements. Database optimization of queries is based on statistical information collected for actual data being accessed. The optimizer uses the number of rows, dataset size, and other factors to generate various possible execution plans and assigns a cost value to each plan. The database uses a plan with the lowest cost. The database must perform a hard parse at least once for each unique DML statement and perform optimizations during hard parsing. DDL is never optimized unless he includes DML components that need to be optimized, such as subqueries. 3,sql line Source generation Row Source generator is a software that accepts an optimized execution plan from the optimizer and generates an iteration plan called a query plan, which is used for the remainder of the database altogether. The query plan takes the form of combining multiple steps, with each step returning a rowset. The rows in the collection can be used in the next step, and the final fire is returned to the application that issued the SQL statement. A row source is a rowset that returns more than one 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 operation or grouping operation. Row source generators produce a row source tree, which is a collection of row sources. (which is what we see in the execution plan) 4,sql execution during execution, the SQL engine executes each row source in the number of rows generated by the source generator. This step is the only mandatory step in DML processing. In the execution plan, we often see an execution tree that shows the lineThe source flows from one to the other step. In general, the order of execution is the opposite of almost the order, so we should read the plan from the bottom up. The initial space in the Operation column represents a hierarchical relationship. For example, if the name of an operation is preceded by two spaces, then this action is a child of the operation preceded by a space. The operation preceded by a space is a sub-operation of the SELECT statement itself. Reference: http://docs.oracle.com/cd/E11882_01/server.112/e40540/sqllangu.htm#CNCPT216
SQL-specific processing flow within Oracle
