DBA Tips Archive for Oracle

  


Parsing SQL Statements in Oracle

by Jeff Hunter, Sr. Database Administrator

Contents

  1. Overview
  2. The Syntax Check & Semantic Analysis
  3. Hard Parse vs. Soft Parse
  4. Why not Check the Shared Pool First?

Overview

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 following figure demonstrates this sequence of steps.

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.

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:

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.



Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Thursday, 19-Sep-2002 00:00:00 EDT
Page Count: 52963