481.Which statement is true about a running session that belongs to the online transaction processing

(OLTP) group?

OCP-1Z0-053-V13.02-481题_ide

 

A. It permanently switches to the low_group consumer group if the session exceeds 10,000 I/O requests

or 2,500 MB of data transfer.

B. It performs the first 10000 I/O requests or 2,500 MB of data transfer in the LOW-GROUP consumer

group, and then switches to the original group.

C. It switches to the LOW_GROUP consumer group if the session exceeds 10000 I/O requests or 2500

MB of data transfer and returns to the original group after the operation.

D. It switches to the LOW_GROUP consumer group if the session exceeds 10000 I/O requests or 2500

MB of data transfer for queries, but not for data manipulation language (DML) operations.

Answer: D

 

 

 

从官方文档可知，此题选C.

 

Example 2

The following PL/SQL block creates a resource plan directive for the OLTP group that temporarily switches any session in that group to the LOW_GROUP consumer group if the session exceeds 10,000 I/O requests or exceeds 2,500 Megabytes of data transferred. The session is returned to its original group after the offending top call is complete.

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
   PLAN                => 'DAYTIME',
   GROUP_OR_SUBPLAN    => 'OLTP',
   COMMENT             => 'OLTP group',
   MGMT_P1             => 75,
   SWITCH_GROUP        => 'LOW_GROUP',
   SWITCH_IO_REQS      => 10000,
   SWITCH_IO_MEGABYTES => 2500,
   SWITCH_FOR_CALL     => TRUE);
END;
/

 

Parameters

Table 120-7 CREATE_PLAN_DIRECTIVE Procedure Parameters

Parameter Description

plan

Name of the resource plan

group_or_subplan

Name of the consumer group or subplan

comment

Comment for the plan directive

cpu_p1

-- deprecated: use mgmt_p1 instead

cpu_p2

-- deprecated: use mgmt_p2 instead)

cpu_p3

-- deprecated: use mgmt_p3 instead)

cpu_p4

-- deprecated: use mgmt_p4 instead)

cpu_p5

-- deprecated: use mgmt_p5 instead)

cpu_p6

-- deprecated: use mgmt_p6 instead)

cpu_p7

-- deprecated: use mgmt_p7 instead)

cpu_p8

-- deprecated: use mgmt_p8 instead)

active_sess_pool_p1

Specifies maximum number of concurrently active sessions for a consumer group. Default is NULL, which means unlimited.

queueing_p1

Specified time (in seconds) after which a job in the inactive session queue (waiting for execution) will time out. Default is NULL, which means unlimited.

parallel_degree_limit_p1

Specifies a limit on the degree of parallelism for any operation. Default is NULL, which means unlimited.

switch_group

Specifies consumer group to switch to, once a switch condition is met. If the group name is 'CANCEL_SQL', then the current call is canceled when the switch condition is met. If the group name is 'KILL_SESSION', then the session is killed when the switch condition is met. Default is NULL.

switch_time

Specifies time (in CPU seconds) that a session can execute before an action is taken. Default is NULL, which means unlimited.

switch_estimate

If TRUE, tells Oracle to use its execution time estimate to automatically switch the consumer group of an operation before beginning its execution. Default is FALSE.

max_est_exec_time

Specifies the maximum execution time (in CPU seconds) allowed for a session. If the optimizer estimates that an operation will take longer than MAX_EST_EXEC_TIME, the operation is not started and ORA-07455 is issued. If the optimizer does not provide an estimate, this directive has no effect. Default is NULL, which means unlimited.

undo_pool

Limits the size in kilobytes of the undo records corresponding to uncommitted transactions by this consumer group

max_idle_time

Indicates the maximum session idle time. Default is NULL, which means unlimited.

max_idle_blocker_time

Maximum amount of time in seconds that a session can be idle while blocking another session's acquisition of a resource

switch_time_in_call

Deprecated. If this parameter is specified, switch_time is set to switch_time_in_call (in seconds) and switch_for_call is effectively set to TRUE. It is better to use switch_time andswitch_for_call.

mgmt_p1

Resource allocation value for level 1 (replaces cpu_p1):

  • EMPHASIS - specifies the resource percentage at the first level

  • RATIO - specifies the weight of resource usage

mgmt_p2

Resource allocation value for level 2 (replaces cpu_p2)

  • EMPHASIS - specifies the resource percentage at the second level

  • RATIO - non-applicable

mgmt_p3

Resource allocation value for level 3 (replaces cpu_p3)

  • EMPHASIS - specifies the resource percentage at the third level

  • RATIO - non-applicable

mgmt_p4

Resource allocation value for level 4 (replaces cpu_p4)

  • EMPHASIS - specifies the resource percentage at the fourth level

  • RATIO - non-applicable

mgmt_p5

Resource allocation value for level 5 (replaces cpu_p5)

  • EMPHASIS - specifies the resource percentage at the fifth level

  • RATIO - non-applicable

mgmt_p6

Resource allocation value for level 6 (replaces cpu_p6)

  • EMPHASIS - specifies the resource percentage at the sixth level

  • RATIO - non-applicable

mgmt_p7

Resource allocation value for level 7 (replaces cpu_p7)

  • EMPHASIS - specifies the resource percentage at the seventh level

  • RATIO - non-applicable

mgmt_p8

Resource allocation value for level 8 (replaces cpu_p8)

  • EMPHASIS - specifies the resource percentage at the eighth level

  • RATIO - non-applicable

switch_io_megabytes

Specifies the amount of I/O (in MB) that a session can issue before an action is taken. Default is NULL, which means unlimited.

switch_io_reqs

Specifies the number of I/O requests that a session can issue before an action is taken. Default is NULL, which means unlimited.

switch_for_call

Specifies that if an action is taken because of the switch_time, switch_io_megabytes, orswitch_io_reqs parameters, the consumer group is restored to its original consumer group at the end of the top call. Default is FALSE, which means that the original consumer group is not restored at the end of the top call.

max_utilization_limit

Specifies the maximum percentage of CPU that this Consumer Group or Sub-Plan can utilize. Valid values are 0% to 100%. NULL implies that there is no limit, or equivalently 100%. You can specify this attribute and leave mgmt_p1 through mgmt_p8 NULL.

parallel_target_percentage

Specifies the maximum percentage of the target number of parallel servers in an Oracle RAC environment that a consumer group can use. Any additional parallel statements that are launched from this consumer group will be queued. The default is NULL, which means that the limit is 100% of the target number. Valid values for queuing are in the range of 0 to 100 (%). For updates to the plan directive, the value of -1 will reset the value to NULL.

If a consumer group does not have any parallel statements running within an Oracle RAC database, the first parallel statement is allowed to exceed this limit.

The target number of parallel servers in an Oracle RAC environment is the sum of the parameter parallel_servers_target across all instances.

parallel_queue_timeout

Specifies the time (in seconds) that a query may remain in its Consumer Group's parallel statement queue before it is removed and terminated with an error (ORA- 07454).

Usage Notes

  • All parameters default to NULL. However, for the EMPHASIS CPU resource allocation method, this case would severely limit resources to all the users.

  • For max_idle_time and max_idle_blocker_time, PMON will check these limits once a minute. If it finds a session that has exceeded one of the limits, it will forcibly kill the session and clean up all its state.

  • The parameter switch_time_in_call is mostly useful for three-tier applications where the mid-tier server is implementing session pooling. By using switch_time_in_call, the resource usage of one client will not affect a future client that happens to be executed on the same session.