First page Back Continue Last page Graphics

SQL Statement Processing


Notes:

Before parsing an SQL statement, the user server process looks to see if the statement is already in the library cache.  This is part of the “soft parse”, which includes: syntax check, privilege check, and a SQL hash value (SHV) is computed on the statement text.  The SHV is used to identify the statement;  the library cache is searched for a statement with the same SHV.   If the statement is not found, the server process will then be forced to do a hard parse, which includes generating an execution plan and storing the result in the library cache.
The SHV will be different (almost always; Oracle actually also does a character-by-character comparison too because two identical strings could actually hash to the same value) for two queries unless the statement text is an exact match.  This means that the text, case (uppercase/lowercase), whitespace and any line breaks must match exactly.  
The following two statements, even though they produce the same result, are considered to be different because of the difference in case.  The second statement will not be able to re-use the parsed version of the first in the cursor.
SELECT * FROM test;
select * from test;

Notes for this slide continue on the next page…