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.
Parsing process
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.
Identical statements
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).
