Home » Oracle » 1z0-117 » Which two are true about parallel statement queuing when this plan is active?
Examine the parallelism parameter for your instance:
Now examine the 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.
Correct Answer: CD
Explanation/Reference:
*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.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
