SQL - statement internal work flow

SELECT statement internal work flow

 

CLIENT - >

            Client executes the sql statement in client side it checks the syntax after that it will send to the server process.

HASH Values ->

            Once server receive the request from the sql statement it will check the HASH value its nothing the HEXA decimal value. Server process pass the value in SGA->shared pool library cache.

            HASH value mapped with SQL area the Given statement already executed by the user it read the data in buffer cache and it provide result to the user.

            If the hash value not available in sql area it will generate new HASH value and create sql area for the statement .

 

PARSE  -> hard & soft

            Soft -  if statement alredy processed.

            Hard – first time of statement execution.

            SQL area created the sever process do the PARSING.

            Syntax -> check the spelling and grammar check of the statement.

            Semantic - >check the object or table exits in the database.

            Privileges  ->in that shared pool the dictionary cache check the user have permission to access the table or record.

 

Execution plan ->

            Parsing once completed the execution plan be created.

            Read the statement if it already executed it give the result to the client if doesn’t executed it will read the files in data file and stored in buffer cache. Most recently search query result are stored in the buffer cache.

Result ->

            Finally server process give the result for the sql statement to the client.

 

 

Delete Statement Internal work flow

Step 1: 

When user issues a delete command server process receive the delete statement from client and before executing delete statement server process required the validation check of the delete statement. Sever process check the syntax and semantic of the delete statement whether the statement is valid or not.

Syntax validation:

Syntax validates check means whether the sql statement is correct or not

Semantic Validation:

Semantic validate check means whether database object is exist or not in database server.

Step 2: 

After pass the syntax / Semantic check then server process go to the library cache and server process will search the delete statement in library cache for match the statement.

If statement match in library cache server process use this statement and didn’t generate an execution plan this is called soft parse.

delete statement don’t match in library cache then server process will generate multiple execution plan and this process is called hard parse.

Step 3: 

After parsing phase server process execution of the delete will began. Server process will bring the requested data block from corresponding datafile of the table into database buffer cache whose rows must be deleted.

Step 4:

Blocks  contain the original data of the table. As the same time server process bring same number of empty block from the rollback/undo tablespace into the database buffer cache.

Server process will copy the original data block from the userdata block into the empty rollback/undo block and create a before image. This rollback/undo block will be reserve for the current transaction and no other server process can’t use the rollback/undo block until current transaction is completed.

Besides, userdata block in database cache will be lock no other server process can’t perform delete, update operation until current statement is being completed. This scenario is called row level lock.

Step 5: 

Server process will bring the set of user data blocks into PGA. Once filter process perform selected rows will be deleted that means data will be removed from original data blocks. This process will continue till all the userdata blocks has been checked and removed.

Step 6: 

if user issue commits oracle background process logwriter immediately writes all changes redo log buffer to disk and SCN number will generate.

At the same time database buffer cache and rollback/undo will be release. During the next checkpoint dbwriter will write the data back to the respective datafile. If user issue rollback original data will be copy from rollback/undo into database buffer cache and userdata will be same as before.