You instance has these parameter settings: 
Which three statements are true about these settings if no hints are used in a SQL statement?
A.
A statement estimated for more than 10 seconds always has its degree of parallelism computed
automatically.
B.
A statement with a computed degree of parallelism greater than 8 will be queued for a
maximum of 10 seconds.
C.
A statement that executes for more than 10 seconds always has its degree of parallelism
computed automatically.
D.
A statement with a computed degree of parallelism greater than 8 will raise an error.
E.
A statement with any computed degree of parallelism will be queued if the number of busy
parallel execution processes exceeds 64.
F.
A statement with a computed degree of parallelism of 20 will be queued if the number of
available parallel execution processes is less 5.
Explanation:
C (not A): 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.
PARALLEL_DEGREE_LIMIT integer
A numeric value for this parameter specifies the maximum degree of parallelism the optimizer can
choose for a SQL statement when automatic degree of parallelism is active. Automatic degree of
parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.
E: 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.
F: PARALELL_MIN_MINPERCENT
PARALLEL_MIN_PERCENT operates in conjunction
with PARALLEL_MAX_SERVERS and PARALLEL_MIN_SERVERS. It lets you specify the
minimum percentage of parallel execution processes (of the value
of PARALLEL_MAX_SERVERS) required for parallel execution. Setting this parameter ensures
that parallel operations will not execute sequentially unless adequate resources are available. The
default value of 0 means that no minimum percentage of processes has been set.
Consider the following settings:
PARALLEL_MIN_PERCENT = 50
PARALLEL_MIN_SERVERS = 5
PARALLEL_MAX_SERVERS = 10
If 8 of the 10 parallel execution processes are busy, only 2 processes are available. If you then
request a query with a degree of parallelism of 8, the minimum 50% will not be met.
Note: With automatic degree of parallelism, Oracle automatically decides whether or not a
statement should execute in parallel and what degree of parallelism the statement should use. The
optimizer automatically determines the degree of parallelism for a statement based on the
resource requirements of the statement. However, the optimizer will limit the degree of parallelism
used to ensure parallel server processes do not flood the system. This limit is enforced by
PARALLEL_DEGREE_LIMIT.Values:
CPU
IO
integer
A numeric value for this parameter specifies the maximum degree of parallelism the optimizer can
choose for a SQL statement when automatic degree of parallelism is active. Automatic degree of
parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.
Reference:
PARALLEL_MIN_TIME_THRESHOLD
PARALLEL_DEGREE_LIMIT
PARALELL_MIN_MINPERCENT
PARALELL_SERVERS_TARGET
Answers:
A. The optimizer first calculates a serial execution plan for the SQL statement; if the estimated execution elapsed time is greater than PARALLEL_MIN_TIME_THRESHOLD (10 seconds), the statement becomes a candidate for automatic DOP.
E.
F.
yes, I agree with raka, A is correct answer ,as Parallel Min Time Threshold works on Estimated execution for Automiatic DOP and not actual execution (C).
C is not correct.
F is false because of this
PARALLEL_MIN_PERCENT
This parameter enables users to wait for an acceptable DOP, depending on the application in use. The recommended value for the PARALLEL_MIN_PERCENT parameter is 0 (zero).
Setting this parameter to values other than 0 (zero) causes Oracle Database to return an error when the requested DOP cannot be satisfied by the system at a given time. For example, if you set PARALLEL_MIN_PERCENT to 50, which translates to 50 percent, and the DOP is reduced by 50 percent or greater because of the adaptive algorithm or because of a resource limitation, then Oracle Database returns ORA-12827. For example:
SELECT /*+ FULL(e) PARALLEL(e, 8) */ d.department_id, SUM(SALARY)
FROM employees e, departments d WHERE e.department_id = d.department_id
GROUP BY d.department_id ORDER BY d.department_id;
Oracle Database responds with this message:
ORA-12827: insufficient parallel query slaves available
A, D , E
This is one hell of a BS question: only two answers are correct: A and E.
F (as Raka mentions above) is wrong because PARALLEL_MIN_PERCENT=25 means oracle has to provide at least 25% of the requested DOP (in this case 25% of 20 = 5) and we have less than 5 servers available => ORA-12827
C is wrong because A is correct (wording issue. keyword: “Estimate”)
D is weird; how can a statement have a “computed” DOP more than 8 when PARALLEL_DEGREE_LIMIT=8?! Does it mean Oracle overrides its own parameters when calculating the Auto DOP?! Doesn’t oracle use the MIN(PARALLEL_DEGREE_LIMIT, ideal DOP – calculated by Optimizer) as the Auto DOP?! How could it be more than PARALLEL_DEGREE_LIMIT?!
A, E, F
Why F? We tell about ORA-12827 insufficient parallel query slaves available when PARALLEL_DEGREE_POLICY is set to manual or limited.
Source: http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams187.htm#REFRN10159
In the other case (I mean AUTO as we have in this question) we have parallel statement queueing.
PS: When PARALLEL_DEGREE_POLICY will be set to e.g. MANUAL and PARALLEL_MIN_PERCENT will be set to 0 we will be noticed that the degree of parallelism is downgraded (alternative to ORA-12827). PARALLEL_MIN_PERCENT lets us choose the best way (for us).