Oracle Concepts: How select statement works:Parsing SQL Statements in Oracle

Parsing SQL Statements in Oracle

How Oracle Database processes SQL statements ?

For performance tuning basic knowledge a developer or tuning expert needs to know the basic execution of the select statement works.

What happens when a select statement get issues in the DB (select * from emp)

This explains the way in which the database processes DDL statements to create objects, DML to modify data, and queries to retrieve data.

SQL Parsing

First stage of SQL processing is PARSING

When an application issues a SQL statement, the application makes a parse call to the database to prepare the statement for execution.

The parse call opens or creates a cursor, which is a handle for the session-specific private SQL area that holds a parsed SQL statement and other processing information. The cursor and private SQL area are in the PGA.

PGA : Program Global Area (PGA) is a memory buffer that contains data and control information for a server process.

Every session will have its own PGA

Cursors: The Cursor is a handle (name or a pointer) for the memory associated with a specific statement.

A cursor is basically an Area allocated by Oracle for executing the SQL statements.

Steps in Parsing

· Syntax Check

· Semantic Check

· Shared Pool Check

Syntax Check

A statement that breaks a rule for well-formed SQL syntax fails the check.

syntactic checking verifies that keywords,

· object names

· operators

· delimiters

and so on are placed correctly in your SQL statement.

For example, the following embedded SQL statements contain syntax errors: For example, the following statement fails because the keyword FROM is misspelled as FORM:

SQL> SELECT * FORM employees;

SELECT * FORM employees

ERROR at line 1: ORA-00923: FROM keyword not found where expected

Semantic Check

Meaning. Thus, a semantic check determines whether a statement is meaningful , 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?

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

Shared Pool Check(Technical Aspects )

The database performs a shared pool check to
determine whether it can skip resource-intensive steps of statement processing. To this end, the database uses a hashing algorithm
to generate a hash value for every SQL statement. The statement hash value is
the SQL ID shown in V$SQL.SQL_ID.

Hard parse

If Oracle Database cannot reuse existing
code, then it must build a new executable version of the application code. This
operation is known as a hard parse, or a library cache miss.

The database always perform a hard parse of DDL.

During the hard parse, the
database accesses the library cache and data dictionary
cache numerous times to check the data dictionary. When the
database accesses these areas, it uses a serialization device called a latch on
required objects so that their definition does not change. Latch contention
increases statement execution time and decreases concurrency.

Latch :A
latch is a low-level internal lock used by Oracle to protect memory structures
(for example if you are issuing a statement at time table structure should not be modified
like drop column).A low-level serialization control mechanism used to protect
shared data structures in the SGA from simultaneous access.

library
cache :.
The Library Cache is a piece of memory within the SGA that Oracle uses in order
to store SQL StatementsAn area of memory in the shared pool.
This cache includes the shared SQL areas, private SQL areas (in a shared server configuration), PL/SQLprocedures
and packages, and control structures such as locks and library cache handles.

Soft parse

A soft parse is any parse that
is not a hard parse. If the submitted statement is the same as a reusable SQL statement in
the shared pool,
then Oracle Database reuses the existing code. This reuse of code is also
called a library
cache hit.

Soft parses can vary in the amount of
work they perform. For example, configuring the session shared SQL area can
sometimes reduce the amount of latching in the soft parses, making them
"softer."

In general, a soft parse is preferable to
a hard parse because the database skips the optimization and row source
generation steps, proceeding straight to execution.

Hard Parse vs. Soft Parse

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?

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

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

SQL Optimization

Query optimization is the process of choosing the most efficient means of executing a SQL statement.
The database optimizes queries based on statistics
collected about the actual data being accessed. The optimizer uses the number
of rows, the size of the data set, and other factors to generate possible
execution plans, assigning a numeric cost to each plan. The database uses the
plan with the lowest cost.

The database must perform
a hard parse at least once for every unique DML statement and performs
optimization during this parse. DDL is never optimized unless it includes a DML
component such as a subquery that requires optimization.

SQL Row Source Generation

The row source generator is software that receives the optimal execution plan from
the optimizer and produces an iterative plan, called the query plan,
that is usable by the rest of the database. The iterative plan is a binary
program that, when executed by the SQL virtual machine, produces theresult set.

A row source is
an iterative control structure. It processes a set of rows, one row at a time,
in an iterated manner. A row source produces a row set.The row source
can be a table, view, or result of a join or grouping operation. Each row source produced by the row source generator is executed by the SQL execution engine.

SQL Statement Processing

SQL Statements are processed differently depending on whether the statement is a query, data manipulation language (DML) to update, insert, or delete a row, or data definition language (DDL) to write information to the data dictionary.

Processing a query:

· Parse:

o Search for identical statement in the Shared SQL Area.

o Check syntax, object names, and privileges.

o Lock objects used during parse.

o Create and store execution plan.

· Bind: Obtains values for variables.

· Execute: Process statement.

· Fetch: Return rows to user process.

Processing a DML statement:

· Parse: Same as the parse phase used for processing a query.

· Bind: Same as the bind phase used for processing a query.

· Execute:

o If the data and undo blocks are not already in the Database Buffer Cache, the server process reads them from the datafiles into the Database Buffer Cache.

o The server process places locks on the rows that are to be modified. The undo block is used to store the before image of the data, so that the DML statements can be rolled back if necessary.

o The data blocks record the new values of the data.

o The server process records the before image to the undo block and updates the data block. Both of these changes are made in the Database Buffer Cache. Any changed blocks in the Database Buffer Cache are marked as dirty buffers. That is, buffers that are not the same as the corresponding blocks on the disk.

o The processing of a DELETE or INSERT command uses similar steps. The before image for a DELETE contains the column values in the deleted row, and the before image of an INSERT contains the row location information.

Processing a DDL statement:

· The execution of DDL (Data Definition Language) statements differs from the execution of DML (Data Manipulation Language) statements and queries, because the success of a DDL statement requires write access to the data dictionary.

· For these statements, parsing actually includes parsing, data dictionary lookup, and execution. Transaction management, session management, and system management SQL statements are processed using the parse and execute stages. To re-execute them, simply perform another execute.

Reference

http://www.idevelopment.info/data/Oracle/DBA_tips/Tuning/TUNING_10.shtml

http://docs.oracle.com/cd/A87860_01/doc/server.817/a76992/optimops.htm#29149
http://docs.oracle.com/cd/E11882_01/server.112/e16508/sqllangu.htm#CHDFCAGA