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.
Explanation:
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.
E,F
When manual parallel is specified. Queuing, Auto DOP an in-memory are disable. So we know that B, D and E are false.
C is already false because PARALLEL_MIN_TIME_THRESHOLD is only considered when PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED
See here: http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel005.htm
So Answers are: A and F
I agree with raka, A & F are correct answers , Queuing is allowed for auto DOP only not on manual.
A is definitely correct. Check under “For a statement-level PARALLEL hint:” in
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#BABHFDDH
It explicitly says about “Parallel(Manual):”
“PARALLEL (MANUAL): The optimizer is forced to use the parallel settings of the objects in the statement.”
so A is correct and also F, since the parallel settings for the object could be “no parallel.”
I agree that A and F are correct.