Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 14 of 17
To specify or change the formula by which Oracle calculates the total resource cost used in a session. The weight that you assign to each resource determines how much the use of that resource contributes to the total resource cost. 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.
Oracle calculates the total resource cost by first multiplying the amount of each resource used in the session by the resource's weight, and then summing the products for all four resources. For any session, this cost is limited by the value of the COMPOSITE_LIMIT
parameter in the user's profile. Both the products and the total cost are expressed in units called service units.
Although Oracle monitors the use of other resources, only the four resources shown in the syntax can contribute to the total resource cost for a session. For information on all resources, see "CREATE PROFILE".
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
statement. If a session's cost exceeds the limit, Oracle aborts the session and returns an error. For information on establishing resource limits, see "CREATE PROFILE". If you use the ALTER RESOURCE COST
statement to change the weight assigned to each resource, Oracle uses these new weights to calculate the total resource cost for all current and subsequent sessions.
You must have ALTER RESOURCE COST
system privilege.
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:
cost = (100 * CPU_PER_SESSION) + (1 * CONNECT_TIME)
where the values of CPU_PER_SESSION
and CONNECT_TIME
are either values in the DEFAULT
profile or in the profile of the user of the session.
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 COST 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:
cost = (100 * CPU_PER_SESSION) + (2 * LOGICAL_READ_PER_SECOND)
where the values of CPU_PER_SESSION
and LOGICAL_READS_PER_SECOND
are either the values in the DEFAULT
profile or in the profile of the user of this session.
This ALTER RESOURCE COST
statement changes the formula in these ways:
CPU_PER_SESSION
resource and the resource was already assigned a weight, so the resource remains in the formula with its original weight.
LOGICAL_READS_PER_SESSION
resource, so this resource now appears in the formula.
CONNECT_TIME
resource, so this resource no longer appears in the formula.
PRIVATE_SGA
resource and the resource was not already assigned a weight, so the resource still does not appear in the formula.
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|