Looking at the working principle of Oracle database query from a single SELECT statement

If we now use a SELECT statement to query data from a database, how does the Oracle database work? What can we learn from it? Below, combine a simple SELECT statement to see the operating mechanism of Oracle database backstage. This is very helpful for our subsequent system management and troubleshooting.

The first step: the client sends the statement to the server side for execution.

When we execute the SELECT statement on the client, the client sends the SQL statement to the server side, and the server-side process processes the statement. That is, the Oracle client is not doing anything, and his main task is to send some SQL statements generated by the client to the server side. Although there is also a database process on the client side, the role of the process is not the same as the process on the server. The database process on the server does not handle the SQL statements as appropriate. However, there is a problem to be explained, that is, the client process and the server process is one by one corresponding. That is, after the client connects to the server, a process is formed on the client and server side, and we are called the client process on the client, and the server is called the server process. Therefore, because all SQL statements are executed by the server process, some people liken the server process to the "shadow" of the client process.

Step two: statement parsing.

When a client sends an SQL statement to the server, the server process resolves the statement. Similarly, this parsing work is done on the server side. Although this is only an analytic action, but it will do a lot of "little gestures."

1, query cache. The server process does not go directly to the database query when it receives the SQL statements that the client sends over. Instead, it looks in the cache of the database to see if the execution plan for the same statement exists. If someone else is using the query in the data cache, the server process executes the SQL statement directly, eliminating the subsequent work. Therefore, the use of high-speed data caching, can improve the query efficiency of SQL statements. On the one hand, it is more efficient to read data from memory than to read it from the data file in the hard disk, on the other hand, because of the reason for parsing the statement.

Note here, however, that this data cache is not the same as the data caching of some client software. In order to improve query efficiency, some client software will set up data caching in the client of the application software. Because of the existence of these data caches, the query efficiency of client application software can be improved. However, if other people in the server for the relevant changes, due to the existence of the application software data cache, resulting in modified data can not be reflected in a timely manner to the client. From this, it can be seen that the application of the software data caching and database server high-speed data caching is not the same.

2, the statement legality check.

When the corresponding SQL statement is not found in the cache, the database server process begins checking the legality of the statement. This is mainly to check the syntax of the SQL statement to see if it conforms to the grammatical rules. If the server process thinks that the SQL statement does not conform to the syntax rules, the error message is fed back to the client. In this syntax check, SQL statements are not included in the table name, column name, and so on, and so he is just a grammar check.

3, language meaning check.

If the SQL statement conforms to the grammatical definition, the server process will then check the fields, tables, and so on in the statement. See if these fields or tables are in the database. If the table name and column name are not accurate, the database will feedback the error message to the client.

So, sometimes when we write SELECT statements, if the syntax and table names or column names are wrong, then the system is prompted to say syntax errors, wait until the syntax is completely correct, and then prompted to say the column name or table name error. If you can master this order, you can save time when the application is in the wrong row.