In-depth analysis of SQL statement running in Oracle

Recently, the project has been completed (maybe not enough), but a fatal obstacle is performance. Now let's think about how brilliant the top author is. The performance runs through various stages such as project requirements, project analysis and design, project coding, unit testing, and integration. Instead of waiting for the project to be developed, consider performance issues. However, this is already true. Therefore, we have recently optimized SQL statements. To better optimize SQL statements, we naturally need to know that the SQL engine in Oracle has a given SQL statement, he has done little-known things. This article is a summary of my learning and only serves as a learning record. I am very grateful for any mistakes or errors!

In order to continue the description from cursor in top, the cursor here is not the cursor in PL/SQL. it is still unclear how to explain this cursor in Chinese. However, I think it is actually a conceptual concept that represents the title of SQL in different environments. When an SQL statement enters the SQL engine, it is cursor. The following shows the life cycle of the top cursor:

1: Open cursor: In the private memory UGA (User global area) of the server process in this session, a memory zone will be opened for this cursor. The SQL statement is not associated with the cursor.

2: parse cursor: this process will associate the SQL statement with the cursor. This step is commonly used for SQL parsing, and finally saves the execution plan to the shared zone. Then, the cursor in UGA points a pointer to a shared cursor in the shared area.

3: Define output variables: defines output variables for insert, update, and delete statements that query and use the returning syntax.

4: bind input variables: If the SQL statement uses the Bind Variable, You need to obtain the value of the BIND variable.

5: Execute cursor: execution, but the actual execution of SQL statements is often delayed until the next step.

6: Fetch cursor: If the SQL statement returns data, the returned data is obtained here.

7: Close cursor to release resources occupied by Uga. However, the cursor in the shared area is not released.

In the above process, the parsing of SQL statements that we care about is always taken. Next, analyze the Second Step parse cursor. For SQL statement parsing, I have long heard that it is soft parsing and hard parsing. Next we will analyze the entire parsing process:

1. The entire parsing process starts with a predicate containing VPD. If the SQL statement contains a table that has VPD (Virtual Private Database) applied, the VPD Security Policy predicate is first appended to the WHERE clause of the SQL statement.

2. syntax, semantic check, and permission control. The syntax check checks the correctness of the SQL statement keywords. The semantic check mainly checks whether the SQL statement references nonexistent objects or violates related constraints. Permission Control

It mainly determines whether the current operation has the corresponding operation permissions.

3. Get a parent cursor from the shared area. If not, allocate the memory in the shared area and define a new parent cursor. The key information of the parent cursor structure is the SQL statement itself.

4. logic optimization. Different algorithms and policies are used in this process to convert SQL statements and generate many equivalent SQL statements.

5. Physical optimization: first, generate the respective execution plans for each SQL statement in the previous step, then, a cost is calculated for each execution plan based on the analysis statistics in the system data dictionary or the statistics obtained through dynamic sampling, and the Execution Plan with the minimum cost is selected.

6. Store a child cursor in the shared area. The child cursor is associated with the preceding parent cursor. The child cursor mainly stores the execution plan and running environment information.

You can see that there is a parent cursor and child cursor in the sharing area to save the information of an SQL statement. by querying views such as V $ sqlarea, V $ SQL, and V $ sqltext, we can obtain information about SQL and cursor.

For the above processes, we can see that parent cursor and child cursor are in the sharing area, that is, they can be shared. Hey hey, that is why there is soft resolution and hard resolution.

Soft resolution: if the parent cursor and child cursor already exist in the shared area, you only need the first two steps. This is soft parsing.

Hard parsing: If parent cursor and child cursor cannot be shared, the complete process is required. This is hard parsing.

The following is an example to illustrate the sharing mechanism and precautions.

First, for the following query statements

Select * from t where n = 1024;

Select * from t where n = 1024;

Select * from t where n = 1024;

Select * from t where n = 1024;

Execute the preceding four query statements. Then, check the V $ sqlarea view and find that the first and fourth query statements are the same and only need to be parsed once, hard Parsing is not required for the second execution, so the number of executions of the first statement is 2. It indicates that the SQL statement must be identical to share the parent cursor.

Select SQL _id, SQL _text, executions from V $ sqlarea where SQL _text like '% N = 1024%'; the result is not pasted.

Bind Variable: for variable binding, it may increase the amount of code for development. From the perspective of performance, it has a huge impact on the OLTP system, and many systems are paralyzed. For example, for the following query statement, select * from t where n = 1024; if we construct an SQL statement in the program, use the following code:

Private string buildsql (int n ){

Stringbuilder sb = new stringbuilder ("select * from t where n = ");

SB. append (N );

Return sb. tostring ();

}

If n in an application contains 0.1 million different values and all these values are executed, 0.1 million shared records will be stored in the shared area. For each value, the SQL statement is two completely different SQL statements.

Therefore, we recommend that you use variable binding to construct SQL statements in the OLTP system. However, binding variables also results in an unsatisfactory execution plan. For example:

Select * from t where c <1000;

If the number of results records for this query is 90% of the total data, the execution plan will select full table scan instead of indexing.

Select * from t where c <10;

For this query, the optimizer will certainly take the index instead of the full table scan.

However, when a variable is bound, the query statement is select * from t where c <: C; in this way, the optimizer cannot know the number of records currently queried, when an execution plan is generated, the selected execution plan may not be optimal.

This seems to have been improved in 9i and added Bind Variable peeking. During hard parsing, the bound variable value is added and the execution plan is generated.