SQL Statements are processed differently depending on whether the statement is a query, data manipulation language (DML) to update, insert, or delete a row, or data definition language (DDL) to write information to the data dictionary.
Processing a query:
- Parse:
- Search for identical statement in the Shared SQL Area.
- Check syntax, object names, and privileges.
- Lock objects used during parse.
- Create and store execution plan.
- Bind: Obtains values for variables.
- Execute: Process statement.
- Fetch: Return rows to user process.
Processing a DML statement:
- Parse: Same as the parse phase used for processing a query.
- Bind: Same as the bind phase used for processing a query.
- Execute:
- If the data and undo blocks are not already in the Database Buffer Cache, the server process reads them from the datafiles into the Database Buffer Cache.
- The server process places locks on the rows that are to be modified. The undo block is used to store the before image of the data, so that the DML statements can be rolled back if necessary.
- The data blocks record the new values of the data.
- The server process records the before image to the undo block and updates the data block. Both of these changes are made in the Database Buffer Cache. Any changed blocks in the Database Buffer Cache are marked as dirty buffers. That is, buffers that are not the same as the corresponding blocks on the disk.
- The processing of a DELETE or INSERT command uses similar steps. The before image for a DELETE contains the column values in the deleted row, and the before image of an INSERT contains the row location information.
Processing a DDL statement:
- The execution of DDL (Data Definition Language) statements differs from the execution of DML (Data Manipulation Language) statements and queries, because the success of a DDL statement requires write access to the data dictionary.
- For these statements, parsing actually includes parsing, data dictionary lookup, and execution. Transaction management, session management, and system management SQL statements are processed using the parse and execute stages. To re-execute them, simply perform another execute.
