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, and the performance is throughout.

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, and the performance is throughout.

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 Bind variables, You need to obtain the value of the Bind Variable here.

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: