How to execute a query in oracle

Steps for how to execute a query in oracle : 

1. An instance has started on the computer running Oracle (often called the host or database server).

2. A computer running an application(a local computer or client workstation ) run the application in a user process. the client application attempts to establish a connection to the server using the proper Oracle Net Services driver.

3. The server detects the connection request from the application and creates a dedicated server process on behalf of the user process.

4. For this server process oracle allocate program global area(PGA) that contains : stack space for stores the session’s variables, array and other information. 

5. PGA also contains the session information if connection is establish to dedicated server otherwise it will manage in SGA.

In SGA some checks are perform on query as:

The first two function of the parse phase syntax check and Semantic analysis for each and every sql statement within the database.

Syntax Check :

Oracle checks that the SQL statement is valid. Does it make sense given the SQL grammar documented in the SQL Reference Manual? Does it follow all of the rules for SQL?

Semantic Analysis :

This function of the parse phase, takes the Syntax Check one step further by checking if the statement is valid in light of the objects in the database.

1. Do the tables and columns referenced in the SQL statement actually exist in the database?

2. Does the user executing the statement have access to the objects and are the proper privileges in place?

3. Are there ambiguities in the statement? (like two tables using same column name in join operation) .

Hard Parse vs. Soft Parse : 

We now consider the next and one of the most important functions of Oracle's parse phase. The Oracle database now needs to check in the Shared Pool to determine if the current SQL statement being parsed has already been processed by any other sessions.

If the current statement has already been processed, the parse operation can skip the next two functions in the process: Optimization and Row Source Generation. If the parse phase does, in fact, skip these two functions, it is called a soft parse. 

On the other hand, if the current SQL statement has never been parsed by another session, the parse phase must execute ALL of the parsing steps. This type of parse is called a hard parse.

Optimization : 

Query transformation is a set of techniques used by the optimizer to rewrite a query and optimizer it better. Few optimization paths open up to the optimizer after query transformation. Some query transformations must be costed to be chosen and some do not need to be costed. For example, if a table can be eliminated completely from the join, then that transformation is applied and need to cost that transformation is minimal.