Shulou(Shulou.com)06/01 Report--

We all know that Oracle is queried or modified through SQL statements. So what processes does SQL go through starting with client-side execution? Which SGA? How did you get into the data file? Please look down.

Case 1: query select in the database

As shown in the figure:

The ① client enters the SQL,SQL statement to reach the database instance through the network, the foreground process (server process) accepts the SQL statement, and PGA allocates a section of space to the foreground process.

② parses the SQL statement to an "execution plan" before it can execute

Parsing this part consumes a lot of resources: CPU and Icano.

Both the SQL statement and the execution plan are cached in the shared pool (shared pool)

③ looks for data in the database buffer (buffer cache) and dbf files through the generated execution plan

This process involves database buffers, which are used to cache dbf data. So the first step is to see if there is any data from the database buffer, then get it from the database buffer, and if not, get it from the dbf file. The data in dbf is first transferred to the database buffer. The data is then returned to the client through the foreground process.

All ① ② ③ processes are completed by foreground processes.

Case 2: modify the data of a table

① is like a query, the client will require the foreground process to transfer to the shared pool, and then read the data to the database buffer. If there is no data in the database buffer, then look for it in the dbf file, and the data in dbf will be transferred to the database buffer and read by the foreground process.

The ② foreground process modifies and commits the table in the database buffer, at this time, a log is generated. The generated log is written to the log buffer.

After ③ modifies the data in the database buffer, the data in memory is inconsistent with the data in the data file. The data in memory needs to be written to the data file. This process is done by the database writing process.

The logs generated by the log buffer in ④ memory need to be written to the log file, which is done by the log writing process.

-end-

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.