The database users regularly complain about the difficulty in performing transactions. On
investigation, you find that some users perform long-running transactions that consume huge
amounts of space in the undo tablespace, which caused the problem. You want to control
the usage of the undo tablespace only for these user sessions and you do not want these
sessions to perform long-running operations.
How would you achieve this?
A.
Implement a profile for the users.
B.
Implement external roles for the users.
C.
Set the threshold for the undo tablespace.
D.
Implement a Database Resource Manager plan.
Correct answer is D.
How Does the Database Resource Manager Address These Problems?
The Oracle Database Resource Manager helps to overcome these problems by allowing the database more control over how machine resources are allocated.
Specifically, using the Database Resource Manager, you can:
Guarantee certain users a minimum amount of processing resources regardless of the load on the system and the number of users
Distribute available processing resources by allocating percentages of CPU time to different users and applications. In a data warehouse, a higher percentage may be given to ROLAP (relational on-line analytical processing) applications than to batch jobs.
Limit the degree of parallelism of any operation performed by members of a group of users
Create an active session pool. This pool consists of a specified maximum number of user sessions allowed to be concurrently active within a group of users. Additional sessions beyond the maximum are queued for execution, but you can specify a timeout period, after which queued jobs will terminate.
Allow automatic switching of users from one group to another group based on administrator defined criteria. If a member of a particular group of users creates a session that executes for longer than a specified amount of time, that session can be automatically switched to another group of users with different resource requirements.
Prevent the execution of operations that the optimizer estimates will run for a longer time than a specified limit
Create an undo pool. This pool consists of the amount of undo space that can be consumed in by a group of users.
Limit the amount of time that a session can be idle. This can be further defined to mean only sessions that are blocking other sessions.
Configure an instance to use a particular method of allocating resources. You can dynamically change the method, for example, from a daytime setup to a nighttime setup, without having to shut down and restart the instance.
Allow the cancellation of long-running SQL statements and the termination of long-running sessions.