Query Processing and Parsing

Queries in the Oracle need to be broken down into its sub-components and various checks needs to be performed on them before it can actually be executed.
The first phase of parsing a query includes Syntax check followed by Semantic check. Syntax check basically deals with the array of the SQL Grammar i.e. whether the statement/query written exists in the SQL Dictionary or is correct according to the grammar of the SQL Language while Semantic check checks for the existence of the object referred in the query i.e. Semantic check is, in short, the object check.

SQL> select * form tab;
select * form tab
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Here what it returns is the expression saying, "FROM keyword not found " ... That means something is wrong with the syntax of the statement i.e. the structure of the query is not matching with what is expected. So, it failed at the syntax check ...

Now in semantic check, if the syntax is correct and the objects referred in the query are correct; then it generates the output otherwise not.
By the correctness the object, comes various considerations like privileges, existence etc. But the main point remains the same; the object should be visible to the user and the user should have necessary privileges on the object referred.

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

Semantic check failed since table doesn't exist even though the syntax of the query is correct.


After both the checks are done, Oracle checks if the exactly same query has been processed before i.e. if the work has already been done before. Shared pool, component of SGA (Shared/System Global Area) plays an important part here ... The SGA is a chunk of memory that is allocated by an Oracle Instance (during the no mount stage) and is shared among Oracle processes. The shared pool is used for objects that are shared among all users. Shared Pool has two important components: - Library Cache and Data Dictionary Cache. Data Dictionary cache stores part of the data dictionary because Oracle has to query the data dictionary very often while library cache is further divided into Shared SQL Area, PL/SQL Procedures and Control Structures (Latches and Locks) which stores the SQL text and the execution plan for the query.

Now if the query has been processed before and if it is found in library cache of SGA, then area called cursor area is already allocated in the library cache and execution plan exists there and thus we don’t need to perform the steps like Query Transformation, Plan generation again saving us lot of time ... This is what we called as "SOFT PARSE".

But if the query isn't there in library cache, then what we do, we call it as "HARD PARSE". In short, the steps that are there are: -
1. Oracle unpins the library cache latch and pin the shared pool latch.
2. Then it performs the query transformation steps (Query rewrite, bind variables etc)
3. After the query transformation, Oracle estimates the cost of the query in terms of I/O, Memory and CPU
4. And based on the cost, it generates multiple plans to execute the query and picks the most optimized plan and executes the query.

We can check for the execution plan selected for a query by using set autot command .It displays a report on the execution of successful SQL DML statements (such as SELECT, INSERT, UPDATE, DELETE or MERGE). The report can include execution statistics and the query execution path.

Syntax: -
SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

OFF does not display a trace report. ON displays a trace report. TRACEONLY displays a trace report, but does not print query data, if any. EXPLAIN shows the query execution path by performing an EXPLAIN PLAN. STATISTICS displays SQL statement statistics.

SQL> set autot on exp stat;
SQL> /

A
----------
1


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01
|   1 |  TABLE ACCESS FULL| TEST |     1 |    13 |     2   (0)| 00:00:01
------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
3  consistent gets
0  physical reads
0  redo size
415  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

Execution Plan gives us the cost and the rows etc while statistics gives us the more physical reads and blocks information.


There is another type of parsing also, called as "SOFTER SOFT PARSE". It makes use of the initialization parameter session_cached_cursors. If this parameter is set to some value, then in that case; Oracle takes you query, looks into the session cursor cache, picks up the cursor and reuses it. This involves lesser work, even lesser than SOFT Parse.

SQL> show parameter session_cached;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------
session_cached_cursors               integer     50

If it is enabled, then two parameters play an important part from v$statname :- 

SQL> select a.name, b.value
2  from v$statname a, v$mystat b
3  where a.statistic# = b.statistic#
4  and lower(a.name) like '%cursor ca%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 0
session cursor cache count                                              0

So when a query is parsed for three times and caching of cursors is enabled, it increases the cursor cache count and caches the cursor ... And from next time onwards, uses that cursor increasing the cache hits count.

SQL> select a.name, b.value
2  from v$statname a, v$mystat b
3  where a.statistic# = b.statistic#
4  and lower(a.name) like '%cursor ca%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 0
session cursor cache count                                              1

SQL> select * from test;

A
----------
1

SQL> select a.name, b.value
2  from v$statname a, v$mystat b
3  where a.statistic# = b.statistic#
4  and lower(a.name) like '%cursor ca%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 1
session cursor cache count                                              3

( Cache Count increased) 


SQL> select * from test;

A
----------
1

SQL> select a.name, b.value
2  from v$statname a, v$mystat b
3  where a.statistic# = b.statistic#
4  and lower(a.name) like '%cursor ca%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 3
session cursor cache count                                              3

(Cache Hit increasing ... It increased twice, once for test query and another to display the statistics)...



So, in short, it has three types of parsing: -

1. HARD Parse: - First time query is being executed, parse it (syntax check, semantic check), hash it, look in the shared pool, not present, perform transformations, optimize it etc....
2. SOFT Parse: - Executed before, parse it, hash it, look in the shared pool, present, use it.
3. SOFTER soft Parse: - Look into session cursor cache, found, uses it. (But only if session_cached_cursors set to some value).

In database system, the ratio of SOFT Parse/HARD Parse should be very high since it implies the same query was reused again and again and less work was done.