What is Hard Parse and Soft Parse?

Hard Parse and Soft Parse
The execution of an sql statement consists of the following steps:
1)Parsing
2)Optimization and Row source generation
3)Execution
4)Fetch
Parsing is the first step in the processing of an sql statement.
Parsing involves the following two main functions:
1)Syntax check
2)Semantic analysis
1)Syntax check
During this phase, oracle checks for any syntax errors in the sql statement.For example,
SQL> select * fro emp
  2  ;
select * fro emp
         *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
The above error comes as a result of syntax check.Oracle checks the sql for any sql grammatical errors.It checks whether the sql follow all the rules it is meant to.
2)Semantic analysis
During this phase,oracle checks the validity of the sql statement in the light of database objects.Oracle checks a number of things,for example:
-Is the object exist?
-Does the user have all the necessary privileges?
-Are there any ambiguity in the sql statement?
etc etc
These checks are performed at symantic analysis.For example,
SQL> select * from employee;
select * from employee
              *
ERROR at line 1:
ORA-00942: table or view does not exist
Although the syntax is correct but oracle reported a semantic error.
Similarly for an sql statement of the type:
SQL> select empno,deptno from emp,dept where emp.deptno=dept.deptno;
select empno,deptno from emp,dept where emp.deptno=dept.deptno
             *
ERROR at line 1:
ORA-00918: column ambiguously defined
As oracle has no way to know which table the columns col1 and col2 belongs,it throws the error.
After these two steps, oracle checks whether the statement that we are parsing is already present in memory or not.
If it is present in memory then oracle can skip the further two phases ie optimization and row source generation.This is know as Soft parse.
If the statement is not present in memory then oracle would have to parse,optimize and generate the plan for the statement.All this reprocessing is known as hard
parse.
So what the fuss about soft and hard parse??
A soft parse is less cpu intensive than the hard parse. Optimizing the query and generating row source operations are cpu intensive tasks. So if the database is
undergoing a large amount of hard parses,this might slow down the database or eventually hang it.
So the goal should be to soft parse the query once and execute it many times.

No comments:

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...