0498-0500

Previous Table of Contents Next

Page 498

connected to the database. The CONNECT_TIME resource, like IDLE_TIME, is set in minutes and terminates the database connection after that limit is exceeded. This resource discriminates equally against active and idle connections.

Like IDLE_TIME, the CONNECT_TIME resource terminates only the database connection and not the applications themselves . Any query running when CONNECT_TIME is exceeded returns with an error message, however.

PRIVATE_SGA_PER_SESSION

Earlier chapters in this book describe the composition of the Oracle SGA. The PRIVATE_SGA_PER_SESSION parameter limits the maximum size of the private SGA/SQL area for the user. The value of this parameter identifies, in database blocks, how large a user 's private SQL area can be. This resource limit can be of significant importance on systems on which memory is at a premium and the DBA and system administrator are working to reduce paging and swapping.

Leaving this value at UNLIMITED (usually, the default) is best unless circumstances warrant otherwise . Make sure that the private SQL area is not too small.

COMPOSITE_LIMIT

One of the most complex and advanced resource elements is COMPOSITE_LIMIT. By using COMPOSITE_LIMIT, you can set an overall resource limit that is a composite (as opposed to an explicit) resource limit. Under this configuration, resource elements are weighted based on values called resource costs. These resource costs form a cumulative cost based on all resource elements. This cost enables you to determine which resource items are more important than others when setting resource limits.

Only the following resource elements are usable when determining a resource cost:

• CPU_PER_SESSION
• LOGICAL_READS_PER_SESSION
• CONNECT_TIME
• PRIVATE_SGA

If an item has a resource cost of 0, that resource has no cost. However, assuming that a value other than 0 exists, you can set values to the resource items by using the ALTER RESOURCE COST command. The syntax for this command follows :

 alter resource cost connect_time 10; 

In this example, each connection minute costs the user 10 points against the overall composite limit. Whenever the sum of the composite limit exceeds the amount set, the database connection terminates.

Page 499

Suppose that you have the following resource costs:

PU_PER_SESSION	1
CONNECT_TIME
LOGICAL_READS_PER_SESSION	50
PRIVATE_SGA_PER_SESSION	10

Now, assume the following composite limit:

COMPOSITE_LIMIT

15,000

The user is allowed any combination of resources that does not exceed the 15,000 COMPOSITE_LIMIT set by the DBA. In this case, the user can have 15,000 CPU minutes (CPU_PER_SESSION) or 1,500 (1,500 10 = 15,000) blocks in his or her private SQL area (PRIVATE_SGA_PER_SESSION). However, the user also may have, for example, only 7,500 CPU minutes and 150 logical reads (LOGICAL_READS_PER_SESSION: 50 150 = 7,500) for a total of 15,000. The session terminates when any combination of resources triggers the limit.

Note that the CONNECT_TIME is set to 0, which does not count against the overall COMPOSITE_LIMIT.

You may have both composite and explicit limits. Take the following example:

IDLE_TIME	180
CONNECT_TIME	600
CPU_PER_CALL	750
COMPOSITE_LIMIT	10,000

In this example, the profile causes session termination if the amount of idle time exceeds 180 minutes, the total connect time for a session exceeds 600 minutes, the amount of CPU time taken to execute a single SQL statement takes more than 750 CPU minutes, or the composite resource limit exceeds 10,000. In this case, the COMPOSITE_LIMIT is used as the termination point as long as the IDLE_TIME, CONNECT_TIME, or CPU_PER_CALL values are not exceeded. If these values are exceeded, the session disconnects despite the value of the COMPOSITE_LIMIT.

Creating Profiles

After you define a profile to suit the needs of the overall database environment, you need to create the profiles. Any user (not necessarily the DBA) with adequate database privileges can create the profiles via SQL through Oracle Server*Manager or SQL*Plus.

In the following example, the DBA creates a profile named BOSS:

 % sqlplus system/manager SQL> create profile boss limit   2>   idle_time 30   3>   cpu_per_call 600   4>   logical_reads_per_session unlimited   5>   composite_limit 7500; Profile created. 

Page 500

This profile is restricted by 30 minutes of idle time, 600 minutes of CPU time per call, or an overall composite limit of 7,500. LOGICAL_READS_PER_SESSION is set to UNLIMITED. All other resource values are set to DEFAULT. Any user you associate with this profile is bound by these constraints.

Modifying Profiles

As with most SQL commands, the ALTER command provides a variation on the CREATE command with which to make changes. The profiles are no different, and you can change any resource item in a profile by using this command, as the following example shows:

 % sqlplus system/manager SQL> alter profile boss limit   2>    sessions_per_user 3   3>    composite_limit default   4>    cpu_per_call unlimited; Profile altered. 

The resource SESSIONS_PER_USER, which was previously DEFAULT, now is set to 3. Deciding to go only with implicit profiles, the DBA also sets COMPOSITE_LIMIT back to DEFAULT and gives the BOSS profile UNLIMITED CPU per call. These changes become effective for all users who are assigned the BOSS profile.

Deleting Profiles

As the roles of users evolve , you may need to remove profiles from the database. You can do this simply and effectively by issuing the Drop Profile command:

 % sqlplus system/manager SQL> drop profile boss; Profile dropped. 

At this time, the BOSS profile no longer is available for use. If the profile currently is assigned to an existing user, an error condition occurs. You can override the error by using the CASCADE option, which assigns the DEFAULT profile to this user:

 % sqlplus system/manager SQL> drop profile boss cascade; Profile dropped. 

Using the DEFAULT Profile

As discussed earlier in this chapter, the DEFAULT profile is a standard part of each database. The values of each of the resource items in DEFAULT are the values that all other profiles use by default unless another value is set for them. DEFAULT profile values are UNLIMITED unless otherwise changed.

You can modify the DEFAULT profile, just as you can any other profile, but you cannot drop or remove it. This profile must exist.

Previous Table of Contents Next