Oracle7 Server SQL Reference

ALTER RESOURCE COST

Purpose

To specify a formula to calculate the total resource cost used in a session. For any session, this cost is limited by the value of the COMPOSITE_LIMIT parameter in the user's profile.

Prerequisites

You must have ALTER RESOURCE COST system privilege.

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match DBLOW or you must have WRITEDOWN system privileges.

Syntax

Keywords and Parameters

integer is the weight of each resource.

Usage Notes

The ALTER RESOURCE COST command specifies the formula by which Oracle7 calculates the total resource cost used in a session. With this command, you can assign a weight to each of these resources:

CPU_PER_SESSION

The amount of CPU time used by a session measured in hundredths of seconds.

CONNECT_TIME

The amount of CPU time used by a session measured in hundredths of seconds.

CPU_PER_SESSION

The elapsed time of a session measured in minutes.

LOGICAL_READS_PER_SESSION

The number of data blocks read during a session, including blocks read from both memory and disk.

PRIVATE_SGA

The number of bytes of private space in the System Global Area (SGA) used by a session. This limit only applies if you are using the multi-threaded server architecture and allocating private space in the SGA for your session.

Oracle7 calculates the total resource cost by multiplying the amount of each resource used in the session by the resource's weight and summing the products for all four resources. Both the products and the total cost are expressed in units called service units.

Although Oracle7 monitors the use of other resources, only these four can contribute to the total resource cost for a session. For information on all resources, see the CREATE PROFILE command .

The weight that you assign to each resource determines how much the use of that resource contributes to the total resource cost. Using a resource with a lower weight contributes less to the cost than using a resource with a higher weight. If you do not assign a weight to a resource, the weight defaults to 0 and use of the resource subsequently does not contribute to the cost. The weights you assign apply to all subsequent sessions in the database.

Once you have specified a formula for the total resource cost, you can limit this cost for a session with the COMPOSITE_LIMIT parameter of the CREATE PROFILE command. If a session's cost exceeds the limit, Oracle7 aborts the session and returns an error. For information on establishing resource limits, see the CREATE PROFILE command . If you use the ALTER RESOURCE COST command to change the weight assigned to each resource, Oracle7 uses these new weights to calculate the total resource cost for all current and subsequent sessions.

Example

The following statement assigns weights to the resources CPU_PER_SESSION and CONNECT_TIME:

ALTER RESOURCE COST

	CPU_PER_SESSION 100

 	CONNECT_TIME      1

The weights establish this cost formula for a session:

T = (100 * CPU) + CON

where:

is the total resource cost for the session expressed in service units.

CPU

is the CPU time used by the session measured in hundredths of seconds.

CON

is the elapsed time of a session measured in minutes.

Because the above statement assigns no weight to the resources LOGICAL_READS_PER_SESSION and PRIVATE_SGA, these resources do not appear in the formula.

If a user is assigned a profile with a COMPOSITE_LIMIT value of 500, a session exceeds this limit whenever T exceeds 500. For example, a session using 0.04 seconds of CPU time and 101 minutes of elapsed time exceeds the limit. A session 0.0301 seconds of CPU time and 200 minutes of elapsed time also exceeds the limit.

You can subsequently change the weights with another ALTER RESOURCE statement:

ALTER RESOURCE COST

	LOGICAL_READS_PER_SESSION 2

	CONNECT_TIME              0

These new weights establish a new cost formula:

T = (100 * CPU) + (2 * LOG)

where:

T CPU

are the same as in the previous formula.

LOG

is the number of data blocks read during the session.

This ALTER RESOURCE COST statement changes the formula in these ways:

Because the statement assigns a weight to the LOGICAL_READS_PER_SESSION resource, this resource now appears in the formula.

Because the statement assigns a weight of 0 to the CONNECT_TIME resource, this resource no longer appears in the formula.

Because the statement omits a weight for the CPU_PER_SESSION resource and the resource was already assigned a weight, the resource remains in the formula with its original weight.