Your database supports a workload consisting of three categories of SQL statements:
- Statements that should execute in less than one minute
- Statement that may execute for up to 15 minutes
- Statements that may be executed for more than 15 minutes
You set PARALLEL_DEGREE_POLICY to Auto.
You plan to prioritize queued statements by using the Database Resource manager.
Which two are true about parallelism prioritization by a consumer group?
A. PARALLEL_TARGET_PERCENTAGE is used to prioritize a consumer group’s use of the overall PARALLEL_SERVER_TARGET.
B. Queuing is done for a consumer group exceeding its percentage, even if the number of busy PX servers in the instance has not reached PARALLEL_SERVERS_TARGET.
C. PARALLEL_TARGET_PERCENTAGE us used to prioritize a consumer group’s use of the overall SPAN CLASS = ‘OracleCode’> PARALLEL_MAX_SERVERS.
D. Having separate queues for consumer groups requires the use of management attributes (MGMT_P1, MGMT_P2 etc. . . )
E. Separate queue timeout using PARALLEL_QUEUE_TIMEOUT require the use of management attributes (MGMT_P1, MGMT_P2 etc . . . groups)
Correct Answer: AD
Explanation/Reference:
A:Parallel Target Percentage
It is possible for a single consumer group to launch enough parallel statements to use all the available parallel servers. If this happens, when a high-priority parallel statement from a different consumer group is run, no parallel servers are available to allocate to this group. You can avoid such a scenario by limiting the number of parallel servers that can be used by a particular consumer group.
Use the PARALLEL_TARGET_PERCENTAGE directive attribute to specify the maximum percentage of the parallel server pool that a particular consumer group can use. The number of parallel servers used by a particular consumer group is counted as the sum of the parallel servers used by all sessions in that consumer group.
Incorrect:
Not B: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.
Not C: Would be true if we replacePARALLEL_MAX_SERVERSwithPARALLEL_SERVER_TARGET.
Not E:The PARALLEL_QUEUE_TIMEOUT directive attribute enables you to specify the maximum time, in seconds, that a parallel statement can wait in the parallel statement queue before it is timed out. The PARALLEL_QUEUE_TIMEOUT attribute can be set for each consumer group. This attribute is applicable even if you do not specify other management attributes (mgmt_p1, mgmt_p2, and so on) in your resource plan.
Note:
*PARALLEL_DEGREE_POLICY
AUTO
Enables automatic degree of parallelism, statement queuing, and in-memory parallel execution.
*The PARALLEL_TARGET_PERCENTAGE attribute enables you to specify when parallel statements from a consumer group can be queued. Oracle Database maintains a separate parallel statement queue for each consumer group.
*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.
*Oracle Database Resource Manager (the Resource Manager) enables you to optimize resource allocation among the many concurrent database sessions.
The elements of the Resource Manager are:
/Resource consumer groupA group of sessions that are grouped together based on resource requirements. The Resource Manager allocates resources to resource consumer groups, not to individual sessions.
/Resource planA container for directives that specify how resources are allocated to resource consumer groups. You specify how the database allocates resources by activating a specific resource plan.
/Resource plan directiveAssociates a resource consumer group with a particular plan and specifies how resources are to be allocated to that resource consumer group.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
