Internal principles of Oracle update

Use Oracle internal principles to describe the following procedures:

1, Sqlplus USER/PASSWD@ORCL

2, update t set a=1 where b= ' SS ';

3, commit;

4, exit

1, Sqlplus USER/PASSWD@ORCL

(1), Client Sqlplus connection monitoring

(2), listening to receive connection requests, fork an Oracle process, as a server process, while allocating a small piece of PGA memory.

(3), the server process and the client establish a connection.

(4), server process (hereinafter referred to as process) query user$, profile$ and other data dictionaries, verify that users can log in.

(5), if you can log in, connection creation completed.

2, update t set a=1 where b= ' SS ';

(i), if no index on column B

(1), query seg$ and other data dictionary, find T-section Head

(2) Read the extent Map from the section head and start the full scan

(3), to find the first line to meet the conditions, to modify

(4), find the same piece of the remaining lines, first construct a CR block, in the CR block to continue to find, if you find the line to meet the conditions, in the Xcur block modified.

(b) If there is an index on column B and the version is not 11GR1 (10G, 11GR2), you do not need to construct a CR block

(c), b column regardless of whether there is an index, in the 11GR1 need to construct a CR block.

(iv) If there is an index on column A, increase the index maintenance steps:

(1), first delete the original index block to modify the value

(2), and then insert the new value

(v) Any modification of the block has the following steps (non-IMU)

(1), in the PGA Generation Undo Segment Header Transaction table after image (5.2)

(2), the back image of the undo block generated in the PGA (5.1)

(3), in the PGA to generate DataBlock block of the back image (11.9)

(4), the first three redo vector as a redo recorder write log buffer

(5), modify the transaction table of the Undo section, the transaction begins formally.

(6), modify the undo block, write the datablock image.

(7), modify the DataBlock, the new value "1" to write buffer cache.

(vi), the modification of any block, there are the following steps (IMU)

(1), in the PGA to generate DataBlock block of the back image (11.9)

(2), in the PGA Generation Undo Segment Header Transaction table after image (5.2)

(3), the back image of the undo block generated in the PGA (5.1)

(4), the first three redo vectors as a redo recorder written to the private strand in the shared pool.

(5) writes the DataBlock image value in the IMU to the pool in the shared pool.

(6), modify the transaction table of the undo segment header.

(7), modify the undo block, write the datablock image.

(8), modify the DataBlock, the new value "1" to write buffer cache.

3, commit;

(i), non-IMU (by the most common quick submissions):

(1), in the PGA generated commit redo information (number 5.4), another as a redo recorder, write log buffer

(2), modify the corresponding slot of the transaction table, declare that the transaction has been committed.

(3), modify DataBlock, write the quick submit flag and SCN in ITL slot. The row on each line is locked 0.

(4), notify LGWR, log buffer to write to redo file.

(5), received LGWR notification, write completed.

(6), to the user to send the completion of the submission of information.

(i), under IMU (by the most frequent quick submissions):

(1) to generate a commit's redo information (number 5.4) in the PGA, passing in the private strand in the shared pool, appended to the redo recorder before the transaction.

(2), modify the corresponding slot of the transaction table, declare that the transaction has been committed.

(3), modify DataBlock, write the quick submit flag and SCN in ITL slot. The row on each line is locked 0.

(4) writes the redo data in the private strand to log buffer.

(5), notify LGWR, log buffer to write to redo file.

(6), received LGWR notification, write completed.

(7), to the user to send the completion of the submission of information.

4, exit

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

(1), disconnect, abort the server process, release the PGA

It's just an outline, no locks, Latch, mutexes, etc.

is not too perfect, the SQL parsing section does not mention

Update A where id=1;

Update A where id=2;

Commit

Non-IMU under:

----------------The first redo Recoder started-----------------

Transaction start

Update A where id=1 corresponds to the redo of the undo

Update A where id=1 corresponds to the redo of the data block

----------------The first redo Recoder end-----------------

----------------The second redo Recoder start-----------------

Update A where id=2 corresponds to the redo of the undo

Update A where id=2 corresponds to the redo of the data block

----------------The second redo Recoder end-----------------

----------------The third redo Recoder start-----------------

Commit Transaction End

----------------The third article Redo Recoder end-----------------

Under the IMU, a total of 3 redo Recoder will be generated, that is, redo entries will increase by 3.

And under the IMU, it's like this:

----------------The first redo Recoder started-----------------

Update A where id=1 corresponds to the redo of the data block

Transaction start

Update A where id=2 corresponds to the redo of the data block

Commit Transaction End

Update A where id=1 corresponds to the redo of the undo

Update A where id=2 corresponds to the redo of the undo

----------------The first redo Recoder end-----------------