Which three are tasks performed in the hard parse stage of a SQL statement executions?
A.
Semantics of the SQL statement are checked.
B.
The library cache is checked to find whether an existing statement has the same hash value.
C.
The syntax of the SQL statement is checked.
D.
Information about location, size, and data type is defined, which is required to store fetched
values in variables.
E.
Locks are acquired on the required objects.
Explanation:
Parse operations fall into the following categories, depending on the type of
statement submitted and the result of the hash check:
A) 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 (see
“Latches”). Latch contention increases statement execution time and decreases concurrency.
B) 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
cursor cache 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.
Incorrect:
A, C: During the parse call, the database performs the following checks:
Syntax Check
Semantic Check
Shared Pool CheckThe hard parse is within Shared Pool check.
Reference: Oracle Database Concepts 11g, SQL Parsing
B,D,E seems correct.
A,C,E correct.
B – incorrect.
The database checks its shared pool BEFORE it decides if it needs SOFT parse or HARD parse. If it doesn’t find the same parsed query in the shared pool, it begins HARD parsing.
D – incorrect.
The answer relates to result of the query which goes after parsing.
During a hard parse, Oracle performs syntactic and semantic checking, checks the object and system privileges, builds the optimal execution plan, and finally loads it into the library cache. A hard parse involves a lot more CPU usage and is inefficient compared to a soft parse, which depends on reusing previously parsed statements. Hard parsing involves building all parse information from scratch, and therefore it’s more resource intensive. Besides involving a higher CPU usage, hard parsing involves a large number of latch gets, which may increase the response time of the query.
Vasiliy,
Syntax and semantics checks are done before soft parse and are not part of hard parsing.
Links are below:
for 12c https://docs.oracle.com/database/121/TGSQL/tgsql_sqlproc.htm#TGSQL175 (Figure 3-1 and descriprtion)
for 11g http://docs.oracle.com/cd/E25054_01/server.1111/e25789/sqllangu.htm#CNCPT1740 (Figure 7.3 and description)
Also semantic is checking after hash comparison. But it’s checking during soft parse (documents above)
B. part of soft parse
D. Describe and define phases- after parsing (soft or hard).
So the only true variant is E. But why answer requires 3 true answers?
B, D & E are correct.
as A & C occurs during SQL Parsing but not during Hard parsing
Another sign that this test is a mess:
https://docs.oracle.com/database/121/TGSQL/tgsql_sqlproc.htm#TGSQL176
Checking Library Cache is NOT part of the hard parse according to Oracle itself; but you don’t have any other options here. You HAVE TO choose it otherwise you only have two answers…
This is BS!
A, B, C are correct.
We have only two kinds of parsing, soft or hard.
Let’s look at an article quoted by BT
https://docs.oracle.com/database/121/TGSQL/tgsql_sqlproc.htm#TGSQL178
In this article we can see that we have steps which are common for both types of parsing:
– Syntax Check (C)
– Semantic Check (A)
– Shared Pool Check (B)
I passed this exam today 🙂 The question was absolutly the same. I am still sure a b c incorrect. d e correct. but i had choose 3 answers 🙂 B D E but accoding oracle feedback maybe I made mistake 🙂 Very difficult exam. Good luck coleagues 🙂
Are all questions the same like here? Or something new appeared on exam?
A C E
hard parse includes these steps:
1. Loading into shared pool – The SQL source code is loaded into RAM for parsing. (the “hard” parse step)
2. Syntax parse – Oracle parses the syntax to check for misspelled SQL keywords.
3. Semantic parse – Oracle verifies all table & column names from the dictionary and checks to see if you are authorized to see the data.
4. Optimization – Oracle them creates an execution plan, based on your schema statistics (or maybe dynamic sampling in 10g).
5. Create executable – Oracle builds an executable file with native file calls to service the SQL query.
Parse lock is synonym of library cache lock. Oracle wants to block any modification on the objects while it parses the SQL statement, and it does so using library cache lock.
Oracle downgrades the library cache lock to NULL mode (which is called breakable parse lock)
and releases it when the cursor is closed.