How is query executed in oracle

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

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. 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.

In the library cache the server process will keep the e-plan in the library cache along with the original sql text. At this point in time the parsing ends and the execution of the sql sataement will begin. 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.

A user starts an application spawning a user process. The application attempts to establish a connection to the server. The server runs a listener that has the appropriate Oracle Net Services handler.

The listener detects the connection request from the application and creates a dedicated server process on behalf of the user process. For example, the user changes the address of a customer in a table and commits the change.

If a shared SQL area is not found, a new shared SQL area is allocated for the statement so that it can be parsed and processed. When those small queries are executed thousands or millions of times per day, they become the most resource-intensive queries in the database. When planning the SQL access paths for your application, tune even the small queries. Save my name, email, and website in this browser for the next time I comment. Toggle navigation. Here are the rules Oracle uses to execute each query, and the order in which execution takes place: C hoose rows based on the WHERE clause.

Calculate the results of the group functions for each group. Eliminate rows based on 2.