Oracle - How Oracle SQL Statement is processed

Stages of Processing SQL Statement:

SQL statements pass through several stages during their processing:

Parsing
Binding
Executing

Oracle uses cursors, private SQL areas, to store parsed statements and other information relating to the statements it’s currently processing. Oracle automatically opens a cursor for all SQL statements.

Parsing

During the parsing stage, Oracle does several things to check your SQL statements:
• Oracle checks that your statements are syntactically correct.
The server consults the data dictionary to check whether the tables and column specifications are correct.
• Oracle ensures that you have the privileges to perform the actions you are attempting through your SQL statements.
• Oracle prepares  the execution plan for the statement, which involves selecting the best access methods for the objects in the statement.

After it checks the privileges, Oracle assigns a number called the SQL hash value to the SQL statement for identification purposes. If the SQL hash value already exists in memory, Oracle will look for an existing execution plan for the statement, which details the ideal way it should access the various database objects, among other things. 

Soft Parse:
If the execution plan exists, Oracle will proceed straight to the actual execution of the statement using that execution plan.This is called a soft parse, and it is the preferred technique for statement processing. Because it uses previously formed execution plans, soft parsing is fast and efficient.

Hard Parse:
The opposite of a soft parse is a hard parse, and Oracle has to perform this type of parse when it doesn’t find the SQL hash value in memory for the statement it wants to execute. Hard parses are tough on system memory and other resources. Oracle has to create a fresh execution plan, which means that it has to evaluate the numerous possibilities and choose the best plan from among them. During this process, Oracle needs to access the library cache and dictionary cache numerous times to check the data dictionary, and each time it accesses these commonly used areas, Oracle needs to use latches, which are low-level serialization control mechanisms, to protect shared data structures in the SGA. Thus, hard parsing contributes to an increase in latch contention.

Any time there’s a severe contention for resources during statement processing, the execution time will increase. Remember that too many hard parses will lead to a fragmentation of the shared pool, making the contention worse. 

After the parsing operation is complete, Oracle allots a shared SQL area for the statement. Other users can access this parsed version as long as it is retained in memory.

Binding

During the binding stage, Oracle retrieves the values for the variables used in the parsing stage. Note that the variables are expanded to literal values only after the parsing stage is over.

Execution

Once Oracle completes the parsing and binding, it executes the statement. Note that Oracle will first check whether there is a parsed representation of the statement in memory already. If there is, the user can execute this parsed representation directly, without going through the parsing process all over again.

It’s during the execution phase that the database reads the data from the disk into the memory buffers (if it doesn’t find the data there already). The database also takes out all the necessary locks and ensures that it logs any changes made during the SQL execution.

After the execution of the SQL statement, Oracle automatically closes the cursors.