Oracle resource profile tips Oracle Tips by Burleson Consulting

 

Using Resource PROFILES

You can set up limits on the system resources used by setting up profiles with defined limits on resources.  Profiles are very useful in large, complex organizations with many users.  It allows you to regulate the amount of resources used by each database user by creating and assigning profiles to users. Using Oracle8 password attributes where added into profiles as well.

Creation of PROFILES

Profiles are a named set of resource limits.  By default, when you create a user, they are given the default profile.  The default profile provides unlimited use of all resources. 

The syntax to create a profile follows:

>---CREATE PROFILE profile LIMIT resource_parameters|password_parameters--;->
Resource_parameters (you can specify multiple paramters per command): 
 [SESSIONS_PER_USER n|UNLIMITED|DEFAULT]
 [CPU_PER_SESSION n|UNLIMITED|DEFAULT]    
 [CPU_PER_CALL n|UNLIMITED|DEFAULT]           
 [CONNECT_TIME              n|UNLIMITED|DEFAULT]
 [IDLE_TIME                 n|UNLIMITED|DEFAULT]
 [LOGICAL_READS_PER_SESSION n|UNLIMITED|DEFAULT] 
 [LOGICAL_READS_PER_CALL    n|UNLIMITED|DEFAULT]
 [COMPOSITE_LIMIT           n|UNLIMITED|DEFAULT]
 [PRIVATE_SGA               n [K|M]|UNLIMITED|DEFAULT]
Password_parameters (Oracle8 and above):
 [FAILED_LOGIN_ATTEMPTS expr|UNLIMITED|DEFAULT]
 [PASSWORD_LIFE_TIME    expr|UNLIMITED|DEFAULT]
 [PASSWORD_REUSE_TIME   expr|UNLIMITED|DEFAULT]
 [PASSWORD_REUSE_MAX    expr|UNLIMITED|DEFAULT]
 [PASSWORD_LOCK_TIME    expr|UNLIMITED|DEFAULT]
 [PASSWORD_GRACE_TIME   expr|UNLIMITED|DEFAULT]
 [PASSWORD_VERIFY_FUNCTION function_name|NULL|DEFAULT]

Restrictions on password parameters:

* Expr must resolve to either an integer value or an integer number of days

* If PASSWORD_REUSE_TIME is set to an integer value, PASSWORD_REUSE_MAX must be set to UNLIMITED.

* If PASSWORD_REUSE_MAX is set to an integer value, PASSWORD_REUSE_TIME must be set to UNLIMITED.

* If both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX are set to UNLIMITED, then Oracle uses neither of these password resources.

* If PASSWORD_REUSE_MAX is set to DEFAULT and PASSWORD_REUSE_TIME is set to UNLIMITED, then Oracle uses the PASSWORD_REUSE_MAX value defined in the DEFAULT profile.

* If PASSWORD_REUSE_TIME is set to DEFAULT and PASSWORD_REUSE_MAX is set to UNLIMITED, then Oracle uses the PASSWORD_REUSE_TIME value defined in the DEFAULT profile.

* If both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX are set to DEFAULT, then Oracle uses whichever value is defined in the DEFAULT profile.

For example:

CREATE PROFILE  enduser  LIMIT
CPU_PER_SESSION                 60000
LOGICAL_READS_PER_SESSION   1000
CONNECT_TIME              30
PRIVATE_SGA               102400
CPU_PER_CALL              UNLIMITED
COMPOSITE LIMIT           60000000
FAILED_LOGIN_ATTEMPTS       3
PASSWORD_LIFE_TIME              90
PASSWORD_REUSE_TIME             180
PASSWORD_LOCK_TIME              3
PASSWORD_GRACE_TIME             3
Verify_function_one ;

You can assign a profile to a user when you create the user or by altering the user.  The syntax to alter the profile for a user is:

ALTER USER PROFILE profile; 

For example: 

ALTER USER scott 
PROFILE appuser;

