SQL statement execution in Oracle

[From: http://blog.csdn.net/wzy0623/archive/2009/09/27/4599615.aspx]

What operations does oracle perform when we submit an SQL statement?
Oracle assigns a server process to each user process: service process (dedicated server and shared server should be distinguished in actual situations). When service process receives the SQL statement submitted by the user process, the server process performs syntax and lexical analysis on SQL statements.
Glossary:
Syntax analysis: the statement is correct.
Lexical Analysis: checks the checklists, indexes, views, and user permissions in the data dictionary.
After the check is passed, the server process converts the SQL statement into an ascii code and generates a hash value using a hash function, the server process checks whether the hash exists in the share pool. If yes, the server process reads the resolved statements from the pool for execution. If no, perform the following steps: generate the execution plan and generate the Execution Code (please understand what is the execution plan ). After the SQL statement is parsed, Oracle stores the code, hash value, compiled code, execution plan, and all statistics related to the statement in the explain pool.

Note: 
1. Try to write the same SQL statement as much as possible, because even changes in the table sequence, query field location, or even case sensitivity in the from statement will prompt oracle to perform a hard parsing again.
2. Increasing the size of pai_pool_size can retain more SQL statement execution plans cached in the memory, which also increases the possibility of sharing SQL statements.

After the compilation code is generated, service process tries to read related cache data from db_buffer.

The following two cases are described:
1 db_buffer does not contain memory data: service process first requests some row locks in the table's header. After the application is successful, it reads the first block of these rows into db_buffer. If db_buffer has insufficient free space, the write operation-dbwr will be triggered. If the remaining space of db_buffer is insufficient to store new data, the dbwr process will be triggered to write dirty data in db_buffer to the data file. New data is written into the vacated space.
Note: db_block is the smallest logical unit in Oracle. Even if the data we require is only one or several rows of many rows contained in a block, we still need to read the entire block into db_buffer. The size of db_block can be set to an integer multiple of 8 K, and different sizes of db_block_size can be set for different tablespaces. Generally, we recommend that you set db_block_size to a greater value for tables with multiple select statements, DML operations require a smaller number of table settings.
2 dbwr is a data writing process. Apart from the insufficient db_buffer space, the ckpt process also triggers the dbwr event.

Supplement:
Segment 1 is the smallest expansion unit of oracle.
2 ckpt process: Checkpoint Process. Write the SCN into the log file, control file, data file header, and data block header. The events that trigger the ckpt process include alter system checkpoint, alter tablespace offline/begin back up, and normal shutdown database.
3 SCN:, system change number, or use system commit number. The SCN number is the logical clock sign of Oracle. It can be understood that it will change only when it is committed. The SCN number is an important indicator of Data Consistency. The data consistency for Oracle backup recovery is determined by the SCN.
After the block is read into db_buffer, service process writes the SCN number and changed row data in the block header to the rollback segment. When a user or Oracle rolls back data, the data is rolled forward through the rollback segment and the current data block.

Explanation:
Rollback segments are used to save the original image data for data modification. They are used to maintain read consistency during concurrent operations and implement rollback. If the rollback segment is too small, the snapshot is too old. 9i provides a dedicated undo tablespace, which is much easier to adjust at the tablespace level than to adjust the rollback segment.

Note:
Insert operation: the rollback segment only needs to record the rowid. If it is rolled back, you only need to delete the record according to the rowid;
Update operation: the rollback segment only needs to record the pre-image value of the changed field, and overwrite the updated value with the pre-image value during rollback;
Delete operation: the rollback segment records the data of the entire row and restores the data of the entire row during rollback;
When processing imp/exp or a large number of transactions, you need to create a large rollback segment for the current transaction and offline other rollback segments.
Then oracle generates logs, server process writes the modified rowid, pre-modification value, modified value, scn information, and related information in the rollback segment to the redo log buffer, when the following operations are performed, LGWr writes the data in the redo log buffer to the online redo on the disk: time exceeds 1 s, occupied redo log buffer space exceeds 1/3, Checkpoint Process, alter switch logfile and DBWr process before.

Note:
1. Data Writing sequence in oracle: 1 reading db_buffer; 2 writing rollback segments; 3 writing redo log buffer; 4 rewriting db_buffer; 5 writing log files; 6 writing data files;
Commit does not trigger the DBWr process, that is, it does not write data. commit only triggers the log write operation and write the scn number. However, any dml statement will generate logs.
When an online log file is full, LGWr writes the next online log. Remember that online logs are written cyclically, while control files are written concurrently. If the archive mode is set, the archiving process writes the previous online log to the archive file.

2 db_buffer contains memory data: first, determine the type of operation performed by the user.
Select Operation: first, judge whether there is a transaction in the data block header in db_buffer. If yes, it means that the data in the data block is being processed by the transaction, and the rollback segment stores the pre-image of the data, server price uses the data in the rollback segment for read consistency reconstruction. If no transaction exists in the data block header, the data may have been processed by the transaction but remains in db_buffer, in this case, the scn numbers in the data block headers in the select statement and db_buffer are compared. If the former is smaller than the latter, the data has been changed. The same is true for data processing, if the former is greater than or equal to the latter, the data is non-dirty data and can be directly read.
Update operation: No matter whether a transaction exists in the data block header or whether it is large or small between the two scn numbers, you must apply for a row lock from the server process to the table header. If the application is successful, continue the operation, if the lock fails, wait until the lock is successful.