How to efficiently allocate your Oracle database resources

What is the limit on the amount of resources a user can use? 

What if a user unwittingly starts a SQL program that guzzles resources like crazy and brings your system to its knees? 

How can ensure that the databases is not loaded down by inefficient queries.

Can you limit an individual's usage of resources, so you can allocate resources on a need-to-use basis? 

You can set the individual resource limits in Oracle by using what are known as profiles. 

You can use profiles to set hard limits on resource consumption by the various users in the database. Profiles help you limit the number of sessions a user can simultaneously keep open, the length of time these sessions can be maintained, and the usage of CPU and other resources. 

Here, for example, is a profile called "reporting" 

SQL> create profile reporting
  2  limit
  3  connect_time 120
  4  failed_login_attempts 2
  5  idle_time 60
  6* sessions_per_user 2;
Profile created.
SQL>

The reporting profile when granted to a user will permit that user to be connected for a maximum of 120 seconds and will log out the user if he or she is idle for more than 1 minute. The user is limited to two sessions at any one time. If the user fails to log in within two attempts, the user's accounts will be "locked" for a specified period or until the DBA manually unlocks them.

Oracle9i enables you to set limits on several parameters within a profile. The following sections provide brief explanations of these parameters. You can divide the profile parameters into two broad types: resource parameters, which are concerned purely with limiting resource usage, and password parameters, which are used for enforcing password-related security policies.

Resource parameters are profile parameters that you can set to control resource usage by users. The main purpose in using resource parameters is to ensure that a single user or a set of users doesn't monopolize the database and server resources. Here are the most important resource parameters that you can set within an Oracle9i database:

• Connect_time: The total time a session may remain connected to the database.
• Cpu_per_call: Limits the CPU used per each call within a transaction (for the parse, execute, and fetch operations).
• Cpu_per_session: Limits the total CPU used during a session.
• Sessions_per_user: Maximum number of concurrent sessions that can be opened by the user.
• Idle_time: Limits the amount of time a session is idle (i.e., nothing is running on its behalf).
• Logical_reads_per_session: Total number of data blocks read (memory plus disk reads).
• Logical_reads_per_call: Limits the logical reads per each session call (parse, execute, and fetch).
• Private_sga: This is a limit applicable only to shared server architecture-based systems. It specifies a session's limits on the space it allocated in the shared pool component of the SGA.
• Composite_limit: A composite limit is a sum of several of the previously described resource parameters, measured in service units. These resources are weighted by their importance. Oracle takes into account four parameters to compute a weighted composite_limit: cpu_per_session, connect_time, logical_reads_per_session, and private_sga. You can set a weight for each of these four parameters by using the alter resource cost statement, as shown in the following example:
  
  

  SQL> alter resource cost
    2  cpu_per_session 200
    3  connect_time 2;
  Resource cost altered.
  SQL>  

When Do Profile Changes Go into Effect?

After you create a profile, when does it come into force? That is, when will the user be really restricted to four simultaneous sessions? The surprising answer is . . . never! Unless you have an initialization parameter modified from its default value, the profile changes you make will never come into force. The initialization parameter is the resource_limit parameter, and its default value is false. You need to set it to true, either by restarting the database after an init.ora file change or through the use of the alter system command, as shown here:

SQL> alter system set resource_limit=true;
System altered.
SQL>

Make sure you have the resource_limit parameter set to true in order for the resource limits set by the profiles to be enforced. Otherwise, Oracle will ignore the limits set in the create or alter profile statement

Assign a profile

You assign a profile to a user by using the alter user statement, as follows

SQL> alter user gm_fomi
  2  profile test;
User altered.
SQL> 

Using the Database Resource Manager

The Database Resource Manager allows you to create resource plans, which specify how much of your resources should go to the various consumer groups. You can now group users based on their resource requirements, and you can have the Database Resource Manager allocate a preset amount of resources to these groups. Thus, you can easily prioritize among your users and jobs.

