See how Oracle queries work from Select statements

We have previously introduced the query function for deep SELECT statements. A Select statement can be one of the most frequently used statements by DBA and database developers at work. But how can this statement be executed? How Does Oracle database work? Today, we will start with a simple Select statement to look at the operating mechanism of the Oracle database background. This is very helpful for system management and troubleshooting.

Step 1: the client sends the statement to the server for execution.

When we execute the select statement on the client, the client sends the SQL statement to the server and allows the server process to process the statement. In other words, the Oracle client does not perform any operations. Its main task is to send some SQL statements generated by the client to the server. Although there is also a database process on the client, the role of this process is different from that of the process on the server. The database process on the server can process SQL statements. However, it should be noted that the client processes correspond to the server processes one by one. That is to say, after the client connects to the server, a process is formed between the client and the server. The client is called the client process, and the server is called the server process. Therefore, because all SQL statements are executed by server processes, some may regard server processes as "Shadows" of client processes ".

Step 2: Statement parsing.

After the client sends an SQL statement to the server, the server process parses the statement. Similarly, this parsing work is also carried out on the server side. Although this is only a parsing action, it will do a lot of "small actions ".

1. query the cache. When the server process receives the SQL statement sent from the client, it does not directly query the database. Instead, the system first searches for the cache of the database to check whether the execution plan of the same statement exists. If someone else uses this query statement in the data cache, the server process will directly execute this SQL statement to save the subsequent work. Therefore, using high-speed data cache can improve the query efficiency of SQL statements. On the one hand, reading data from the memory is more efficient than reading data from data files on the hard disk. On the other hand, it is also because of the parsing of this statement.

However, you must note that this data cache is different from the data cache of some client software. Some client software sets data cache on the client of the application software to improve query efficiency. Because of the existence of the data cache, the query efficiency of the client application software can be improved. However, if others make relevant modifications on the server, the modified data cannot be promptly reflected on the client due to the existence of the application software data cache. From this we can see that the data cache of the application software is not the same as the high-speed data cache of the database server.

2. Check the statement validity.

When the corresponding SQL statement cannot be found in the cache, the database server process starts to check the validity of this statement. Here we mainly check the syntax of SQL statements to see if they comply with the syntax rules. If the server process determines that the SQL statement does not comply with the syntax rules, the error message is returned to the client. During this syntax check, the SQL statement does not check the table name, column name, and so on.

3. Check the language meaning.

If the SQL statement meets the syntax definition, the server process checks the fields and tables in the statement. Check whether these fields and tables are in the database. If the table name and column name are inaccurate, the database reports the error information to the client.

Therefore, when we write a select statement, if the syntax is wrong with the table name or column name at the same time, the system first prompts a syntax error. When the syntax is correct, the system prompts that the column name or table name is incorrect. If you can grasp this order, you can save time in troubleshooting the application.

4. Get the object resolution lock.

When the syntax and semantics are correct, the system locks the objects to be queried. This is mainly to ensure data consistency and prevent other users from changing the structure of this object during the Query Process. I have already specifically described the principles and methods of locking in other articles. I will skip this article here.

5. Check data access permissions.

After checking the syntax and semantics, the client may not be able to obtain data. The server process also checks whether the user you connect has the permission to access the data. If the user you connect to the server does not have the data access permission, the client cannot obtain the data. Sometimes, when we query data, we have worked hard to write and compile SQL statements. However, at last, the system returns an error message "no permission to access data", which makes us angry. This may be encountered during front-end application software development and debugging. Therefore, pay attention to this problem. The database server process checks the syntax and semantics before checking the access permission.

6. determine the best execution plan.

If the statements and syntaxes are correct and the permissions match, the server process does not directly query the database files. The server process will optimize this statement according to certain rules. However, note that this optimization is limited. Generally, the SQL language of the database needs to be optimized in the process of application software development. The function of this optimization is greatly greater than the self-optimization of the server process. Therefore, database optimization is indispensable during application software development.

After the server process optimizer determines the optimal execution plan of the query statement, the SQL statement and execution plan are saved to the data cache. In this case, the preceding syntax, semantics, and permission check steps will be omitted when this query is still available, and the SQL statements will be executed directly to improve the SQL statement processing efficiency.

Step 3: Execute the statement.

Statement parsing only parses the syntax of the SQL statement to ensure that the server can know what the statement actually represents. After the statement Parsing is complete, the database server process will actually execute this SQL statement.

This statement can also be executed in two cases. First, if the data block of the selected row has been read to the data buffer, the server process will directly pass the data to the client instead of querying the data from the database file. If the data is not in the buffer, the server process queries the relevant data from the database file and puts the data into the data buffer.

Note that many types of cache are defined in the Oracle database. As mentioned above, the SQL statement cache is similar to the current data cache. When learning about databases, we need to have a clear understanding of these caches and understand the role of various types of caches. This is very useful for our subsequent database maintenance and database optimization.

Step 4: extract data.

After the statement is executed, the queried data is still in the server process and has not been transferred to the client user process. Therefore, there is a piece of code specifically responsible for data extraction in the server-side process. The function is to return the queried data results to the client process to complete the entire query.

During the entire query process, we should pay attention to the following points during database development or application software development:

First, we need to know the database cache and application software cache. The database cache exists only on the database server and does not exist on the client. Only in this way can the content in the database cache be consistent with that in the database file. In order to prevent the occurrence of dirty or wrong reads according to relevant rules. The data cache involved by the application software is not exactly the same as the database cache. Therefore, although the data cache of the application software can improve the data query efficiency, but it breaks the requirements of data consistency. Sometimes dirty reads and wrong reads occur. Therefore, sometimes there is a special function in the application software to clear the data cache when necessary. However, the data cache is only used to clear the data cache on the local machine, or to clear the data cache of the application without clearing the data cache of the database.

Second, most SQL statements are processed according to this process. Our DBA or Oracle database-based developers understand the processing process of these statements. It is very helpful for us to develop and debug SQL statements. Sometimes Mastering these processing principles can reduce the time for troubleshooting. Note that the database reviews the Data Query permission after the syntax and semantics. Therefore, sometimes the database permission control principle may not meet the application software permission control requirements. In this case, the foreground settings of the application software are required to implement permission management. In addition, sometimes the permission management of the application database is a little complicated and will increase the workload of server processing. Therefore, for the query permission control of records and fields, most people involved in the program prefer to implement it in the application, rather than in the database.