How select statement works internally in oracle

In this post we will discuss about the order or flow of execution of select statement in oracle.

To write a select query against an oracle database we require an oracle client installation on the client system. Oracle client is nothing but oracle sql*plus. whenever we are giving the username, password and host string to sql*plus client then it takes the host string and will lookup a file known as tnsnames.ora (transparent network substrait). 

This file will be located in $ORACLE_HOME\network\admin\tnsnames.ora. 

The oracle client is also installed when we install developer forms or jdeveloper. The tns file will keep the host string or alias and that will point to a config script. The script will keep ip address of oracle server,port number of the listener and sid of the database. Using these details sql*plus will dispatch the given username and password to the above given address. The database will authenticate the user and if successful then a server process will be initiated on the server side and user process will be initiated on the client side. After this a valid session is establish between the client and the server. The user types a query on the sql prompt.

Below are the select query execution flow in oracle:

1.  SQL*PLUS checks the syntax on client side.


2.  If syntax is correct the query is stamped as a valid sql statement and encrypted into oci (oracle call interface) packets and sent via lan using tcp to the server.


3.  Once the packets reach the server the server process will rebuild the query and again perform a syntax check on server side.


4. Then if syntax is correct server process will continue execution of the query.


5. The server process will go to the library cache. The library cache will keep the recently executed sql statements along with their execution plan.


6.  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.  Parsing is the process undertaken by oracle to generate an execution plan.


8.  The first step in parsing involves performing a semantic check. This is nothing but check for the existence of the object and its structure in the database.


9.  This check is done by server process in the data dictionary cache. Here server process will ask for the definition of the object, if already available within the data dictionary cache, server process will process the check. If not available then server process will retrieve the required information from the system tablespace.


10. After this in case of hard parsing the server process will approach the optimizer, who will read the sql statement and generate the execution plan of the query. the optimizer generates multiple execution plans during parsing.


11. After generation of the e-plan's by the optimizer the sp will pick the best possible and cost effective e-plan and go to the library cache.


12. In the library cache the server process will keep the e-plan in the library cache along with the original sql text.


13. At this point in time the parsing ends and the execution of the sql sataement will begin.


14. Server Process will then go to the database buffer cache and checks whether the data required by the query is already available or not in the cache.


15. If available that data can be returned to the client else it brings the data from the database files.


16. If sorting and filtering is required  by the query then the pga is utilized along with the temporary tablespace for performing sort run.


17. After sort run the data is returned to the client and sql*plus client will convert the given data to ascii format and display the data in a tabular format to the users.