0495-0497

Previous Table of Contents Next

Page 495

Using Profiles

As mentioned earlier in this chapter, profiles control the amount of resources a user can have. A list of profile resources follows :

• SESSIONS_PER_USER
• CPU_PER_SESSION
• CPU_PER_CALL
• LOGICAL_READS_PER_SESSION
• LOGICAL_READS_PER_CALL
• IDLE_TIME
• CONNECT_TIME
• PRIVATE_SGA_PER_SESSION
• COMPOSITE_LIMIT

It is important to note that you don't need to specify every profile resource in every profile. Any profile resource you do not specifically set has the value of DEFAULT, which corresponds to the value of the DEFAULT profile.

The database does not enforce the values of a profile unless you set the parameter RESOURCE_LIMIT in the INIT.ORA parameter file. This value is FALSE by default, meaning that no profiles are enforced; you should set the value to TRUE if you want profiles. If a database cannot be restarted (by using shutdown and startup), and profiles are needed, issue the following SQL command from Oracle Server*Manager or SQL*Plus:

 alter system set resource_limit = true; 

Defining Profiles

As with creating users, defining a profile is more complex than just issuing a SQL command to create it. Each individual profile is a combination of one or more resources the database is instructed to manage. Many of these resources contain the value DEFAULT, which can change depending on the value of the DEFAULT profile, or UNLIMITED, which places no upper limits on the resource.

Just as important as knowing how to create the profile, however, is knowing what to create. A cost accountant and a comptroller, for example, may both work out of the accounting group . The comptroller, however, may work an additional 3 to 4 hours a night above what the cost accountant works. Therefore, placing a limit of 8 hours on the total connect time is not sufficient for the comptroller. In this case, you need to make the overall connect time longer (12 hours) or give the associates separate profiles. You need to understand the ramifications of profiles and how they will affect the jobs of each user class before you implement them.

Page 496

The following sections briefly describe the various profile resources for which you can set limits.

SESSIONS_PER_USER

The setting for SESSIONS_PER_USER determines the maximum number of simultaneous sessions (connections to the database) a user can have. If a user reaches the limit set in the SESSIONS_PER_USER resource of his or her profile, the next logon (and any subsequent logons ) produces an error condition.

If a user with a SESSIONS_PER_USER of 2 pulls up an application menu via SQL*Forms and is running a report via Oracle*Reports, for example, the user has reached his or her limit. If that user attempts to create another session via SQL*Plus (or any other application), the database denies the connection until one of the other connections terminates.

WARNING

Be careful when dealing with Oracle development tools (such as Oracle*Forms). In many cases, if a tool makes a call to another tool, another connection is established. An Oracle*Forms application that calls another Oracle*Forms application that calls an Oracle*Reports report, for example, is a total of three connections (one for each tool) instead of a single connection.

CPU_PER_SESSION

Each query a user issues consumes an amount of CPU time, which varies depending on the query. By setting this resource item, you limit the amount of CPU time a user can consume from a single database session. After reaching the CPU limit, the user can perform no further activity in that session. The user must disconnect from the database and then reconnect to reset this CPU accumulator .

The value of this parameter represents the total amount of CPU time (in minutes) a user can consume during a single database connection.

CPU_PER_CALL

This resource, like CPU_PER_SESSION, represents the total amount of CPU time (in minutes) available to the user. However, this resource restricts the user on a per-call (SQL statement) basis rather than a per-session basis. Whenever a SQL statement reaches its limit, it ends with an error condition. Unlike CPU_PER_SESSION, however, the user has no need to disconnect from the database. When using CPU_PER_CALL, the user is free to issue another query as long as it does not exceed the total amount of time specified in CPU_PER_CALL.

LOGICAL_READS_PER_SESSION

Like CPU_PER_SESSION, this resource element is responsible for determining how much activity can take place during a given database session. In this case, the value is the total number of logical reads (in database blocks) that can be performed in a given session. If

Page 497

LOGICAL_READS_PER_SESSION is exceeded, the user still can continue to function as long as he or she does not perform actions that cause reads from the database to be performed (such as a query).

To reset, the user must disconnect from the database and/or establish a new database connection.

LOGICAL_READS_PER_CALL

What CPU_PER_SESSION is to LOGICAL_READS_PER_SESSION, CPU_PER_CALL is to LOGICAL_READS_PER_CALL. The value of this parameter restricts the number of database blocks that can be read during a single CPU call (SQL statement). If the number of blocks the database attempts to logically read exceeds the limit set, the operation is abandoned . The user may issue another SQL statement and have no problems unless the logical reads in this statement exceed the value.

IDLE_TIME

Many UNIX systems have a so-called idle daemon, which terminates user processes that exceed a certain amount of inactive time. The IDLE_TIME resource is an attempt by Oracle to implement such a technology at the database level.

In essence, a system (in this case) database is considered idle when it has had no activity within a certain period of time. This activity may consist of a user typing information at the keyboard or running a query. By using the IDLE_TIME resource, you can designate how much time (in minutes) a user may allow a database connection to sit idle before terminating the connection.

NOTE

A database that is processing a long-running query is not considered idle.

A terminated connection to the database might not be readily obvious to the user because the resource does not terminate any applications. The user might not realize that the database connection has been terminated until the next time he or she attempts to perform an operation (such as a query).

Suppose that a user has been sitting idle in SQL*Plus for two hours. Because an IDLE_TIME value of 60 was selected (one hour), the user's connection to the database is broken after the first hour. However, the user still sees SQL*Plus, and not until another hour passes , when he or she attempts to issue a query, does an error message inform the user of the terminated connection.

CONNECT_TIME

Limiting the amount of time for which a user can be connected to the database sometimes can be advantageous. Unlike idle time, which measures how much time a user spends performing no actions, the CONNECT_TIME resource is compared against the total amount of time the user is

Previous Table of Contents Next