Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

6.6 Database Resource Manager

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.