Controlling Resources Using Profiles

Resource management limits can be enforced at the session level (as long as you are logged in to SQL*Plus or whatever interface you are using to interact with the database), at the individual command or query level, or both. Limits can be defined by profiles created with the CREATE PROFILE command, and resource limits are enabled with the RESOURCE_LIMIT initialization parameter or the ALTER SYSTEM command. Resource limits can be set for a profile and are enforced, but only when you enable the resource limitations for the associated database. These limitation enforcements can be enabled or disabled with either of two methods.

First, enabling limits before startup can be controlled with the RESOURCE_LIMIT initialization parameter. Valid values are TRUE or FALSE with FALSE being the default. Second, enabling limits when the database is open can be accomplished with the ALTER SYSTEM SET RESOURCE_LIMIT = TRUE|FALSE command.

When the RESOURCE_LIMIT is set to true, you can create profiles to manage resource limits.

Resource limits managed with a profile include the following:

• SESSIONS_PER_USER is the number of concurrent sessions allowed for each username.

• CPU_PER_SESSION is the total CPU time measured in hundredths of seconds per session.

• CPU_PER_CALL is the total CPU time measured in hundredths of seconds per call.

• LOGICAL_READS_PER_SESSION specifies the limitation on the number of data blocks involved in physical as well as logical reads per session. This is used to ensure that I/O intensive operations can't tie up memory and disk resources.

• PRIVATE_SGA applies only when running in a shared server architecture and can be specified in either megabytes or kilobytes.

• LOGICAL_READS_PER_CALL is the number of data blocks involved in logical as well as physical reads per call.

• IDLE_TIME is the period of inactive time measured in minutes and is measured for the server process only. It does not take into account application activity; therefore, it is not affected by long-running queries.

• CONNECT_TIME measures elapsed connected time in minutes.

• COMPOSITE_LIMIT enables you to limit the total resource cost for a session in addition to being able to create and alter profiles as a means to set limits on specific resources. Composite limits are expressed as the weighted sum, measured in service units, of a set of resources. Accurate composite limits depend on the total amount of a resource used by an average profile user. Historical information should be gathered as a means of determining the normal range for the composite usage for a typical user. CPU_PER_SESSION, LOGICAL_READS_PER_SESSION, CONNECT_TIME, and PRIVATE_SGA are all weight-able resources, and if you don't assign a value to a given resource, that resource assumes a weight of 0 and does not contribute to the composite.

Call level limits are enforced for each call made while executing a SQL statement. Whenever a call level limit is exceeded, the processing of the statement is halted, the statement is rolled back, all previous statements remain intact, and the user's session remains connected.

To create a profile that limits resources, you can issue a command similar to the following:

 CREATE PROFILE my_resource_profile LIMIT SESSIONS_PER_USER 5 CPU_PER_SESSION 1000 IDLE_TIME = 120 CONNECT_TIME = 360; 

After you set the RESOURCE COST, as in the following example, and determined the COMPOSITE_LIMIT for a profile, Oracle calculates the total resource cost by multiplying the amount of each resource used by the session by the resource's weight and sums all the products for all resources.

The following example shows how you would alter a profile's settings:

 ALTER RESOURCE COST CPU_PER_SESSION 10 LOGICAL_READS_PER_SESSION 100 CONNECT_TIME = 2; 

The weighted cost formula, then, for a given session would be

 COST = (10 * CPU_PER_SESSION) + (100 * LOGICAL_READS_PER_SESSION) + (2 * CONNECT_TIME) 

Be careful when determining composite limits and carefully consider the math. If the COMPOSITE_LIMIT is not assigned a high enough value, users with the given profile could be unable to connect because the COMPOSITE_LIMIT is lower than the COST of connecting.

The values multiplied by the weights are gleaned from the profile to which the user is assigned.

By default, the DEFAULT profile has all parameters set to UNLIMITED.

We will now look at using the Database Resource Manager to create and control resource plans and consumer groups.