Parsing in Oracle
April 21, 2004
Whenever a statement is executed, Oracle follows a methodology to evaluate the statement in terms of syntax, validity of objects being referred and of course, privileges to the user. Apart from this, Oracle also checks for identical statements that may have been fired, with the intention of reducing processing overheads. All this takes place in a fraction of a second, even less, without the user knowing what is happening to the statement that was fired. This process is known as Parsing.
Types of Parsing
All statements, DDL or DML, are parsed whenever they are executed. The only key fact is that whether it was a Soft (statement is already parsed and available in memory) or a Hard (all parsing steps to be carried out) parse. Soft parse will considerably improve the system performance where as frequent Hard parsing will affect the system. Reducing Hard parsing will improve the resource utilization and optimize the SQL code.
Oracle internally does the following to arrive at the output of an SQL statement.
1. Syntactical check. The query fired is checked for its syntax.
2. Semantic check. Checks on the validity of the objects being referred in the statement and the privileges available to the user firing the statement. This is a data dictionary check.
3. Allocation of private SQL area in the memory for the statement.
4. Generating a parsed representation of the statement and allocating Shared SQL area. This involves finding an optimal execution path for the statement.
In point four, Oracle first checks if the same statement is already parsed and existing in the memory. If found, the parsed representation will be picked up and the statement executed immediately (Soft parse). If not found, then the parsed representation is generated and stored in a shared SQL area (Part of shared pool memory in SGA), the statement is then executed (Hard parse). This step involves the optimization of the statement, the one that decides the performance.
Oracle does the following to find identical statements to decide on a soft or a hard parse.
a. When a new statement is fired, a hash value is generated for the text string. Oracle checks if this new hash value matches with any existing hash value in the shared pool.
b. Next, the text string of the new statement is compared with the hash value matching statements. This includes comparison of case, blanks and comments present in the statements.
c. If a match is found, the objects referred in the new statement are compared with the matching statement objects. Tables of the same name belonging to different a schema will not account for a match.
d. The bind variable types of the new statement should be of same type as the identified matching statement.
e. If all of the above is satisfied, Oracle re-uses the existing parse (soft). If a match is not found, Oracle goes through the process of parsing the statement and putting it in the shared pool (hard).