The previous section described some of the ways that you can end up with poor performance through a lack of CPU resources. The Database Resource Manager (DRM), first introduced in Oracle8i and enhanced in Oracle9i, can help you automatically avoid some of these problems.
DRM works by leveraging consumer groups you've identified and enabling you to place limits on the amount of computer resources that can be used by that group. Implementing the DRM ensures that one group or member of a group does not end up using an excessive amount of any one resource, as well as acting to deliver guaranteed service levels for different sets of users.
The following DRM features can be combined to protect against poor performance:
Predicting resource utilization
The DRM can leverage the query optimizer cost computations to predict the amount of resources that a given query will take and the query execution time. Note that, by default, the query optimizer uses a CPU + I/O cost model in Oracle Database 10g. In Oracle9i, the query optimizer used an I/O cost model based on single block reads.
Switching consumer groups
The DRM can switch consumer groups dynamically. You might want to give a particular consumer group a high allocation of CPU resources. But if a single query from that group looks as if it will take up too many CPU resources and affect the overall performance of the machine, the consumer group can be switched to another group that has a smaller CPU allocationfor example, a consumer group designed for batch operations.
Limiting number of connections
The DRM can limit the number of connections for any particular consumer group. If the limit on connections for a group has been reached and another connection request comes in, the connection request is queued until an existing connection is freed. By limiting the overall number of connections for a consumer group, you can place some rough limits on the overall resources that particular group might require.