Statement execution procedure for SQL

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

When we execute the SQL statement on the client, the client sends the SQL statement to the server side, allowing the server-side process to process the statement. That is, the Oracle client does nothing, and his main task is to send some SQL statements generated by the client to the server side. After the server process receives the information from the user process, the process allocates the required memory in the PGA, storing the relevant information, such as the login information associated with the session memory store.

Although there is a database process on the client side, the role of this process is different from the process on the server, and the database process on the server processes the SQL statement. However, there is a problem to note that the client process and the server process is one by one corresponding. In other words, after the client connects to the server, a process is formed between the client and the server, and we are called the client process on the client side, and we are called the server process on the server.

Step Two: Statement parsing

When the client sends the SQL statement to the server, the server process parses the statement. This parsing work is done on the server side, and the analytic action can be divided into a lot of little gestures.

1) query cache (library cache)

The server process does not go directly to the database query when it receives the SQL statement that the client sends over. The server process converts the character of the SQL statement into an ASCII equivalent digital code, which is then passed to a hash function and returns a hash value, then the server process will go to the library in the shared pool Cache to find out if the same hash value exists. If present, the server process will execute with the parsed version of this statement cached in the library cache of the shared pool, eliminating the subsequent parsing effort, which is soft parsing. If the adjustment cache does not exist, then the following steps are required, which is hard parsing. Hard parsing is usually an expensive operation that accounts for about 70% of the total SQL execution time, and hard parsing generates execution trees, execution plans, and so on.

Therefore, the use of high-speed data cache, you can improve the query efficiency of SQL statements. There are two reasons for this: reading data from memory is more efficient than reading from a data file on a hard disk, and saving time by avoiding statement parsing.

However, it is important to note that this data cache is not the same as the data cache of some client software. Some client software sets up the data cache on the client side of the application in order to improve query efficiency. Due to the existence of these data caches, the query efficiency of the client application software can be improved. However, if the other person in the server made the relevant changes, due to the application of the data cache, the data can not be reflected in the changes in time to the client. From this, it can be seen that the data cache of the application software is not matter with the database server's high-speed data cache.

2) statement legality check (data dict cache)

When the corresponding SQL statement is not found in the cache, the server process begins to check the validity of the statement. The main point here is 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, it will feed the error back to the client. During this syntax check, the table names, column names, etc. contained in the SQL statement are not checked, but the syntax is checked.

3) language meaning check (data dict cache)

If the SQL statement conforms to the syntax definition, then the server process will parse the table, index, view and other objects involved in the statement, and check the names of these objects and the related structure against the data dictionary to see if the fields, tables, views, etc. are in the database. If the table name and column name are inaccurate, the database will feed back the error message to the client.

So, sometimes when we write a SELECT statement, if the syntax and table name or column name at the same time, the system is prompted to say syntax error, wait until the syntax is correct, then prompt to say that the column name or table name is wrong.

4) Get Object resolution lock (Control Structer)

When the syntax and semantics are correct, the system will lock the objects we need to query. This is mainly to ensure the consistency of the data, to prevent us in the process of the query, other users of the structure of this object changes.

5) Verification of data access rights (Dict cache)

When the syntax, semantics through the check, the client is not necessarily able to obtain data, the server process will also check whether the connecting user has access to the data. If the user does not have access to data, the client will not be able to obtain the data. Note that the database server process checks the syntax and semantics before it checks access rights.

6) Determine the best execution plan

When the syntax and semantics do not have a problem with permissions, the server process does not query the database files directly. The server process optimizes the statement according to certain rules. There is a one-step query transformation before the execution of the plan, such as: view merging, subquery nesting, pre-predicate and materialized view rewrite query, etc. To determine which execution plan to use, Oracle also needs to collect statistical information to determine the access-junction method for the table, and ultimately determine the lowest possible cost of execution plan.

However, it is important to note that this optimization is limited. Generally in the process of application software development, the SQL statements of the database need to be optimized, the role of this optimization is much larger than the server process of self-optimization.

When the optimizer of the server process determines the best execution plan for this query statement, the SQL statement is saved with the execution plan to the data cache (library cache). So, when there is this query, we omit the syntax, semantics, and permission check steps, and execute SQL statements directly to improve the efficiency of SQL statement processing.

