Internal Working of Query Execution
When we execute an SQL statement e.g. SELECT * FROM EMP , Lets see what happens behind the scene i.e Internal working of query execution.In other words , Life cycle of an SQL statement .
1. Opening the cursor –
In the very first step , implicitly or explicitly a cursor is created for every SQL statement.In this step a hash value is generated for the statement which is compared with the hash value in the SQL area. If the hash value is already present then it undergoes soft parse , else hard parse.
2. Parsing –
The main purpose of parsing is generation of query execution plan,
Query Execution Plan – It is a step by step instruction as to how the query must be executed. The query execution plan is also stored in the library cache.The query execution plan for an SQL statement can be viewed by –
EXPLAIN PLAN;
In this step , SQL statement is parsed. This parsing may be soft parse or hard parse.
a ) Soft parse –
If the SQL statement to be parsed is already present in the library cache i.e. shared pool global area, then , it is not parsed and is directly executed using the stored information.
b ) Hard Parse –
If the SQL statement is not present in the library cache then it needs to undergo hard parsing which involves following steps:
1. Syntax Check – In this step the syntax of the statement is checked. For e.g.
Selct ename from Customer ; —- incorrect
Select ename from Customer ; —-correct
2. Semantic Check – This step involves checking the data dictionary for the table and the column definitions.
For e.g. Select name from Customer ;
This statement is correct but it may happens that name column does not exist in the table customer , or the table itself does not exist or sometimes it may happen that user is trying to execute the query with insufficient object privileges.
3. Parse Lock – This step involves acquiring parse locks for the objects under process in order to lock their definition during parsing.
4. Loading in the shared SQL area – If the statement is syntactically and semantically correct , it is loaded into the shared SQL area.
5. Binding Variables :
What are Bind Variables ?
Many times , most DML statements and certain queries (such as those with a WHERE clause), requires a program to pass data to Oracle as part of a SQL statement.For e.g.
INSERT INTO CUSTOMER
(empno, name , location, sal )
Values ( 101,’shikha’,’bengaluru’,30000);
INSERT INTO CUSTOMER
(empno, name , location, sal )
Values ( 102,’shallu’,’chennai’,20000);
In the above case, the program needs to be recompiled again each time we try to enter a new record which comes out to be very costly.In order to make the program more flexible , data is supplied at run time through bind variables. The bind variables are indicated by colon followed by name or number. Hence now the above statements can be rewritten as :
INSERT INTO CUSTOMER
(empno, name , location, sal )
Values ( :empno, :name , :location, :sal);
SELECT * FROM CUSTOMER
WHERE name = :name
The Bind step supplies the length, datatype and address where the value for the variabe can be found.This process is called binding variables.
at this point, oracle needs values for any variables listed in the statement; for example, in sql statement “select * from emp where empno = p_empno”, oracle needs a value for p_empno. This process is called binding variables. A program must specify the location (memory address) where the value can be found.Memory is allocated for the bind variables and filled with the binding variables.
5. Execute
Oracle checks if the data required for query execution is there in buffer cache or not. If it is not there then it is loaded from the disk into the buffer cache. In case the records are to be changed for e.g. in case of updation and deletion, the records are locked in order to avoid changes by other sessions. The image before the changes is written to redo log buffer and the after image describing the changes is written to rollback segments.The original block contains a pointer to the rollback segments. The records are then changed.
6. Fetch
Data is fetched from the database block and returned to the application.Rows that do not match the criteria are removed, if query contains order by clause then it is sorted.
