Oracle7 Server Concepts
User Resource Limits and Profiles
As part of a user's security domain, you can set limits on the amount of various system resources available to the user. By explicitly setting resource limits for each user, the security administrator can prevent the uncontrolled consumption of valuable system resources such as CPU time.
The resource limit feature of Oracle is very useful in large, multiuser systems. In such environments, system resources are very expensive; therefore, the excessive consumption of these resources by one or more users can detrimentally affect the other users of the database. In single user or small scale multiuser database systems, the system resource feature is not as useful because users are less likely to consume system resources with detrimental impact.
You manage resource limits with user profiles. A profile is a named set of resource limits that you can assign to a user. Each Oracle database can have an unlimited number of profiles. Additionally, Oracle provides the security administrator the option to universally enable or disable the enforcement of profile resource limits.
If you use resource limits, a slight degradation in performance occurs when users create sessions. This is because Oracle loads all resource limit data for the user when a user connects to a database.
Types of System Resources and Limits
Oracle can limit the use of several types of system resources. In general, you can control each of these resources at the session level, the call level, or both:
| Session Level | Each time a user connects to a database, a session is created. Each session consumes CPU time and memory on the computer that executes Oracle. Several resource limits for Oracle can be set at the session level. |
| If a user exceeds a session-level resource limit, Oracle terminates the current statement (rolled back), and returns a message indicating the session limit has been reached. At this point, all previous statements in the current transaction are intact, and the only operations the user can perform are COMMIT, ROLLBACK, or disconnect (in this case, the current transaction is committed); all other operations produce an error. Even after the transaction is committed or rolled back, the user can effectively accomplish no more work during the current session. |
| Call Level | Each time a SQL statement is executed, several steps are taken to process the statement. During this processing, several calls are made to the database as part of the different execution phases. To prevent any one call from excessively using the system, Oracle allows several resource limits to be set at the call level. |
| If a user exceeds a call-level resource limit, Oracle halts the processing of the statement, rolls back the statement, and returns an error. However, all previous statements of the current transaction remain intact, and the user's session remains connected. |
CPU Time
When SQL statements and other types of calls are made to Oracle, a certain amount of CPU time is necessary to process the call. Average calls require a small amount of CPU time. However, a SQL statement involving a large amount of data or a runaway query can potentially consume a large amount of CPU time, reducing CPU time available for other processing.
To prevent uncontrolled use of CPU time, you can limit the CPU time per call and the total amount of CPU time used for Oracle calls in the duration of a session. The limits are set and measured in CPU one-hundredth seconds (0.01 seconds) used by a call or a session.
Logical Reads
Input/output is one of the most expensive operations in a database system. I/O intensive statements can monopolize memory and disk usage and cause other database operations to compete for these resources.
To prevent single sources of excessive I/O, Oracle can limit the logical data block reads per call and per session. Logical data block reads include data block reads from both memory and disk. The limits are set and measured in number of block reads performed by a call or a session.
Other Resources
Oracle also provides for the limitation of several other resources at the session level:
Note: Shortly after a session is aborted because it has exceeded an idle time limit, PMON cleans up after the aborted session. Until PMON completes this process, the killed session is still counted as one of the sessions for the sessions/user resource limit.
Note: Oracle does not constantly monitor the elapsed idle time or elapsed connection time. Doing so would reduce system performance. Instead, it checks every few minutes. Therefore, a session can exceed this limit slightly (for example, by five minutes) before Oracle enforces the limit and aborts the session.
Instructions on enabling and disabling resource limits are included in the Oracle7 Server Administrator's Guide.
Profiles
A profile is a named set of specified resource limits that can be assigned to valid usernames of an Oracle database. Profiles provide for easy management of resource limits.
When to Use Profiles
You only need to create and manage user profiles if resource limits are a requirement of your database security policy. To use profiles, first categorize the related types of users in a database. Just as roles are used to manage the privileges of related users, profiles are used to manage the resource limits of related users. Determine how many profiles are needed to encompass all types of users in a database and then determine appropriate resource limits for each profile.
Determining Values for Resource Limits of a Profile
Before creating profiles and setting the resource limits associated with them, you should determine appropriate values for each resource limit. Base these values on the type of operations a typical user performs. For example, if one class of user does not normally perform a high number of logical data block reads, then the LOGICAL_READS_PER_SESSION and LOGICAL_READS_PER_CALL limits should be set conservatively.
Usually, the best way to determine the appropriate resource limit values for a given user profile is to gather historical information about each type of resource usage. For example, the database or security administrator can gather information about the limits CONNECT_TIME, LOGICAL_READS_PER_SESSION, and LOGICAL_READS_PER_CALL using the audit feature of Oracle. By using the AUDIT SESSION option, the audit trail gathers helpful information that you can used to determine appropriate values for the previously mentioned limits. You can gather statistics for other limits using the Monitor feature of Server Manager, specifically the Statistics monitor. The Monitor feature of Server Manager is described in the Oracle Server Manager User's Guide.