Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Avoiding Performance Problemswith Oracle9i

The previous section described some of the ways that you can end up with poor performance through a lack of CPU resources. In Oracle9i, the Database Resource Manager (DRM) has been enhanced to help you automatically avoid some of these problems.

The following DRM features can be combined to protect against poor performance:

Predicting CPU time

The DRM can predict the amount of CPU time that a given query will take.

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 allocation—for 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.