Examine the parallelism parameter for your instance:
parallel_servers_target
Now examine the resource plan containing parallel statement directives:
Consumer Group resource plan containing parallel statement directives:
Which two are true about parallel statement queuing when this plan is active?
A.
Urgent_group sessions collectively can consume up to 64 parallel execution servers before
queuing starts for this consumer group.
B.
ETL_GROUP sessions can collectively consume up to 64 parallel execution servers before the
queuing starts for this consumer.
C.
A single OTHER_GROUPS session will execute serially once it is queued for six minutes.
D.
A single ETL_GROUP session can consume up to eight parallel execution servers.
E.
A single ETL_GROUP session can consume up to 32 parallel execution servers.
F.
A single OTHER_GROUPS session will execute in parallel once it is queued for six minutes.
Explanation:
* 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.
* PARALLEL_QUIT_TIMEOUT* PARALLEL_DEGREE_LIMIT
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:
The maximum degree of parallelism is limited by the number of CPUs in the system. The formula
used to calculate the limit isPARALLEL_THREADS_PER_CPU * CPU_COUNT * the number of
instances available (by default, all the opened instances on the cluster but can be constrained
using PARALLEL_INSTANCE_GROUP or service specification). This is the default.
The maximum degree of parallism the optimizer can use is limited by the I/O capacity of the
system. The value is calculated by dividing the total system throughput by the maximum I/O
bandwidth per process. You must run
the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure on the system in order to use
the IO setting. This procedure will calculate the total system throughput and the maximum I/O
bandwidth per process.
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 orLIMITED.
Answers
Correct
“A” because in this example, the parameter PARALLEL_SERVERS_TARGET is set to 64, which means that the number of parallel servers available is 64. The total number of parallel servers that can be used for parallel statement execution before URGENT_GROUP sessions with PARALLEL_DEGREE_POLICY set to AUTO are added to the parallel statement queue is equal to 64
“D” individually each ETL_GROUP session can consume up to 8 parallel execution servers because of PARALLEL_DEGREE_LIMIT_P1
https://docs.oracle.com/cd/E18283_01/server.112/e17120/dbrm006.htm#BABCIJDG
Not correct:
Normally “B” is not false. But i think that’s a mistake slip into the statement. If we suppose that PARALLEL_QUEUE_TIMEOUT is 50% for ETL_GROUP, so B is false.
“C” and “F” are false because when a parallel statement is timed out, the statement execution ends with the following error message:
ORA-07454: queue timeout, n second(s), exceeded
https://docs.oracle.com/cd/E18283_01/server.112/e17120/dbrm001.htm
“E” is false because single session execution can consume up to 8 parallel execution servers (limited by PARALLEL_DEGREE_LIMIT_P1 )
I agree, A & D are correct answers
A, B
D is not correct. A single ETL_GROUP session can consume up to 16 (rather than 8) parallel execution servers. It’s the DOP that is limited to 8, not the number of parallel servers.