Home » Oracle » 1z0-117 » Which two are true about the execution of statement?
You enable auto degree of parallelism (DOP) for your database instance.
Examine the following query:
Which two are true about the execution of statement?
A. Dictionary DOP for the objects accessed by the query is used to determine the statement DOP.
B. Auto DOP is used to determine the statement DOP only if the estimated serial execution time exceeds PARALLEL_MIN_TIME_THRESHOLD.
C. Dictionary DOP is used to determine the statement DOP only if the estimated serial execution time exceeds PARALLEL_MIN_TIME_THRESHOLD.
D. The statement will be queued if insufficient parallel execution slaves are available to satisfy the statements DOP.
E. The statement will be queued if the number of busy parallel execution servers exceeds PARALLEL_SERVERS_TARGET.
F. The statements may execute serially.
Correct Answer: BF
Explanation/Reference:
Not B, not D:MANUAL - This is the default. Disables Auto DOP, statement queuing and in-memory parallel execution. It reverts the behavior of parallel execution to what it was previous to Oracle Database 11g, Release 2 (11.2).
C:PARALLEL_MIN_TIME_THRESHOLD specifies the minimum execution time a statement should have before the statement is considered for automatic degree of parallelism. By default, this is set to 30 seconds. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.
Note:
*PARELLEL (MANUAL)
You can use the PARALLEL hint to force parallelism. It takes an optional parameter: the DOP at which the statement should run.
The following example forces the statement to use Oracle Database 11g Release 1 (11.1) behavior:
SELECT /*+ parallel(manual) */ ename, dname FROM emp e, dept d
WHERE e.deptno=d.deptno;
*PARALLEL_SERVERS_TARGET specifies the number of parallel server processes allowed to run parallel statements before statement queuing will be used. When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle will queue SQL statements that require parallel execution, if the necessary parallel server processes are not available. Statement queuing will begin once the number of parallel server processes active on the system is equal to or greater than PARALLEL_SERVER_TARGET.
By default, PARALLEL_SERVER_TARGET is set lower than the maximum number of parallel server processes allowed on the system (PARALLEL_MAX_SERVERS) to ensure each parallel statement will get all of the parallel server resources required and to prevent overloading the system with parallel server processes.
Note that all serial (non-parallel) statements will execute immediately even if statement queuing has been activated.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