The resource plans that you formulate have the directives regarding resource usage, and you can easily modify these plans. Using the Database Resource Manager, it's possible for you to ensure that your critical user groups (called consumer groups here) are always guaranteed enough resources to perform their tasks. The resources that the Database Resource Manager can allocate are CPU usage, degree of parallelism, execution time limit, and the undo that can be generated by a consumer group. You can also limit the maximum number of concurrently active sessions allowed in each group.

Here's the sequence of actions you need to take to start using the Database Resource Manager:

Create a pending area

Before you can modify an old plan or create a new plan, you need to activate or create a pending area using the Database Resource Manager package in the following manner. All the resource plans you'll create will be stored in the data dictionary, and the following create pending area procedure will enable you to work with resource plans in a staging area before they are implemented:

SQL> execute dbms_resource_manager.create_pending_area;
PL/SQL procedure successfully completed.
SQL>

You can also clear the pending area anytime you want by using the following procedure:

SQL> execute dbms_resource_manager.clear_pending_area;
PL/SQL procedure successfully completed.
SQL>

Creating Consumer Groups

Once the pending area is active, you can create the consumer groups to which you'll allocate your users. You can assign users initially to one group, and you can later switch them to other groups if necessary. Now you'll create in your database three consumer groups : agency, direction, and national.

SQL> execute dbms_resource_manager.create_consumer_group (consumer_group => 'agency',comment => 'remote agencies'); 

PL/SQL procedure successfully completed. 

SQL> execute dbms_resource_manager.create_consumer_group (consumer_group => 'direction',-> comment => 'direction'); 

PL/SQL procedure successfully completed. 

SQL> execute dbms_resource_manager. create_consumer_group (consumer_group => 'national',-> comment => 'national office'); 

PL/SQL procedure successfully completed. SQL> 

Assigning Users to Consumer Groups

Users are already members of a default group, the default_consumer_group. Therefore, you need to first grant the three users privileges to switch their groups before you can actually switch them to your new groups. If you just grant the user PUBLIC the privilege to switch groups, you don't have to grant the privilege individually to all the users in the group. If you have a large number of users in each group, it is better to grant the user PUBLIC the privilege to switch groups, so you can avoid granting the privilege individually to each user.

SQL> execute dbms_resource_manager_privs.grant_switch_consumer_group('gm_fomi','low_group',TRUE);

PL/SQL procedure successfully completed. 

SQL> execute dbms_resource_manager.set_initial_consumer_group ('gm_fomi','low_group'); 

PL/SQL procedure successfully completed. 

Verifying Consumer Group Membership of Users

SQL> select username,initial_rsrc_consumer_group from dba_users;

USERNAME                  INITIAL_RSRC_CONSUMER_GROUP
------------------                  -------------------------------------------------------
SYS                                  SYS_GROUP
SYSTEM                         SYS_GROUP
DBSNMP                        DEFAULT_CONSUMER_GROUP
SIGNAT                          DEFAULT_CONSUMER_GROUP
PERFSTAT                    DEFAULT_CONSUMER_GROUP
OUTLN                           DEFAULT_CONSUMER_GROUP
WMSYS                           DEFAULT_CONSUMER_GROUP
GM_FOMI                      LOW_GROUP

Creating Resource Plans and Plan Directives

The heart of the Database Resource Manager is its capability to assign resource plans to various groups.  

Resource plans enable you to set limits on resource use by specifying limits on four variables: CPU, active session pool, degree of parallelism, and the order in which queued sessions will execute. Currently, for all four parameters, only the default levels and methods provided by Oracle can be used. 

Creating Resource Plans

Create your resource plan by invoking the DBMS_RESOURCE_MANAGER package :

SQL> execute dbms_resource_manager.create_pending_area;
PL/SQL procedure successfully completed.
SQL>  execute dbms_resource_Manager.create_plan (plan => 'membership_plan',comment => 'New Membership Recruitment');
PL/SQL procedure successfully completed. 

