SQL statement execution process

Chapter 4 SQL statement ProcessingBefore adjustment, we need to know some background knowledge. Only by knowing this background knowledge can we better adjust SQL statements.
This section describes the basic process of SQL statement processing, including:
· Query statement Processing
· DML statement processing (insert, update, delete)
· DDL statement processing (create..., drop..., alter ..,)
· Transaction control (commit, rollback)

SQL Statement Execution process (SQL Statement Execution)

Figure 3-1 lists the important stages required to process and run an SQL statement. In some cases, the SQL running process in Oracle may be different from the sequence of each stage listed below. For example, the define stage may be prior to the fetch stage, which depends on how you write code.

For many oracle tools, some stages are automatically executed. Most users do not need to worry about the details of each stage. However, it is necessary to know each stage of execution. This will help you write more efficient SQL statements, in addition, you can guess which stage the SQL statement with poor performance is mainly caused by. Then, we can find a solution for this specific stage.

Figure 3-1 phases of SQL statement Processing

DML statement Processing

This section provides an example to illustrate what happened in each stage of DML statement processing. Assume that you use the Pro * C program to increase salaries for all employees in the specified department. The program has been connected to the correct user. You can embed the following SQL statement in your program:
Exec SQL update employees
Set salary = 1.10 * salary where department_id =: var_department_id; var_department_id is a program variable that contains the Department number. We want to modify the employee's salary of this department. When this SQL statement is executed, the value of this variable is used.

Each type of statement requires the following phases:
· Step 2: Create a cursor to create a cursor
· Step 2: Parse the Statement Analysis Statement
· Step 2: Bind Any Variables Bind the variable
· Step 2: Run the Statement
· Step 2: Close the Cursor to Close the Cursor

If the parallel function is used, the following stage is also included:
· Step 2: Parallelize the Statement parallel Statement execution

For a query statement, perform the following steps:
· Step 2: Describe Results of a Query describes the Query result set.
· Step 2: Define Output of a Query defines the Output data of the Query.
· Step 2: Fetch Rows of a Query to obtain the queried Rows

Next, let's take a look at what happened in each step :.

Step 2: Create a Cursor)

A cursor is created by calling the program interface ). Any SQL statement will create it, especially when running DML statements, it will automatically create a cursor without developer intervention. In most applications, the creation of cursors is automatic. However, the creation of targets in the precompiled Program (pro * c) may be implicit or explicit. This is also true in the stored procedure.

Step 1: Parse the Statement)

During syntax analysis, SQL statements are transmitted from user processes to Oracle. After syntax analysis, the information of SQL statements and analysis is loaded into the shared SQL zone. In this phase, many types of errors can be solved.

Perform the following operations for syntax analysis:
L translate the SQL statement and verify that it is a valid statement, that is, the statement is correctly written.
L implement data dictionary search to verify that the table and column definitions are met
L obtain the syntax analysis lock on the required object so that the definition of these objects is not changed during the syntax analysis of the statement.
L verify that the permissions required to access the mode object involved are met
L determine the optimal execution plan for this statement
L load it into the shared SQL Zone
L for Distributed statements, route all or part of the statements to the remote node containing the involved data

If an error occurs in any of the preceding steps, the statement is reported incorrectly and the execution is aborted.

Syntax analysis of SQL statements is performed only when there are no equivalent SQL statements in the shared pool. In this case, the database kernel allocates a new shared SQL zone for the statement and performs syntax analysis on the statement. Syntax analysis requires a lot of resources, so we should try to avoid syntax analysis, which is one of the optimization techniques.

The syntax analysis stage contains the processing requirements that only need to be analyzed no matter how many times the statement will be executed. Oracle only translates each SQL statement once. When you execute this statement again later, as long as the statement is still in the shared SQL area, you can avoid re-parsing the statement, that is, you can directly use the corresponding execution plan to access the data. This is mainly implemented by BIND variable, which is commonly referred to as shared SQL. The concept of shared SQL will be given later.

Although syntax analysis verifies the correctness of SQL statements, syntax analysis can only identify errors (such as writing errors and insufficient permissions) that can be found before SQL statements are executed ). Therefore, some errors cannot be obtained through syntax analysis. For example, an error in data conversion or an error in data (for example, an attempt to insert a duplicate value in the primary key) and deadlocks are errors or situations that can be encountered and reported only during the statement execution phase.

Query statement Processing

The query is different from other types of SQL statements because data is returned as a result after successful execution. Other statements simply return success or failure, while query can return one or more rows of data. All query results are in the form of tables. Results rows are retrieved in one row or in batches. We can learn from this that batch fetch data can reduce network overhead, so batch fetch is also one of the optimization techniques.

