Processing SQL Statements, SQL query Process Mechanism

Hey Folks,

We will take a quick review here about SQL query Processing phases which is used by ORACLE internally when we execute any  SQL statement  in Oracle Database.

Processing SQL Statements :

• Connect to an Instance using :
• User Process 
• Server Process
• The Oracle Server components that are used depend on the type of SQL statement :
• Queries Return Rows
• DML statement log changes
• Commit ensures transaction recovery
• Some Oracle Server components do not participate in SQL statement Processing.

Processing a query  : Below is the different Phases  comes in Picture for Query Execution : 

• Parse : 
• Search for identical statement
• Check syntax, Object names, and Privileges
• Lock Objects used during parse
• Create and Store Executions Plan
• Bind : 
• Obtain values for variables
• Execute : 
• Process SQL statement
• Fetch:
• Return Rows to user process

 

Processing a DML Statement :

• Parse : Same as the parse phase used in processing a Query.
• Bind  : Same as the Bind Phase used for Processing a query
• Execute : Here In Execution phase some internal operations performed by an Oracle :
• If the data and undo blocks are not already in the database buffer cache, the Server Process reads them from the data files into the database buffer cache.
• The Server process places  locks on the rows that are to be modified. The Undo blocks is used to store the before image of the data, So that the DML statements can be rolled back if necessary.
• The Data blocks records 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 database buffer cache. Any changed block in the Database Buffer Cache are marked as Dirty Buffers. That is, Buffer 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 value in 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.

 

Thank You !!