Procedures for Oracle to execute SQL statements

Reprint to: http://blog.csdn.net/aqszhuaihuai/article/details/7024551

What does Oracle do when we submit an SQL statement?

Oracle assigns a server process to each user process: service process (which should differentiate between dedicated and shared servers) when the service process receives the SQL statement submitted by the user process. The server process makes syntax and lexical analysis of SQL statements.

Noun Explanation:

Parsing: The statement itself is correct.

Lexical Analysis: Check tables, indexes, views, and user permissions against the data dictionary.

After the check passes, the server process turns the SQL statement into ASCII code and generates a hash value from the ASCII code through a hash function, and the server process queries the share pool for the existence of the hash and, if present, the server process from share The pool reads the parsed statement to execute; if it does not exist, you need to do the following steps: Build the execution plan and generate the execution code (understand what the execution plan is). When parsing is complete, Oracle stores the SQL statement itself code, hash value, compiled code, execution plan, and all statistics related to the statement in the share pool.

Attention:

1 write the same SQL statements as much as possible, because even the change in the table order in the FROM statement, the change in the location of the query field, or even the difference in case, will cause Oracle to redo the hard parse.

2 increasing shared_pool_size can preserve more cached SQL statement execution plans in memory, which also means an increase in the likelihood of sharing SQL.

After generating the compiled code, the service process attempts to read from the Db_buffer whether there is a related cache data.

Here are two things to illustrate:

1 Db_buffer does not contain memory data: Service process first requests some row locks on the head of the table, and after the request succeeds, reads the first block of these rows into the Db_buffer. If Db_buffer free space is insufficient, the write operation-DBWR is triggered. If the db_buffer remaining space is not enough to store the new data, the DBWR process is triggered and the dirty data in Db_buffer is written to the data file. Frees up space to write new data.

Note: Db_block is the smallest logical unit of Oracle, and even if the data we require is just one or a few rows in the many rows contained in a block, we still need to read the entire block into the db_buffer. The size of the Db_block can be set to an integer multiple of 8k, and the size of different db_block_size can be set for different table spaces, and it is generally recommended that the db_block_size be set larger on the Select multiple table, while the DML operations are much smaller on the table.

2 DBWR is the write data process, the event that triggers the DBWR process is not enough db_buffer space, the CKPT process is also the event that triggers the DBWR.

Add:

The 1 segment is the smallest expansion unit of Oracle.

2 CKPT Process: Checkpoint process. The SCN is written to the log file, control file, data file header, data block head. Events that trigger the CKPT process have an alter system Checkpoint,alter tablespace Offline/begin back up and a normal shutdown database.

3 Scn:system change number or use system commit number. The SCN number is the logical clock sign of Oracle, which we can understand to change at commit. The SCN number is an important sign of maintaining data consistency, and Oracle's data coherency for backup recovery is judged by the SCN.

When the block reads into the Db_buffer, the service process writes the SCN number and the changed row data to the rollback segment of the header. When the user or Oracle rolls back the data, it implements the forward rollback of the data through the rollback segment and the current data block.

Explain:

The rollback segment is used to hold the pre-image data of the modified data, to maintain read consistency during concurrent operations, to implement rollback, and so on. A rollback segment is too small to cause an old snapshot error. The 9i provides a dedicated undo table space, which is obviously much easier to resize at the table space level than to adjust the rollback segment.

Attention:

Insert operation: Rollback segment only need to record rowid, if fallback, simply delete the record according to rowID;

Update operation: The rollback segment only needs to record the pre-image values of the fields that have changed, overwriting the updated values with the previous image values when rolling back;

Delete operation: The rollback segment records the entire row of data, rollback the entire row of data recovery;

When doing imp/exp or bulk transactions, you need to create a large rollback segment for the current transaction and offline the other rollback segments.

Oracle then generates the log, and the server process writes the ROWID of the modified data, the pre-modified value, the modified value, the SCN information, and the relevant information in the rollback segment to redo log buffer, LGWR redo log when the following actions occur Data in buffer is written to online redo on disk: Time exceeds 3s, occupies redo log buffer space exceeding 1/3, checkpoint process, alter switch logfile, and DBWR process.

Attention:

1 The order of write data in Oracle is: 1 read into db_buffer;2 write rollback segment, 3 write redo log buffer;4 overwrite db_buffer;5 write log file; 6 write data file;

Commit does not trigger the DBWR process, that is, no data is written, and commits only trigger the write log operation and write the SCN number. However, any DML statement will generate a log.

When an online log file is full, LGWR writes the next online log, keeping in mind that the online logs are circular and that the control files are concurrent writes. If set to archive mode, the archive process writes the previous online log to the archive file.

2 Db_buffer contains memory data: First determine what type of action the user is performing.

Select operation: First determine whether the data block header in the Db_buffer has a transaction, if any, the data in the data block is being transacted, the rollback segment is stored in the pre-image of the data, and the server process uses the data in the rollback segment for read consistency refactoring If the data block header does not have a transaction, it is possible that the data has been transacted but still remain in Db_buffer, which compares the SCN number in the SELECT statement with the SCN number of the data in the Db_buffer, which is less than the latter, indicating that the data has changed. Processing the data above, if the former is greater than or equal to the latter, then the data is non-dirty data, directly read.

Update operation: Regardless of the data block header whether there is a transaction, or the SCN number between the big or small, requires the server process to the table header request row lock, the request to continue the success of the operation, unsuccessful will wait to lock until successful.

Procedures for Oracle to execute SQL statements