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.
Correct Answer: BDE
Explanation/Reference:
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 Check
The hard parse is within Shared Pool check.
Reference:Oracle Database Concepts11g,SQL Parsing
Download Printable PDF. VALID exam to help you PASS.
|
|