You must have the CREATE PROFILE system privilege to create a profile.  To alter a profile you must be the creator of the profile or have the ALTER PROFILE system privilege.  To assign a profile to a user, you must have the CREATE USER or ALTER USER system privilege.

Profiles and Resource Limits

The default cost assigned to a resource is unlimited.  By setting resource limits, you can prevent users from performing operations that will tie up the system and prevent other users from performing operations.  You can use resource limits for security to ensure that users log off the system and do not leave the session connected for long periods of time. You can also assign a composite cost to each profile . The system resource limits can be enforced at the session level, the call level or both.

The session level is from the time the user logs into the database until the user exits.  The call level is for each SQL command issued. Session level limits are enforced for each connection.  When a session level limit is exceeded, only the last SQL command issued is rolled back and no further work can be performed until a commit, rollback or exit is performed. Table 7.15 lists the system resources which can be regulated at the session level.

One thing to note if you use parallel query option (PQO) is that the resources are applied to each new session, not accumulated over all of the sessions that a parallel operation uses.

TABLE 7.15:  RESOURCES REGULATED AT THE SESSION LEVEL

SYSTEM RESOURCE	DEFINITION
CPU_PER_SESSION	total CPU time in hundreds of seconds
SESSIONS_PER_USER	number of concurrent sessions for a user
CONNECT_TIME	allowed connection time in minutes
IDLE_TIME	inactive time on the server in minutes
LOGICAL_READS_PER_SESSION	number of data blocks read including both physical and logical reads from memory and disk
PRIVATE_SGA	bytes of SGA used in a database with the multithreaded server (in K or M)

You can combine the CPU_PER_SESSION, LOGICAL_READS_PER_SESSION, CONNECT_TIME, and PRIVATE_SGA to create a COMPOSITE LIMIT.

Call-level limits are enforced during the execution of each SQL statement.  When a call-level limit is exceeded, the last SQL command issued is rolled back.  All the previous statements issued are still valid and the user can continue to execute other SQL statements.  The following system resources can be regulated at the call level:

* CPU_PER_CALL for the CPU time for the SQL statement
* LOGICAL_READS_PER_CALL for the number of data blocks read for the SQL statement

The assignment of a cost to a resource can be performed with the ALTER RESOURCE COST command. Resource limits that you set explicitly for a user take precedence over the resource costs in an assigned profile.  The command line syntax for this command is:

>-ALTER RESOURCE COST ---------------------------------------------------;-->
                      |-[CPU_PER_SESSION n|UNLIMITED|DEFAULT]-----------|
                      |-[CONNECT_TIME n|UNLIMITED|DEFAULT]--------------|
                      |-[LOGICAL_READS_PER_SESSION n|UNLIMITED|DEFAULT]-|
                      |-[PRIVATE_SGA  n [K|M]|UNLIMITED|DEFAULT]--------|

For example,

ALTER RESOURCE COST CONNECT_TIME 100;

Use of resource limits is set in the database initialization parameter RESOURCE_LIMIT=TRUE.  By default this parameter is set to false.  This parameter can be changed interactively with an ALTER SYSTEM command.

The DBA_PROFILES view provides information on all the profiles and the resource limits for each profile.  The RESOURCE_COST view shows the unit cost associated with each resource. Each user can find information on his resources and limits in the USER_RESOURCE_LIMITS view. 

Table 7.16 gives a description of these data dictionary views.

Table 7.16:  Data dictionary views for resources.

DBA_PROFILES

Column	Definition
Profile	the name given to the profile
Resource_name	the name of the resource assigned to the profile
Limit	the limit placed on the profile

RESOURCE_COST

Column	Definition
Resource_name	name of the resource
Unit_cost	cost assigned

USER_RESOURCE_LIMITS

Column	Definition
Resource_name	the name of the resource
Limit	the limit placed on the user

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam.  It’s only $19.95 when you buy it directly from the publisher here. 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.