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