Creating a Plan Directive

You now have a resource plan, but the plan still doesn't have any resource limits assigned to it. You need to create a resource plan directive to assign specific resource limits to your resource plan.

SQL> execute dbms_resource_manager.create_plan_directive 

(plan => 'test_plan',GROUP_OR_SUBPLAN  => 'low_group', 

COMMENT => 'test_plan_directive',CPU_P1 => 70); 

PL/SQL procedure successfully completed.

SQL> execute dbms_resource_manager.create_plan_directive 

(plan => 'test_plan',GROUP_OR_SUBPLAN  => 'test_group', 

COMMENT => 'test_plan_directive',CPU_P1 => 30); 
PL/SQL procedure successfully completed. 

This plan directive assigns 70 percent of the available CPU at the first level to the low_group and the rest, 30 percent, to the test_group

In addition to the preceding groups, you'll need to add a plan directive for the default other_groups for the Database Resource Manager to accept your plan directives.

SQL> execute dbms_resource_manager.create_plan_directive (plan => 'test_plan',GROUP_OR_SUBPLAN  => 'OTHER_GROUPS', comment => '',cpu_p1 => 0);
PL/SQL procedure successfully completed.
SQL>

Tip

If you don't include a resource directive for other_groups, Oracle won't let you use your directives for the other groups if the plan directive is for a primary or top plan.

You can now validate and submit your new top-level plan, membership_plan, in the following manner:

SQL>  execute dbms_resource_manager.validate_pending_area;
PL/SQL procedure successfully completed.
SQL> execute dbms_resource_manager.submit_pending_area;
PL/SQL procedure successfully completed.

Determining the Status of the Resource Plans

SQL> select plan,group_or_subplan,cpu_p1,cpu_p2,cpu_p3, status from dba_rsrc_plan_directives;

PLAN                      GROUP_OR_SUBPLA     CPU_P1     CPU_P2     CPU_P3 STATUS
------------------------- --------------- ---------- ---------- ---------- ----------
SYSTEM_PLAN               SYS_GROUP              100          0          0 PENDING
SYSTEM_PLAN               OTHER_GROUPS             0        100          0 PENDING
SYSTEM_PLAN               LOW_GROUP                0          0        100 PENDING
INTERNAL_QUIESCE          SYS_GROUP                0          0          0 PENDING
INTERNAL_QUIESCE          OTHER_GROUPS             0          0          0 PENDING
INTERNAL_PLAN             OTHER_GROUPS             0          0          0 PENDING
SYSTEM_PLAN               SYS_GROUP              100          0          0 ACTIVE
SYSTEM_PLAN               OTHER_GROUPS             0        100          0 ACTIVE
SYSTEM_PLAN               LOW_GROUP                0          0        100 ACTIVE
INTERNAL_QUIESCE          SYS_GROUP                0          0          0 ACTIVE
INTERNAL_QUIESCE          OTHER_GROUPS             0          0          0 ACTIVE
INTERNAL_PLAN             OTHER_GROUPS             0          0          0 ACTIVE

Enabling the Database Resource Manager

The fact that you created a new plan and plan directives and submitted your pending area doesn't mean that Oracle will automatically enforce the resource plans. It's our job to explicitly activate the Database Resource Manager, either by specifying the initialization parameter resource_manager_plan in the init.ora file or by using the alter system command in the following manner:

SQL> alter system set resource_manager_plan=MEMBERSHIP_PLAN ;
System altered. 

SQL> select * from v$rsrc_plan;

NAME
--------------------------------
MEMBERSHIP_PLAN
SQL>

If you decide to deactivate the Database Resource Manager, you use the following command:

SQL> alter system set resource_manager_plan='';
System altered. 

SQL>  select * from v$rsrc_plan;
no rows selected