Third Step: Binding variable Assignment

If a binding variable is used in the SQL statement, the declaration of the bound variable is scanned, the binding variable is assigned a value, and the value of the variable is brought into the execution plan. If SQL is present in the cache at the first step of parsing, skip to that step directly.

Fourth step: statement execution

Statement parsing is simply parsing the syntax of the SQL statement to make sure that the server knows exactly what the statement is meant to say. The database server process does not actually execute this SQL statement until the statement resolution is complete.

for Select statement:

1) First, the server process to determine whether the required data in DB buffer exists, if present and available, then directly obtain the data rather than from the database file to query data, and based on the LRU algorithm to increase its access count;

2) If the data is not in the buffer, the server process will query the data from the database file and put the data into the data buffer (buffer cache).

Where the data exists in DB buffer, its usability is checked by checking if there is a transaction in the head of the DB buffer block, reading the data from the rollback segment if there is a transaction, or, if there is no transaction, comparing the SCN of the Select and the SCN for the size of the DB buffer, if the former is less than the latter , the data is still read from the rollback segment, and if the former is greater than the latter, it is a non-dirty cache that can read the contents of the DB buffer block directly.

for DML Statement (Insert , delete , update ):

1) Check that the required database has been read into the buffer cache. If the buffer cache already exists, perform step 3 directly;

2) If the required database is not in the buffer cache, the server reads the data block from the data file into the buffer cache;

3) Lock the data row (row Exclusive lock) for the table that you want to modify, and then obtain an exclusive lock on the data row that needs to be modified;

4) Copy the redo record of the data to the redo log buffer;

5) undo data to produce data modification;

6) Modify the DB buffer;

7) DBWR will be modified to write to the data file;

In the 2nd step, the server reads the data from the data file to the DB buffer through the following steps:

1) First the server process will request a TM lock on the table header (to ensure that other users cannot modify the structure of the table), if the TM lock is successfully added, and then request some row-level lock (TX lock), if the TM, TX locks are successfully locked, then start reading data from the data file.

2) before reading the data, first prepare the buffer space for the read file. The server process needs to scan the LRU list for free db buffer, and during the scan, the server process will register all discovered db buffer in the dirty list. If free DB buffer and non-dirty block buffers are insufficient, the DBWR will be triggered to write the buffer block pointed to in dirty buffer to the data file, and purge these buffers to make room to buffer the newly read data.

3) to find enough free buffer, the server process will read the data file into each block of data (db block) (db block is the smallest operating unit of Oracle, even if the data you want is just one or a few rows in many rows in the DB block, Oracle will also read all the rows in this DB block into Oracle DB buffer in an idle area in db buffer or overwrite a non-dirty block buffer that has been extruded from the LRU list, and is arranged in the head of the LRU table, that is, the data block into the DB Before buffer, it is also necessary to apply the latch in DB buffer before the data can be read to DB buffer after the lock is successfully added.

If the data block already exists in the DB buffer cache (sometimes referred to as DB buffer or DB cache), even if a non-transaction is found in DB buffer and the SCN is smaller than its own, the server process still has to request a lock on this record to the head of the table. The locking succeeds in subsequent actions, and if unsuccessful, waits for the previous process to be unlocked before the action can be performed (this time the block is blocked by a TX lock).

In the redo log, the specific steps are as follows:

1) After the data has been read into DB buffer, the server process writes the ROWID of the row data that is affected by the statement and is read into the DB buffer, and the original value and the new value to be updated and the SCN are written to redo log buffer from the PGA. Before writing to the redo log buffer, the latch of redo log buffer must be requested beforehand, and the write will not begin until the lock is successfully locked.

2) When writing to a size of redo log buffer of One-third or write to 1M or more than three seconds or a checkpoint occurs or before DBWR occurs, the LGWR process is triggered to write the data redo log buffer to the disk redo File (this time generates a log file sync wait event).

