BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Managing Resources with Profiles

Oracle Tips by Burleson

You can establish limits on the system resources consumed by setting up profiles with defined limits on resources. Profiles are very useful in large, complex organizations with many users. It allows you, the DBA, to regulate the amount of resources consumed by each database user by creating and assigning profiles to users. Under Oracle8, password attributes were added to the profile capability.

Creation of Profiles

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

The syntax to create a profile follows:

Password_parameters (Oracle8 and above):

Restrictions on password parameters are as follows:

* 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
PASSWORD_VERIFY_FUNCTION ;

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, but you can also assign a composite cost to each profile.. 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, so as not to leave the session connected for long periods of time.

The system resource limits can be enforced at the session level, the call level, or both. The session level is calculated from the time the user logs in to the database until the user exits. The call level applies to 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; no further work can be performed until a commit, rollback, or exit is performed. Table 9.3 lists the system resources that can be regulated at the session level.

Note: If you use parallel query option (PQO), the resources will be applied to each new session, not accumulated over all of the sessions used by a parallel operation.

Table 9.3 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 KB or MB)

Note: 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 continue to be 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:

For example:

ALTER RESOURCE COST CONNECT_TIME 100;

The 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 or her resources and limits in the USER_RESOURCE_LIMITS view.

Altering Profiles

Provided you have the CREATE PROFILE or ALTER PROFILE system privilege, you can alter any profile, including the Oracle-created DEFAULT profile. You can alter a profile to change the cost assigned to each resource. The syntax to alter a profile follows:

ALTER PROFILE profile LIMIT resource_parameters|password_parameters;

Resource_parameters:

Password_parameters (Oracle8 and above):

ALTER PROFILE enduser LIMIT
CPU_PER_SESSION 60000
LOGICAL_READS_PER_SESSION 1000
CONNECT_TIME 60
PRIVATE_SGA 102400
CPU_PER_CALL UNLIMITED
COMPOSITE LIMIT 60000000;

To disable a profile during a session, you must have the ALTER SYSTEM privilege. A limit that you set for the session overrides the previous limit set by the profile. To reset the profile to the limit originally set by the database, set the limit to DEFAULT.

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE|FALSE;

For example:

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE ;

Profiles and Passwords

The capability to control password expiry and password complexity and validity was added to Oracle8. The capability to control passwords is contained within the purview of the profile. Table 9.4 lists the password control attributes in a profile and their definitions.

Table 9.4 Password Control Attributes in a Profile

Attribute	Description
FAILED_LOGIN_ATTEMPTS	Specifies the number of failed attempts to log in to the user account before the account is locked.
PASSWORD_LIFE_TIME	Limits the number of days the same password can be used for authentication. The password expires if it is not changed within this period, and further connections are rejected.
PASSWORD_REUSE_TIME	Specifies the number of days before which a password cannot be reused. If you set PASSWORD_REUSE_TIME to an integer value, then you must set PASSWORD_REUSE_MAX to UNLIMITED.
PASSWORD_REUSE_MAX	Specifies the number of password changes required before the current password can be reused. If you set PASSWORD_REUSE_MAX to an integer value, then you must set PASSWORD_REUSE_TIME to UNLIMITED.
PASSWORD_LOCK_TIME	Specifies the number of days an account will be locked after the specified number of consecutive failed login attempts.
PASSWORD_GRACE_TIME	Specifies the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires.
PASSWORD_VERIFY_FUNCTION	Allows a PL/SQL password complexity verification script to be passed as an argument to the CREATE PROFILE statement. Oracle provides a default script, but you can create your own routine or use third-party software instead. FUNCTION is the name of the password complexity verification routine. NULL indicates that no password verification is performed.

Oracle also provides a template PL/SQL procedure for use in creating your own password complexity and verification function. The sample PL/SQL procedure is located in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql on UNIX and in x:\orant\ rdbms\admin\utlpwdmg.sql on NT. Any script you use must follow the general template shown in Listing 9.1.

LISTING 9.1 Required function input and return variables.

CREATE OR REPLACE FUNCTION <function_name>
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
BEGIN
…
RETURN <boolean value>;
END;

Other than the required input and return variables, the password verification function can be as simple or as complex as you desire.

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books