This is the first step in the processing of any statement in Oracle. Parsing is the act of breaking the submitted statement down into its component parts ? determining what type of statement it is (query, DML, DDL) and performing various checks on it.
The parsing process performs two main functions:
o Syntax Check: is the statement a valid one? Does it make sense given the SQL grammar documented in the SQL Reference Manual. Does it follow all of the rules for SQL.
o Semantic Analysis: Going beyond the syntax ? is the statement valid in light of the objects in the database (do the tables and columns referenced exist). Do you have access to the objects ? are the proper privileges in place? Are there ambiguities in the statement ? for example if there are two tables T1 and T2 and both have a column X, the query ?select X from T1, T2 where ?? is ambiguous, we don?t know which table to get X from.And so on.
So, you can think of parsing as basically a two step process, that of a syntax check to check the validity of the statement and that of a semantic check ? to ensure the statement can execute properly. The difference between the two types of checks are hard for you to see ? Oracle does not come back and say ?it failed the syntax check?, rather it returns the statement with a error code and message.
So for example, this statement fails with a syntax error:
SQL> select from where 2;
select from where 2
*
ERROR at line 1:
ORA-00936: missing expression
While this statement failed with a semantic error ? if the table NOT_A_TABLE existed and we had permission to access it, this statement would have succeeded:
SQL> select * from not_a_table;
select * from not_a_table
*
ERROR at line 1:
ORA-00942: table or view does not exist
That is the only way to really tell the difference between a semantic and syntactic error ? if the statement COULD have executed given the proper objects and privileges, you had a semantic error, otherwise if the statement could not execute under any circumstances, you have a syntax error. Regardless ? Oracle will not execute the statement for you!
The next step in the parse operation is to see if the statement we are currently parsing has already in fact been processed by some other session. If it has ? we may be in luck here, we can skip the next two steps in the process, that of optimization and row source generation. If we can skip these next two steps in the process, we have done what is known as a Soft Parse ? a shorter process to getting our query going. If we cannot,
if we must do all of the steps, we are performing what is known as a Hard Parse ? we must parse, optimize, generate the plan for the query. This distinction is very important. When developing our applications we want a very high percentage of our queries to be Soft Parsed ? to be able to skip the optimize/generate phases ? as they are very CPU intensive as well as a point of contention (serialization). If we have to Hard Parse a large percentage of our queries, our system will function slowly and in
some cases ? not at all.The way this sharing of SQL in Oracle is accomplished is via the shared pool, a piece of memory in the SGA maintained by Oracle. We covered this topic in chapter 5 but will revisit is again in the context of processing a query. After Oracle parses the query and
it passes the syntax and semantic checks ? it will look in the shared pool component of the SGA to see if that same exact query has already been processed by another session. Since it has performed the semantic check it has already figured out:
o Exactly what tables are involved
o That we have access to the tables (the proper privileges are there)
And so on. Now, it can look at all of the queries in the shared pool that have already been parsed/optimized and generated to see if the work has already been done.
........ +
