8.1 What is the Parser?

---

What happens when an SQL statement is sent to the database?

A portion of memory is allocated to store the results of an SQL statement. This chunk of memory is called a cursor. Amongst other things a cursor contains a pointer to the row in an index or table currently being fetched from the database. Unless a cursor is declared explicitly in a tool such as PL/SQL then a cursor is implicitly created for every SQL SELECT statement submitted to the Oracle Database. When an SQL statement has finished executing, the cursor can either be left in memory as a reusable, already allocated chunk of memory or it can be removed from memory altogether.

The SQL statement is parsed. What is parsing? The meaning of the word parsing is that of syntactical and perhaps grammatical or semantic analysis of a sentence. In programming terms parsing is a syntax check on a line of program code and between related lines. SQL statements are lines of program code. So the parser is effectively a compiler of SQL statements, much like a C compiler is a compiler or syntax analyzer of C programming language commands.

The Oracle SQL parser does a few other things. There is a portion of memory or a buffer in the Oracle Database configuration called the shared pool. The shared pool is used to store parsed execution plans for previously executed SQL statements. These previously parsed execution plans can be used to re-execute the SQL statement if exactly, and I mean exactly, the same string is executed once again.

Note 

 Oracle Database 10 Grid   Oracle Database 10g has Optimizer improvements such as less of a need for SQL code statements to be case sensitive.

Thus the Optimizer does not have to do its work all over again. The result is that if the parser finds an existing SQL code statement in the shared pool it does not have to call the Optimizer, and recalculate the execution plan for the SQL statement.

Parses can be split into two categories:

• Hard parse.   No match is found in the shared pool for an SQL statement never before submitted to the database engine.

• Soft parse.   A match is found for an SQL statement perhaps submitted to the database engine by a different session.

Hard parses are more expensive in system resources than soft parses but reparsing is best avoided altogether if possible.

---