How oracle select statement work internally

In this article we are going to clarify how select statement work in oracle database. Here, we will discuss about oracle select statement internal mechanism along with example.

When oracle database receives sql statement from client oracle database perform some pre-tasks before run the sql statement which steps are given below.

Step 1: When oracle receive sql statement first of all oracle server process check the query syntax whether sql statement is valid or not.

For example,

Select * employees; 

ORA-00923: FROM keyword not found where expected

above query generate an error message because FROM keyword is missing and this types of check are called syntax check. In this stage database validate the syntax of the sql statement whether is valid or not.

select * from employees; 

Above statement syntax check is valid

Step 2: This step database validates the semantic of the sql statement whether the statement is meaningful or not. 

For example,

Select * from test;

ORA-00942: table or view does not exist

It means test table doesn't exist this check is called semantic check. In semantic check whether the object or column of the sql statement is exist or not. Server process perform this check in the data dictionary cache of shared pool. Server process will check the definition of the object.

Step 3: If the sql statement pass the syntax and semantic than server process will go the library cache. In library cache 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 (this is called soft parsing) but if no match is found then server process has to proceed with the generation of execution plan (this is called hard parsing).

Step 4 (Soft Parse): If the submitted sql statement hash value match of the query in library cache, then oracle database reuses the existing code and execution plan this is called soft parsing. 

Step 4 (Hard Parse): If the submitted sql statement hash value don't match then server process generate execution plan for the sql statement and this process is called hard parsing. In this stage database optimizer generate multiple execution plans for this sql statement during parsing. Server process will take the best and cost effective execution plan of the query and go to the library cache. In library cache will keep the execution plan along with the sql statement for further reuse. 

Step 5: After completing the sql statement parsing process the sql statement execution will began. Server process will go to database buffer cache and whether required data is already available in cache or not in cache. If query data find in cache it will returned to the client otherwise it brings the data from the database files.

Done!