What Happens Behind SELECT statement

Working Behind SELECT statement

This is very Important Concept for a DBA perspective.

This wil be Helpful in All aspects

1. A user requests a connection to the Oracle server through a 3-tier or an n-tier web-based client using Oracle Net Services.

2. Upon validating the request, the server starts a new dedicated server process for that user.

3. The user executes a statement to insert a new row into a table.

4. Oracle checks the user’s privileges to make sure the user has the necessary rights to perform the insertion. If the user’s privilege information isn’t already in the library cache, it will have to be read from disk into that cache.

5. If the user has the requisite privileges, Oracle checks whether a previously executed SQL statement that’s similar to the one the user just issued is already in the shared pool. If there is, Oracle executes this version of the SQL; otherwise Oracle parses and executes the user’s SQL statement. Oracle then creates a private SQL area in the user session’s PGA.

6. 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.

7. Oracle immediately applies row-level locks, where needed, to prevent other processes from trying to change the same data simultaneously.

8. The server P. writes the change vectors to the redo log buffer.

9. The server P. modifies the table data (inserts the new row) in the data buffer cache.

10. The user commits the transaction, making the insertion permanent. Oracle releases the row locks after the commit is issued.

11. The log writer process immediately writes out the changed data in the redo log buffers to the online redo log file.

12. 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.)

13. 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 it may be some time before the inserted information is actually written permanently to the database files on disk.