1. As a laymen, consider a user/application requests a connection request to the Oracle database through his login credential.
2. This login credential is accepted by the Listener Process which is present on the server side.
3. PMON checks the authenticity of the user in the data dictionary cache of the Shared Pool. If the detail of user is found in the data dictionary then PMON creates a server process and allocate a memory area PGA to that user and sends acknowledgement for successful connection to user.
If PMON didn't find the detail in the data dictionary cache, it checks in the datafiles and if details found a copy of that is placed in the data dictionary cache and user is acknowledged for successful connection. If details are not found in the datafile then PMON sends acknowledgement of unsuccessful connection to user.
6. When Oracle receives sql/insert query, it requires to run some pre-tasks before actually being able to really run the query.
During parsing, Database validate the syntax of the statement whether the query is valid or not.
Database validate the semantic of the statement. It checks whether a statement is meaningful or not.
If syntax/Semantic check pass, then server process will continue execution of the query. The server process will go to the library cache. In the library cache the server process will search from the MRU (Most Recently Used) end to the LRU (Least Recently Used) end for a match for the sql statement. It does this by using a hash algorithm that returns a hash value. If the hash value of the query we have written matches with that of the query in library cache then server process need not generate an execution plan (soft parsing) but if no match is found then server process has to proceed with the generation of execution plan (hard parsing).
7. As already mentioned above about semantic check, this check is done in the data dictionary cache by the server process. Server process will check the definition of the object, if that is already available within the data dictionary cache, server process will process the check. If not available then server process will retrieve required information from the system tablespace.
8. If the submitted sql statement is the same as a reusable SQL statement in the shared pool, then Oracle Database reuses the existing code. This is called Soft parse.
9. Oracle first checks whether the necessary data is already in the data buffer cache. If not, the server process reads the necessary table data from the datafiles on disk.
Once the execution plan generates by the optimizer, then the server process will pick the best possible and cost effective execution plan and go to the library cache.
10. Server process will keep the execution plan along with the original sql text in the library cache. Here the parsing ends and the execution of sql statement will start.
11. Server process will keep the plan in the library cache on the MRU (Most Recently Used) end after generation of execution plan. Then the plan is picked up and execution of the insert job will start.
12. Server process will bring empty blocks from respective datafile of tablespace in which table exist and into which rows will be inserted. Blocks will be brought into database buffer cache. Blocks does not contain any data.
13.Server process will bring same no of empty blocks from rollback/undo tablespace. Server process will copy the address of the actual data blocks of user data datafiles into the empty rollback/undo blocks.
14. Server process will bring set of user data blocks and data will be added from the insert sql statement into user data blocks.
15. The server process sends a message to the client process to indicate the successful completion of the INSERT operation. The message would be “COMMIT COMPLETE” in this case. (If it couldn’t complete the request successfully, it sends a message indicating the failure of the operation.)
16. Changes made to the table by the insertion may not be written to disk right away. The database writer process writes the changes in batches, so after the insert job completes in the database buffer cache, then database writer will write the data back to respective datafiles.
