OCP-043 resources by using Resource Manager

86. In your production database, you observe that users' transactions are consuming a lot of resources
and you plan to impose resource restrictions by using Resource Manager.
For which three resources can you enable usage restrictions by using Resource Manager? (Choose
three.)

A.CPU usage
B.degree of parallelism
C.number of open cursors
D.number of sorts performed
E.idle time for blocking sessions
F.number of disk I/O operations performed
G.number of memory I/O operations performed
Answer: ABE

Resource Plan Directives

How resources are allocated to resource consumer groups is specified in resource allocation directives. The Database Resource Manager provides several means of allocating resources.

CPU Method

This method enables you to specify how CPU resources are to be allocated among consumer groups or subplans. Multiple levels of CPU resource allocation (up to eight levels) provide a means of prioritizing CPU usage within a plan schema. Consumer groups and subplans at level 2 get resources that were not allocated at level 1 or were not consumed by a consumer group or subplan at level 1. The same rules apply to levels 3, 4, and so on. Multiple levels not only provide a way of prioritizing, but they provide a way of explicitly specifying how all primary and leftover resources are to be used.

See Figure 24-3 for an example of a multilevel plan schema.

Note:

When there is only one level, unused allocation by any consumer group or subplan can be used by other consumer groups or subplans in the level.

Active Session Pool with Queuing

You can control the maximum number of concurrently active sessions allowed within a consumer group. This maximum designates the active session pool. When a session cannot be initiated because the pool is full, the session is placed into a queue. When an active session completes, the first session in the queue can then be scheduled for execution. You can also specify a timeout period after which a job in the execution queue (waiting for execution) will timeout, causing it to terminate with an error.

An entire parallel execution session is counted as one active session.

Degree of Parallelism Limit

Specifying a parallel degree limit enables you to control the maximum degree of parallelism for any operation within a consumer group.

Automatic Consumer Group Switching

This method enables you to control resources by specifying criteria that, if met, causes the automatic switching of sessions to another consumer group. The criteria used to determine switching are:

• Switch group: specifies the consumer group to which this session is switched if the other (following) criteria are met

• Switch time: specifies the length of time that a session can execute before it is switched to another consumer group

• Switch time in call: specifies the length of time that a session can execute before it is switched to another consumer group. Once the top call finishes, the session is restored to its original consumer group.

• Use estimate: specifies whether the database is to use its own estimate of how long an operation will execute

The Database Resource Manager switches a running session to switch group if the session is active for more than switch time seconds. Active means that the session is running and consuming resources, not waiting idly for user input or waiting for CPU cycles. The session is allowed to continue running, even if the active session pool for the new group is full. Under these conditions a consumer group can have more sessions running than specified by its active session pool. Once the session finishes its operation and becomes idle, it is switched back to its original group.

If use estimate is set to TRUE, the Database Resource Manager uses a predicted estimate of how long the operation will take to complete. If the database estimate is longer than the value specified as the switch time, then the database switches the session before execution starts. If this parameter is not set, the operation starts normally and only switches groups when other switch criteria are met.

Switch time in call is useful for three-tier applications where the middle tier server is using session pooling. At the end of every top call, a session is switched back to its original consumer group--that is, the group it would be in had it just logged in. A top call in PL/SQL is an entire PL/SQL block being treated as one call. A top call in SQL is an individual SQL statement issued separately by the client being treated as a one call.

You cannot specify both switch time in call and switch time.

Canceling SQL and Terminating Sessions

You can also specify directives to cancel long-running SQL queries or to terminate long-running sessions. You specify this by setting CANCEL_SQL or KILL_SESSIONas the switch group.

Execution Time Limit

You can specify a maximum execution time allowed for an operation. If the database estimates that an operation will run longer than the specified maximum execution time, the operation is terminated with an error. This error can be trapped and the operation rescheduled.

Undo Pool

You can specify an undo pool for each consumer group. An undo pool controls the amount of total undo that can be generated by a consumer group. When the total undo generated by a consumer group exceeds its undo limit, the current DML statement generating the redo is terminated. No other members of the consumer group can perform further data manipulation until undo space is freed from the pool.

Idle Time Limit

You can specify an amount of time that a session can be idle, after which it will be terminated. You can further restrict such termination to only sessions that are blocking other sessions.