Processing select statement in oracle database

Understanding the execution of any query in the Oracle database needs an understanding of different components of the Oracle database such as SGA, Processes, datafiles, memory, etc.

Here, we will discuss the execution of a simple select query like “select * from

 hr.emp

First of all, a connection to the database is required, and to achieve that you need to know database connection details such as port number, hostname, sid, etc. Once you have these details, the user will log in to the database and a connection is established using the listener.

Now user can execute any query, the query sent by the user once reaches the database, gets picked up by the server process, and from here onwards, processing will be carried out by the server process.

The server process will create a hash for the SQL query sent by the user, the hash is just hexadecimal coding in the form of SQL id and SQL hash value.

Now server process searches in the shared pool and checks if any SQL area is present for the user query, if SQL area is already present for this query, if the result finds in SQL area, data will be returned to the user from memory itself, no further process required.

In case the SQL area is not present for this SQL query, that means the query is getting executed the first time in the database. The server process will start building SQL area for the query

Processing of the select query is taken in 3 main parts

• Parsing
• Execution
• Fetching

Parsing

Parsing is also known as validation of the query sent by the user, it will be done in three steps

• Syntax check
• Semantics check
• Privilege check

Syntax Check

            Here, the syntax of the SQL query will be verified as per SQL standards and keywords, sometimes we misspell some keywords such as “SELECT” is written as “SLECT” or “SELCT”, syntax check will throw an error, once the syntax check is passed, it moves to semantic check

Semantic check

            In semantic check, the server process verifies the structure of the query, i.e., columns names, tables, or schema names used will be verified with the help of a dictionary cache

Privilege check

In this stage of parsing, privileges on users will be verified, the client is executing the query using USER1 schema and accessing the table from USER2, so the server process will check if USER1  has access to USER2

Once all these checks are verified as part of the parsing, it will start the execution of the SQL query based on the SQL plan described by OPTIMIZER, OPTIMIZER is an internal utility in the database, that keeps all the statistics of the database objects

The server process will bring data in the buffer cache i.e., memory from datafiles, once data is loaded in the memory, a complete SQL area will be created and the server process will return data to the user, this completes the processing of the SQL query. so, processing the query involves a lot of components of the database