step by step processing of sql statements in oracle architecture

If it's a query (SELECT)

-------------------------------------------------------------------------------

1. Server process checks Library Cache to see if same statement has been issued previously. If so, acquire the existing execution plan

2. If no, then server process stores the submitted select in the library cache. Server process now parses it and develops an execution plan, storing the final execution plan with the SQL statement in the Library Cache

3. Server process visits Buffer Cache and checks to see if required data is already in cache. If so, read the existing buffers to obtain the data.

4. If not, the server process fetches the necessary blocks from disk and loads them into the buffer cache. Once in the cache, the server process is able to obtain the necessary row data.

5. Server process returns data to the user that requested it.

If its DML (INSERT/UPDATE/DELETE/MERGE)

--------------------------------------------------------------------------------

1. As above

2. As above

3. As above

4. Once the necessary data is in the buffer cache, the server process takes locks against each of the rows it is interested in

5. The server process obtains a reservation of space in an available undo segment and buffers in the cache to act as the in-memory store for that space. It writes the current version of the data into those in-memory buffers.

6. The server process obtains permission to access the Log Buffer, and writes the current AND FUTURE versions of the data about to be changed into that log buffer. If you're changing someone's age from 44 to 45, for example, only the ROWID and that age column is written in as a pair of "change vectors" -sometimes called the 'before and after images of the data'

7. Once the log buffer data has been written, the server process can now modify the data in the buffer cache (changing the relevant columns to their new values

8. Once all changes to data have been made, the server process returns an 'X rows updated/deleted/inserted' message to the user who submitted the original SQL

Note that changed data remains entirely in memory at this point, so a power failure would cause you to lose this update.

If it's a COMMIT

--------------------------------------------------------------------------------

1. The server process sends a message to the LGWR process to flush all outstanding redo in the log buffer to the online redo logs.

2. The LGWR process sends a message back to the server process saying 'flush complete'

3. The Server Process sends a message to the client saying 'commit comlpete'

4. The Server Process removes locks from rows stored in the buffer cache.

Note that changed data remains entirely in memory at this point, so a power failure would cause you to lose that data. However, the before and after images of the data have been saved to the online redo logs -so the nature of the change you've just committed is safely captured and recoverable. That's why, after a commit, you can be sure your data changes are recoverable (so long as your redo logs are safe, of course!)

And that's just a very generalised explanation of things, because a heck of a lot of internals take place at each and every one of those steps (latches, mutexes and fun stuff like that). But hopefully, that will give you the gist of what goes on.