How SQL statements Works in oracle

Connect the instance using User process and server process

Once Basic validation completed SQL statement ready to execute

• Syntax Check
• Semantics check
• Shared pool check

Syntax Check:

The database validates the sql query syntax is correct or not.

Example:

SQL> select from dba_objects;

select from dba_objects

       *

ERROR at line 1:

ORA-00936: missing expression

SQL>

Semantics check

Oracle Verify all column and table names using the dictionary, and confirm that you have permission to see the data.

Shared pool Check

Once syntax and semantics check done the server processer will initiate the sql query.

Server process will check the library cache

In the Library cache server process will check Most recently user to the least recently used algorithm for match the sql statement.

If library cache matches the sql statements that’s called soft parsing

If there is no match, the server process must continue with the creation of the execution plan hard parsing.

What is soft parsing?

If the sql statement is the same as a reusable in the shared pool area, then Oracle Database reuses the existing code. This is called Soft parse.

 

What is hard parsing? 

If the sql statement not available in shared pool area its called hard parsing.

The server process will then interact with the optimizer, who will read the SQL statement and produce the query's execution plan for the query. During parsing, the optimizer generates multiple execution plans.

The server process will select the most efficient execution plan and move to the library cache when the optimizer has generated execution plans.

In the library cache will store the execution plan along with the original sql statement.

The execution of the sql statement will now start after the parsing is finished.

The server process will then go to the database buffer cache to see if the data required by the query is already in the cache or not.

If the data is available, it is returned to the end user. otherwise, it is read from the database files.

How DML (insert,update,delete)statement will work: 

Once the query will complete the pre checks and it will come execute phase.

If the data and undo blocks are not already in the database buffer cache, the server process will read from datafiles to database buffer cache. 

The server process places locks on the rows that are to be modified. The rollback block issued to store the before-image of the data, so that the DML statements can be rolled back if necessary.

The data blocks record 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 done in the database buffer cache. Anychanged blocks in thebuffer cache are marked as dirty buffers. That is, buffers that are not the same as thecorresponding blocks on the disk.

The processing of a DELETE or INSERT command uses similar steps. The before image for a DELETE contains the column values in the deleted row, and the before image of an INSERT contains the row location information.