Some problems are only related to query processing. A query is not only a select statement, but also an implicit query included in other SQL statements. For example, each of the following statements needs to use the query as part of its execution:
Insert into Table select...
UPDATE table SET x = y WHERE...
Delete from table WHERE...
CREATE table as select...

Specifically, the query
· Read consistency
· Rollback segments may be used for intermediate processing
· SQL statements may be required to process the description, definition, and data retrieval phases

Step 2: Describe Results of a Query)

The Description phase is required only when the columns in the query results are unknown. For example, when a query is performed, the user enters the columns to be output interactively. In this case, the characteristics (data type, length, and name) of the query results must be determined in the description phase ).

Step 2: Define Output of a Query)

In the definition phase of the query, you specify the location, size, and data type of the receiving variable corresponding to the queried column value, so that you can get the query result by receiving the variable. If necessary, Oracle automatically converts data types. This is determined by comparing the type of the received variable with the corresponding column type.

Step 2: Bind Any Variables)

At this point, Oracle knows the meaning of the SQL statement, but there is still not enough information to execute this statement. Oracle needs to obtain the values of all variables listed in the statement. In this example, Oracle needs to get a limited value for the department_id column. The process of getting this value is called binding variables)

This process is called bundling variable values. The program must point out the variable name that can be found for this value (this variable is called a bundled variable, and the variable name is essentially a memory address, which is equivalent to a pointer ). The end users of the application may not find that they are specifying the Bind Variable, because the Oracle program may simply instruct them to enter a new value. In fact, all this is done automatically in the program. Because you have specified the variable name, you do not need to re-bind the variable before you execute it again. You can change the value of the bound variable, and Oracle only uses the memory address to find the value each time it executes. If Oracle needs to implement automatic data type conversion (unless they are implicit or default), you must specify the data type and length for each value. For more information, see oracle documentation, such as the Oracle Call Interface Programmer's Guide.

Step 2: run the Statement in parallel (Parallelize the Statement)

ORACLE can perform parallel query operations in SELECTs, INSERTs, UPDATEs, MERGEs, and DELETEs statements. For some DDL operations, such as creating indexes, creating tables with subqueries, and performing operations on partitioned tables, you can also perform parallel operations. Parallelism can cause multiple server processes (oracle server processes) to work for the same SQL statement, so that the SQL statement can be completed quickly, but it will consume more resources, unless necessary, otherwise, do not use parallel queries.

Step 2: Run the Statement)

Now, Oracle has all the required information and resources, so it can truly run SQL statements. If this statement is a SELECT query or INSERT statement, no rows need to be locked because no data needs to be changed. However, if the statement is an UPDATE or DELETE statement, all rows affected by the statement will be locked to prevent other users from modifying the data before the user submits or rolls back. This ensures data consistency. For some statements, you can specify the number of executions, which is called array processing ). If you specify to execute N times, the binding variable and the definition variable are defined as the starting position of the array whose size is N. This method can reduce the network overhead and is also one of the optimization techniques.

Step 1: Fetch the queried Rows (Fetch Rows of a Query)

In the fetch stage, the row data is obtained, and the next row of data in the retrieval result set of each subsequent access operation is obtained until the last row is obtained. As mentioned above, batch fetch is one of the optimization techniques.

Step 2: Close the Cursor)

The last stage of SQL statement processing is to close the cursor.

DDL Statement Processing)

The execution of DDL statements is different from that of DML statements and query statements because the data dictionary data needs to be modified after the DDL statements are executed successfully. For DDL statements, the statement analysis stage includes analyzing, searching for data dictionary information, and executing. Transaction Management statements, session management statements, and system management statements are only in the analysis and execution stages. To re-execute the Statement, the statement is re-analyzed and executed.

Control of Transactions)

Generally, only Application designers who use ORACLE programming interfaces care about the operation type and organize related operations to form a transaction. In general, we must define transactions so that all work in a logical unit can be committed or rolled back at the same time, ensuring data consistency. A transaction should be composed of all the necessary parts in the logical unit. There should be no more or less.
· During the start and end of a transaction, all data in the referenced table should be in the same state (or can be traced back to the same State)
· The transaction should only contain SQL statements that can make consistent changes to the data

For example, the transfer between two accounts (this is a transaction or logical unit of work) should include borrowing money from one account (completed by one SQL statement ), then, save the borrowed money to another account (completed by another SQL statement ). These two operations, as a logical unit, should succeed at the same time or fail at the same time. Other unrelated operations, such as saving money to an account, should not be included in the transfer transaction.

When designing an application, in addition to determining which type of operations constitute a transaction, you also need to determine whether the use of the BEGIN_DISCRETE_TRANSACTIO stored procedure can improve the performance of small and non-distributed transactions.

Http://blog.chinaunix.net/u2/61723/showart_482625.html