Oracle internal operations

When we introduce a SQL statement, Oracle will do with it?
Oracle will be allocated for each user process to a server process: the service Process (actual distinction should be made ??between a dedicated server and shared server), when the service Process received sql statement submitted by the user process, the server process will SQL statement syntax and lexical analysis .
Syntax analysis: the correctness of the statement itself.
Lexical analysis: control check in the data dictionary tables, indexes, views, and user rights.
Check, the server processes the SQL statement into ascii code ascii code through a hash function to generate a hash value, the server process to sharepool query the existence of this hash, if it exists, the server process from sharepool read has been parsed statement to perform; if it does not exist, you need to do the following steps: generating an execution plan and generate perform encoding (please understand what the implementation plan). The completion of the analysis after the Oracle will sql code of the statement itself, the hash value, compiled code, implementation plans and with the statement statistics stored to sharepool.

1 try to write the same SQL statement, because even from the table sequence changes in the statement, changes in the location of the query field, or even just different case, will be prompted Oracle to re-do a hard parse.
The 2 increases share_pool_size can be retained more cache memory SQL statement execution plan, also means sharing to increase the possibility of SQL.


Service Process will attempt to read from the db_buffer the existence of the cached data compiled code is generated.

The following two cases to illustrate:
1 db_buffer without memory: serviceprocess first request line lock at the head of the table, the application is successful, these lines where the first block read db_buffer. The If db_buffer lack of free space is triggered write operation-DBWr. If the remaining db_buffer not enough space to store new data, it will trigger DBWR process will write dirty data db_buffer to the data file. The space vacated write new data.
Note: the Oracle db_block is the smallest logical unit, even if the required data is just a block contains a number of rows in a row or a few lines, we still need to read the entire block into the db_buffer. the smaller the size of the db_block can be set to an integer multiple of 8k different db_block_size size, and can be set for different table space is generally recommended to select on the table the db_block_size settings, DML operation on a table set .
2 DBWR is to write data process, events trigger DBWr process in addition to the enough db_buffer space CKPT process is the event that triggered the DBWr.


Section 1, the oracle smallest expansion units.
2 ckpt process: the checkpoint process. Scn written to the log files, control files, data files, data block head. The event trigger ckpt process alter systemcheckpoint alter tablespace offline / begin back up and normal shutdown database.
3 scn:, system change number or to use System commitnumber. SCN number is oracle logic clock flag, we can understand the commit will change. The Scn number is an important symbol to maintain data consistency, oracle backup and recovery of data consistency is through the SCN to determine.
the block read db_buffer service process will block head SCN number and changed lines of data written to the rollback segment. When users or Oracle rollback data is through the rollback segment and the current data block data move rollback.

Rollback segment is used to to save modify data before image data, is to maintain concurrent operation of read consistency to achieve rollback. Rollback segment is too small will lead to snapshot too old error. 9i provides a dedicated undo tablespace, it is clear that if the size of the table space level adjustment than adjust the rollback segments much easier.

Insert operation: rollback segment only need to record rowid, rolled back, just delete this record rowid;
Update operations: rollback segment only need to record changes in the field before image value rollback the updated value can be covered with before-image values;
Delete operation: rollback segment recorded the entire line of data recovery rollback the entire row of data;
Do imp / exp or high-volume transaction processing, you need to create a large rollback segment for the current transaction, and other rollback segments offline.
Oracle will then generate a log value before the serverprocess the modified data rowid, modify, modified value SCN information and rollback segment information is written redo logbuffer command when the following actions occur, LGWR will data is written to the redo log buffer the disk onlineredo: longer than 1s, to occupy the redo log buffer space of more than 1/3, checkpoint process, ALTER switchlogfile and the DBWR process before.


oracle in order to write data: 1 reads db_buffer; to write rollback segments; 3 write the redo log buffer; rewritten db_buffer; 5 write log files; 6 write data files;
commit does not trigger DBWr process, that does not write data commit triggers only write the log operation and write scn number. But any DML statement will produce a log.
When an online log file is filled, LGWR will write the next online log, keep in mind that the online log write cycle, the control file is concurrent write. If set to archive mode, before the archiving process will write online log archive.

The 2 db_buffer memory contains data: First, determine the type of operation performed by the user.
Select operation: First, determine the data block head db_buffer the existence of affairs, if there is, then the data in the data block is transaction processing, the first image of the data stored in the rollback segment, serverprice use rollback segment The the data read consistency reconstruction; transaction does not exist if the data block head, it is possible that the data has been the transaction is completed, but remained in db_buffer data block header, this time in the the scn number and db_buffer will select statement scn No. of the Ministry of the former than the latter indicates that the data has been changed, process the data above, if the former is greater than or equal to the latter, the data are non-dirty data can be read directly.
Update operation: regardless of the head of the block of data the existence of affairs, or SCN number, whichever is big or small, need server processes apply to the head of the table row lock, successful applicants continue unsuccessful wait for the lock until.