Parsing SQL Statements in Oracle

Parsing SQL Statements in Oracle

One of the first steps Oracle takes in processing a SQL statement is to parse it. During the parsing phase, Oracle will break down the submitted SQL statement into its component parts, determine what type of statement it is (Query, DML, or DDL), and perform a series of checks on it. Two important concepts for the DBA to understand is (1) what are the steps involved in the parse phase and (2) what is the difference between a hard parse and a soft parse.

The Syntax Check & Semantic Analysis


The first two function of the parse phase Syntax Check and Semantic Analysis happen for each and every SQL statement within the database.
Syntax Check
Oracle checks that the SQL statement is valid. Does it make sense given the SQL grammar documented in the SQL Reference Manual? Does it follow all of the rules for SQL?


Semantic Analysis
This function of the parse phase, takes the Syntax Check one step further by checking if the statement is valid in light of the objects in the database. Do the tables and columns referenced in the SQL statement actually exist in the database? Does the user executing the statement have access to the objects and are the proper privileges in place? Are there ambiguities in the statement? For example, consider a statement that references two tables emp1 and emp2 and both tables have a column name. The following statement "select name from emp1, emp where..." is ambiguous; the query doesn't know which table to get name from.

Although Oracle considers the first two functions of the parse phase (checking the validity of the SQL statement and then checking the semantics to ensure that the statement can be properly executed), the difference is sometimes hard to see from the users perspective. When Oracle reports an error to the user during the parse phase, it doesn't just come out and say "Error within the Syntax Function" or "Error within the Semantics Function".

For example, the following SQL statement fails with a syntax error:

SQL> select from where 4;
select from where 4
*
ERROR at line 1:
ORA-00936: missing expression
Here is an example of a SQL statement that fails with a semantic error:

SQL> select * from table_doesnt_exist;
select * from table_doesnt_exist
*
ERROR at line 1:
ORA-00942: table or view does not exist

Hard Parse vs. Soft Parse


We now consider the next and one of the most important functions of Oracle's parse phase. The Oracle database now needs to check in the Shared Pool to determine if the current SQL statement being parsed has already been processed by any other sessions.
If the current statement has already been processed, the parse operation can skip the next two functions in the process: Optimization and Row Source Generation. If the parse phase does, in fact, skip these two functions, it is called a soft parse. A soft parse will save a considerable amount of CPU cycles when running your query. On the other hand, if the current SQL statement has never been parsed by another session, the parse phase must execute ALL of the parsing steps. This type of parse is called a hard parse. It is especially important that developers write and design queries that take advantage of soft parses so that parsing phase can skip the optimization and row source generation functions, which are very CPU intensive and a point of contention (serialization). If a high percentage of your queries are being hard-parsed, your system will function slowly, and in some cases, not at all.

Oracle uses a piece of memory called the Shared Pool to enable sharing of SQL statements. The Shared Pool is a piece of memory in the System Global Area (SGA) and is maintained by the Oracle database. After Oracle completes the first two functions of the parse phase (Syntax and Semantic Checks), it looks in the Shared Pool to see if that same exact query has already been processed by another session. Since Oracle has already performed the semantic check, it has already determined:


Exactly what table(s) are involved

That the user has access privileges to the tables.
Oracle will now look at all of the queries in the Shared Pool that have already been parsed, optimized, and generated to see if the hard-parse portion of the current SQL statement has already been done.

Why not Check the Shared Pool First?


Now that you understand the steps involved in parsing SQL statements, it's time to take it one step further. Oracle will always keep an unparsed representation of the SQL code in the Shared Pool, and that the database will perform a hashing algorithm to quickly located the SQL code. OK, so why doesn't Oracle make this step (checking the Shared Pool for a matching statement) the first step in its parsing phase, before making any other checks.
Even when soft parsing, Oracle needs to parse the statement before it goes looking in the Shared Pool. One of the big reason's for this sequence is the Semantic Check. Consider the following query:

select * from emp;
Assume that this query was first submitted by user "SCOTT" and that the "emp" table in the FROM clause is a table owned by SCOTT. You then submit the same exact query (as a user other than SCOTT) to Oracle. The database has no idea what "emp" is a reference to. Is it a synonym to another table? Is it a view in your schema that references another table? For this reason, Oracle needs to perform a Semantic Check on the SQL statement to ensure that the code you are submitting is going to reference the same exact objects you are requesting in your query.

Then remember that Oracle needs to syntactically parse the query before it can semantically parse it. The hash is good only for finding query strings that are the same; it doesn't help the database figure out if the referenced statements are the same statement as in you execution context.