SQL Query Processing

Let us discuss what happens inside oracle when we write a SQL query and press the green color execute button on the SQL Developer application.

I have only highlighted a small portin of the oracle architecture to explain the SQL processing in oracle.Let dive into the topic.

Lets first focus on how SQL is processed by oracle

Once we write the query SELECT * FROM EMP, this is stored in the Library Cache (Parsing)

Oracle checks if the same select is present in the Library cache or not (It is case sensitive so if same query is written in two cases then it will consider it as all together different query).If it is not present then it will copy the query in the memory.So best practice is to be consistent with the case of your select query so that oracle need not copy the query into memory again.
If the query is not present then oracle will copy the query in the memory (Hard Parsing)
If query is found then oracle will not copy the query again (Soft Parsing)
Every select statement in the library cache is associated with a Hash value (its a unique value representing a query)
SELECT * FROM V$SQL WHERE SQL_TEXT LIKE ‘%empid%’; is the query to find the Library cache queries
Parsing time will be high if the number of sql queries hitting the db is high.

Syntax is checked
Semantic Parse (Checking validity of tables and columns and checks the rights of user)
Loading SQL in Library cache (Hard or Soft Parsing)
Based on Statistics it will create Execution plan of that SQL (Stats is stored in data dictionary in oracle. Statistics -> As of now what is the information of the table)
Lastly oracle will try to fetch the data in minimal time and using minimal resources.

Since memory area allocated to the shared pool is finite, so when we start the oracle instance it will occupy memory area into the RAM.Within the RAM(1gb), SGA(20mb) is present,Shared Pool(5mb from the 20mb).
Since the library cache has a small memory, the query that comes first into the memory goes out first (FIFO- First in First Out or LRU-Least Recently Used)
So if you have executed some sql in the morning then you wont find it in the memory in the evening because many people have executed sql queries.
During hard parsing a lot of table views are update by oracle, so try to avoid hard parsing.
Every SQL is assigned a hash value and SQL Id
Bind variable can be used to reduce hard parsing.The query stored in the oracle table is using the variable name instead of the variable value.

I encourage you to go through the oracle docs for the detailed working of the Oracle architecture.

If you have a little more patience, you can wait for me to post it in my blog in simplified way.

ROLLUP and CUBE Functions in Oracle