Tuesday, February 26, 2008

Hard Parse Vs Soft Parse

Hard parse consists of following steps.

1. Load the sql into memory. This memory area is the shared pool.

2. Syntax parse. Check the syntax of the sql, if there are any spelling mistakes, keywords used in wrong places, etc.

3. Semantic parse. Check if the objects refered in the sql do exists and user is authorized to access these objects.

4. Optimization. An execution plan is created based on the schema statistics or dynamic sampling.

5. Create executable. An executable is created with native file calls to service the sql query.

Soft parse consists of all the steps mentioned above except for the first one. If the sql is already found in the shared pool it is used instead of loading it to shared pool.