You have enabled DML by issuing: ALTER session ENABLE PARALLEL DML;
The PARELLEL_DEGREE_POLICY initialization parameter is set to AUTO.
Which two options true about DML statements for which parallel execution is requested?
A.
Statements for which PDML is requested will execute serially estimated time is less than the
time specified by the PARALLEL_MIN_THRESHOLD parameter.
B.
Statements for which PDML is requested will be queued if the number of busy parallel
execution servers greater than PARALLEL_MIN_SERVERS parameter.
C.
Statements for which PDML is requested will always execute in parallel if estimated execution
in parallel if estimated execution time is greater than the time specified by the
PARELLEL_MIN_TIME_THRESHOLD parameter.
D.
Statements for which PDML is requested will be queued if the number of busy parallel
execution servers is greater than PARELLEL_SERVERS_TARGET parameter.
E.
Statement for which PDML is requested will be queued if the number of busy parallel execution
servers is greater than PARELLEL_DEGREE_LIMIT parameter.
Explanation:
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.
D: 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.
Note:
* PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism,
statement queuing, and in-memory parallel execution will be enabled.
AUTO
Enables automatic degree of parallelism, statement queuing, and in-memory parallel execution.
* PARALLEL_MIN_SERVERS specifies the minimum number of parallel execution processes for
the instance. This value is the number of parallel execution processes Oracle creates when the
instance is started.
Reference: Oracle Database Reference; PARALLEL_SERVERS_TARGET
Why not A?
A is incorrect because parameter PARALLEL_MIN_THRESHOLD doesn’t exist
Today I pass exam: in A parameter name is correct: PARALLEL_MIN_TIME_THRESHOLD. Generally in Exam I did not saw intentional incorrect names.
C+D
A, D
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 10 seconds. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.
Source: https://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams185.htm
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.
Source: https://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams186.htm
A – because below this time the statement isn’t even considered for automatic degree of parallelism
D – because this parameter is dedicated for this functions (specifies the number of parallel server processes allowed to run parallel statements before statement queuing will be used)
PS: C is wrong, because if estimated execution time is greater than the time specified by the PARELLEL_MIN_TIME_THRESHOLD parameter then the statement is only (!) considered for automatic degree of parallelism (in the fact: maybe parallel, maybe serial).