3) The latches held by redo log buffer that have been written to redo file are freed and can be overwritten with subsequent write information, redo log buffer is used for recycling. Redo file is also used for recycling, and when a Redo file is full, the LGWR process automatically switches to the next Redo file (at this point the log file switch may appear waiting for the event). In the case of archive mode, the archive process also writes the contents of the previous redo file to the archive log file (a log file switch (archiving needed) may appear at this time.

When setting up undo information for a transaction, the steps are as follows:

1) After completing the relevant redo log buffer, the server process begins overwriting the block header transaction list for this DB buffer and writes the SCN (the SCN was written in redo log buffer, not written in db buffer).

2) copy of the header transaction list containing the block and the data copy of the SCN information are then placed in the rollback segment, where the information in the rollback segment is referred to as the "pre-image" of the data block, which is used for future rollback, recovery, and consistent reads. (The rollback segment can be stored in a dedicated rollback table space, which consists of one or more physical files and is designed to roll back the table space, and the rollback segment can also be opened in data files in other tablespaces).

When the modification information is written to the data file, the steps are as follows:

1) Overwrite the data contents of the DB buffer block and write the address of the rollback segment at the head of the block.

2) Place the DB buffer pointer into the dirty list. If a row of data is update multiple times without a commit, there will be more than one "pre-image" in the rollback segment, with the SCN information and the "Pre-image" rollback segment address in addition to the first pre-image containing SCN information. An update only corresponds to one SCN, and then the server process creates a pointer to this DB buffer block in the dirty list (a convenient DBWR process can find the DB buffer data block of the dirty list and write to the data file). The server process then continues to read the second block of data from the data file, repeating the previous block's actions, reading the data block, logging, setting up the rollback segment, modifying the data block, and placing the dirty list.

3) When the length of the dirty queue reaches the threshold (typically 25%), the server process notifies DBWR to write out the dirty data, releasing the latch on the DB buffer and freeing up more free db buffer. It's always been the same. Oracle reads a block of data at a time, in fact, Oracle can read multiple blocks at a time (Db_file_multiblock_read_count to set the number of read-in blocks)

When executing a commit, the steps are as follows:

1) Commit triggers the LGWR process, but does not force the DBWR to immediately release all the corresponding DB buffer blocks of the lock. In other words, it is possible that the DBWR is still writing the block of data involved in the SQL statement for a later period of time, although it is already a commit. The row lock of the table header is not released immediately after the commit, but is not released until the DBWR process is complete, which may occur when a user requests that another user has failed to commit the resource.

2) The time between the end of commit and DBWR process is very short, if happen after commit, DBWR not end before the power outage, because the data after commit is already the content of the data file, but this part of the file is not fully written to the data file. So you need to roll forward. Since commit has triggered LGWR, these changes that have not yet been written to the data file will be rolled forward by the Smon process based on the Redo log file after the instance is restarted, completing the unfinished work of the commit (i.e. writing the changes to the data file).

3) If no commit is lost, because the data has been changed in db buffer, there is no commit, this part of the data does not belong to the data file. Since DBWR is triggered before LGWR that is, as long as the data changes, (must first have log) all DBWR changes on the data file will be recorded in the Redo log file first, after the instance restarts, the Smon process is then rolled back and forth according to the Redo log file.

In fact, Smon roll rollback is done according to Checkpoint, when a full checkpoint occurs, let the LGWR process first write all buffers in Redologbuffer (contains uncommitted redo information) to the Redo log file, Then let the DBWR process write the dbbuffer committed buffer to the data file (without forcing the uncommitted). The SCN on the head of the control file and data file is then updated to indicate that the current database is consistent and that there are many transactions between the adjacent two checkpoints, both committed and uncommitted.

When executing rollback, the steps are as follows:

The server process finds the corresponding pre-modified copy in the rollback segment based on the transaction list and the SCN and the rollback segment address of the block's header in the data file block and DB buffer, and restores the modified but uncommitted changes in the current data file with these original values. If there are multiple "pre-images", the server process finds the rollback segment address of the front-end image at the head of a "pre-image", until it finds the oldest "pre-image" under the same transaction. Once a commit is issued, the user cannot rollback, which makes the subsequent action of the DBWR process not fully completed after the commit has been guaranteed.

Fifth Step: Extract data

When the statement executes, the queried data is still in the server process and has not been routed to the client's user process. So, in the server-side process, there is a piece of code that is dedicated to extracting data. His role is to return the results of the query to the client process, thus completing the entire query action.

Statement execution procedure for SQL