SQL Parsing in Oracle

Parsing is the process of interpreting a SQL statement and
creating an execution plan for it. This process has many phases, including syntax
checking, security checking, execution plan generation, and loading shared
structures into the shared pool.

There are two types of parse operations:

■ Hard Parsing: A SQL statement is submitted for the first time, and no match is
found in the shared pool. Hard parses are the most resource-intensive and
unscalable, because they perform all the operations involved in a parse.

■ Soft Parsing: A SQL statement is submitted for the first time, and a match is
found in the shared pool. The match can be the result of previous execution by
another user. The SQL statement is shared, which is good for performance.
However, soft parses are not ideal, because they still require syntax and
security checking, which consume system resources.


To ensure that SQL is shared, use bind variables and do not use string literals with SQL statements.

For example:

Statement with string literals:
--------------------------------
SELECT * FROM employees
WHERE last_name LIKE 'KING';

Statement with bind variables:
-------------------------------

SELECT * FROM employees
WHERE last_name LIKE :1;