Monday, November 17, 2008

Oracle - Stages in DML Statements

DML statements include SQL statements like delete, insert, and update which does data manipulation. Since these statements differ to what select statement mean for obviously these have different steps of execution.
For these DML statements which perform data manipulations there comes a new concept of UNDO segments that only store old values, not the new values.
The new values that are given or calculated through DML statements are stored in object itself.
Following are the steps that are said to be occurred when DML statements are mean to be executed.
1) Parse statement

This is a common statement for select query as well which is to prepare an execution plan or parse tree and store in shared pool (library cache). If such execution already exist in library cache RDBMS skips this step.

2) Execute statement

RDBMS performs all processing to execute the DML statement. Again insert statement from DML differs from update or delete. Insert statement does not need to fetch data from disk to buffer cache where as for update or delete server process has to retrieve from disk to perform update or delete actions.
While running delete or update, server process will fetch data from disk to buffer cache, implicitly acquire a lock on the data to be changed and then make specific data change in buffer cache. As performing manipulations on data in memory is faster than in disk.
A lock has to be acquired on the data because to avoid other users doing the same thing.
For insert statements, the server process need not retrieve data from disk to buffer cache but should retrieve a block from a disk that has enough space available to house the new row of data and places that new row into block.
Also a part of this process DML statement is writing old and new versions of data to the Undo log segment acquired for that transaction. A lock must be acquired on the undo segment to write changes to an undo segment as well.

3) Generate redo

There can be several action been performed by a user in his/her session which has to be stored into redo log buffer after these DML statements are executed. So that DBA can recover the data change if damage is later done to the disk files containing oracle data.

No comments: