Specific SQL processing procedures in Oracle

Source: Internet
Author: User
Tags dedicated server

Specific SQL processing procedures in Oracle

Shows the general phases of SQL processing in Oracle: parsing, optimization, generation of row sources and execution. The database may ignore some steps, depending on the specific statements.

1. SQL Parsing

The first stage of SQL processing is SQL parsing. When an application sends an SQL statement, the application sends a parsing call to the database to prepare to execute the statement. The parsing call opens or creates a cursor, it is a session-specific private SQL zone handle, which contains the analyzed SQL statements and other processing information. The cursor and private SQL area are located in PGA. During parsing and calling, the database performs the following checks: ● syntax check ● semantic check -- whether objects and columns exist ● Shared Pool check the database performs a Shared Pool check, you can skip the statement processing steps that occupy a large amount of resources. Therefore, the database uses a hash 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 (Reference Note: differentiate four SQL-related fields: hash_value, SQL _hash_value, plan_hash_value, and SQL _id). When you submit an SQL statement, the database searches for the shared SQL area to check whether a ready-made analysis statement has the same hash value. The hash value of an SQL statement is different from the following values: ● memory address value of the statement (address field value of V $ SQL) ● The Hash Value of the statement execution plan (the plan_hash_value field value in the V $ SQL _PLAN view) is divided into the following categories based on the type of the submitted statement and the result of the hash check: ● hard parsing if the database cannot reuse existing code, it must generate a new executable version of the application code. The next operation is called a hard parsing, or the database cache is not hit. The database always performs hard parsing on the DDL. During hard parsing, the database accesses the database cache multiple times and the data dictionary cache to check the data dictionary. When the database accesses these areas, it uses a serialized device called a latch on the desired object so that their definitions are not changed. The contention of latches increases the statement execution time and reduces concurrency. ● Soft resolution any unsuitable soft Resolution resolution is soft resolution. If the submitted statement is the same as a reusable SQL statement in the shared statement, the database will reuse the existing code. Code reuse is also called database cache hit. Generally, soft resolution is more desirable than hard resolution, because the database can skip optimization and generate row-source steps and directly enter the direct row stage. Is a simplified representation of the Shared Pool check of an update statement in the dedicated server architecture. (It seems that the hash value of SQL text is generated in PGA ). If a statement in the Shared Library has the same hash value, the database performs a semantic and Environment check (workspace size or optimizer setting ), of course, there are also statements (case, space, comments, etc ). For details, see Notes: Oracle Performance Tuning hard resolution and soft resolution 2. SQL optimization query optimization is the most effective way to execute SQL statements. The optimization of database queries is based on the statistics collected on the actual data being accessed. The optimizer uses the number of rows, dataset size, and other factors to generate various possible execution plans and assign a cost value to each plan. The database uses a plan with the lowest cost. The database must execute hard parsing at least once for each unique DML statement and perform optimization during hard parsing. DDL will never be optimized unless it includes DML components to be optimized, such as subqueries. 3. the SQL row source generates a row source generator, which accepts optimized execution plans from the optimizer and generates an iteration plan called a query plan, the rest of the database is used. A query plan combines multiple steps to return a row set for each step. The rows in the set can be used in the next step. The last step of the fire is returned to the application that issues the SQL statement. The row source is the row set returned by multiple rows in one step of the Execution Plan and has a control structure that can iterate over the row set. The row source can be the result of table, view, or join operation or group operation. The row source generator generates a row source tree, which is a collection of row sources. (The execution plan we see) 4. Each row source in the number of row source generators executed by the SQL engine during execution. This step is the only mandatory step in DML processing. In the Execution Plan, we often see an execution tree, showing the line source flows from one to another. Generally, steps are executed in almost the opposite order, so we should read the plan from the bottom up. The initial space in the operation column indicates the hierarchical relationship. For example, if there are two spaces before an operation name, this operation is a suboperation with a space before it. The preceding space operation is a sub-operation of the select statement. Reference: http://docs.oracle.com/cd/E11882_01/server.112/e40540/sqllangu.htm#CNCPT216

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.