As a Database Administrator (DBA), managing and controlling the resources consumed by user sessions is crucial for maintaining a stable and performant Oracle database.
Uncontrolled resource usage by a single session can lead to performance degradation, impacting all other users.
🟥Oracle provides a powerful, yet often underutilized, mechanism for this purpose:
the Resource Cost feature, which works in conjunction with user Profiles and the COMPOSITE_LIMIT parameter.
This technical post will demystify Oracle's Resource Cost, explain its relationship with user profiles, detail the COMPOSITE_LIMIT option, and outline the conditions under which a DBA can effectively use these limitations.
What is `RESOURCE_COST` in Oracle?
The term `RESOURCE_COST` in Oracle refers to a calculated, weighted value that represents the total consumption of specific system resources by a single database session. It is not a single, directly measured metric but rather a formula-based score.
The purpose of the resource cost mechanism is to provide a flexible way to limit a session's overall impact on the system by combining multiple resource metrics into a single, manageable value.
The Four Resources and the Calculation Formula
Oracle's resource cost calculation is based on a weighted sum of four specific session-level resource consumption metrics.
A DBA uses the `ALTER RESOURCE COST` statement to assign a weight (or "cost") to each of these four resources, which are measured in service units.
The four resources that contribute to the total resource cost are:
1. `CPU_PER_SESSION`: The total CPU time a session can use, measured in hundredths of a second.
2. `CONNECT_TIME`: The total elapsed time a session can be connected to the database, measured in minutes.
3. `LOGICAL_READS_PER_SESSION`: The total number of data blocks read from the buffer cache and disk during a session (a measure of I/O activity).
4. `PRIVATE_SGA`: The number of bytes of memory a session can use from the Shared Global Area (SGA) for private memory allocations.
The total resource cost for a session is calculated as a weighted sum of the four resources. This calculation combines the amount of each resource consumed by the session with the weight (or "cost") assigned to that resource via the ALTER RESOURCE COST statement.The calculation is a simple addition of the products of resource consumption and its assigned weight:
📘Total Resource Cost =
(CPU_PER_SESSION * CPU Weight)
+ (CONNECT_TIME * Connect Weight)
+ (LOGICAL_READS_PER_SESSION * Logical Read Weight)
+ (PRIVATE_SGA * Private SGA Weight)
The sum of these four products yields the Total Resource Cost (in service units), which is then compared against the COMPOSITE_LIMIT defined in the user's profile.
Use the ALTER RESOURCE COST statement to specify or change the formula by which Oracle Database calculates the total resource cost used in a session.
Although Oracle Database monitors the use of other resources, only the four resources shown in the syntax can contribute to the total resource cost for a session.
This statement lets you apply weights to the four resources.
Oracle Database then applies the weights to the value of these resources that were specified for a profile to establish a formula for calculating total resource cost.
You can limit this cost for a session with the COMPOSITE_LIMIT parameter of the CREATE PROFILE statement.
If the resource cost of a session exceeds the limit, then Oracle Database aborts the session and returns an error.
🚧If you use ALTER RESOURCE COST statement to change the weight assigned to each resource, then Oracle Database uses these new weights to calculate the total resource cost for all current and subsequent sessions.
Adjusting Resource Weights with `ALTER RESOURCE COST`
The `ALTER RESOURCE COST` statement is the tool a DBA uses to define the formula for the resource cost calculation. By default, all weights are zero, meaning the resource cost is not actively calculated or enforced.
Syntax Example:
ALTER RESOURCE COST
CPU_PER_SESSION 100
CONNECT_TIME 5
LOGICAL_READS_PER_SESSION 2
PRIVATE_SGA 0;
In this example, every 100 hundredths of a second of CPU time, 5 minutes of connection time, and 2 logical reads all contribute equally to the total resource cost. The weight for `PRIVATE_SGA` is set to 0, effectively excluding it from the calculation.
Key Point: When a DBA uses `ALTER RESOURCE COST` to change the weights, Oracle immediately uses these new weights to calculate the total resource cost for all current and subsequent sessions.
The `COMPOSITE_LIMIT` Option in Profiles
The `COMPOSITE_LIMIT` is the parameter within a database Profile that directly utilizes the calculated resource cost. A profile is a named set of resource limits and password parameters that can be assigned to one or more users.
`COMPOSITE_LIMIT` specifies the maximum total resource cost (in service units) that a session assigned to that profile is allowed to incur.
Relationship with Profiles
The relationship between `RESOURCE_COST` and `COMPOSITE_LIMIT` is as follows:
1. Define the Formula: The DBA uses `ALTER RESOURCE COST` to define the weighted formula for calculating the total resource cost.
2. Set the Limit: The DBA uses the `CREATE PROFILE` or `ALTER PROFILE` statement to set the `COMPOSITE_LIMIT` for a specific profile.
Syntax Example for Setting the Limit:
CREATE PROFILE LIMITED_USERS LIMIT
SESSIONS_PER_USER 1
CPU_PER_SESSION UNLIMITED
CONNECT_TIME UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
COMPOSITE_LIMIT 5000;
In this example, individual resource limits are set to `UNLIMITED`, but the session is capped by a `COMPOSITE_LIMIT` of 5000 service units.
Enforcement Mechanism
If a session's calculated total resource cost exceeds the `COMPOSITE_LIMIT` defined in its assigned profile, Oracle Database will immediately abort the session and return an error to the user. This is a hard limit designed to prevent runaway sessions from monopolizing system resources.
When and With Which Conditions a DBA Can Use These Limitations
The resource cost mechanism is a powerful tool for DBAs to implement fine-grained resource governance. A DBA can use these limitations under the following conditions and for the following purposes:
Summary of Key Concepts
By mastering the use of `ALTER RESOURCE COST` and `COMPOSITE_LIMIT`, DBAs can move beyond simple, single-metric limits and implement a sophisticated, weighted resource governance policy that is tailored to the specific needs of their database environment and user base.
🛑Limiting Memory Use for User Sessions
To restrict the memory used by each client session from the SGA, set a resource limit using PRIVATE_SGA.
PRIVATE_SGA defines the number of bytes of memory used from the SGA by a session. However, this parameter is rarely used, because most DBAs do not limit SGA consumption on a user-by-user basis.
No comments:
Post a Comment