Table Space Management   «Prev  Next»

Lesson 7 Use Database Resource Manager in Oracle
Objective Use Database Resource Manager to limit resource usage.

Use Database Resource Manager to limit resource usage

The previous lesson described how to design a resource management plan. This lesson shows you how to implement the plan. Implementing a resource management plan means loading information into the database using several procedures stored in the DBMS_RESOURCE_MANAGER package. This package is installed with all Oracle Enterprise Edition databases. Look at the following series of images for syntax and examples of each step. The code shown here can be executed in SQL*Plus.

Oracle SQL*Plus, displaying a successful execution of a PL/SQL procedure.
1) The image is a screenshot of Oracle SQL*Plus, displaying a successful execution of a PL/SQL procedure. 
Oracle SQL*Plus

File Edit Search Options Help
1 SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>

Relevant Features:
  1. SQL*Plus Environment: The screenshot shows the SQL*Plus interface, a tool used for executing SQL and PL/SQL commands interactively or from a script. The menu bar contains typical options like File, Edit, Search, and Help, indicating the application is likely running on a Windows platform.
  2. PL/SQL Block Execution: The text block is a PL/SQL anonymous block that executes a single procedure: `DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA`.
  3. Procedure Purpose: The `CREATE_PENDING_AREA` procedure is part of the Oracle Database Resource Manager (DBRM). This procedure initializes a pending area where changes to the resource manager configuration can be staged before being committed. The use of this procedure suggests that the user is preparing to make configuration changes related to resource management.
  4. Execution Feedback: The message "PL/SQL procedure successfully completed." indicates that the procedure executed without errors, signifying the pending area was created successfully.
  5. User Guidance: At the bottom of the screenshot, there's a text box providing guidance on the procedure's purpose: "When you start working with the Database Resource Manager, your first task is to tell the service that you are going to make a change. Do this by executing the CREATE_PENDING_AREA procedure as shown here."

This screenshot is typically used for educational or documentation purposes to illustrate the step-by-step process of working with Oracle's DBMS Resource Manager, especially during initial configuration stages.
Oracle SQL*Plus, displaying a successful execution of a PL/SQL procedure.
2) Oracle SQL*Plus, showcasing the execution of two different PL/SQL blocks related to the Oracle Database Resource Manager. Below is the transcription of the text from the screenshot: 
Oracle SQL*Plus

File Edit Search Options Help
1 SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>
1 SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PLAN (
3 plan => 'ESALES_PLAN',
4 comment => 'New plan.');
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
Here is the code for creating a new plan. In this example, the plan name is ESALES_PLAN (we are using the example from the previous lesson.)
Relevant Features:
  1. SQL*Plus Environment: Like the previous screenshot, this one is also set in the SQL*Plus interface with the usual menu options indicating a Windows environment.
  2. PL/SQL Block Execution:
    • The first block executes the `CREATE_PENDING_AREA` procedure again, which, as mentioned before, sets up a pending area for staging configuration changes.
    • The second block creates a new resource plan using `DBMS_RESOURCE_MANAGER.CREATE_PLAN`. The plan is named `ESALES_PLAN`, and it includes a comment describing the plan as "New plan." This suggests configuration for a specific purpose or application (in this case, "ESALES").
  3. Execution Feedback: Both blocks show a message indicating "PL/SQL procedure successfully completed," confirming the successful execution of the commands without errors.
  4. Contextual Information: The instructional text at the bottom provides context for the second block, noting that the code snippet is for creating a new resource plan named `ESALES_PLAN` and ties it to the previous lesson, likely from a tutorial or educational course.

This image isuseful for educational purposes or as a guide for database administrators on how to configure and manage resource plans using the Oracle Database Resource Manager. The example demonstrates how to sequentially set up a pending area and then create a resource management plan, illustrating typical steps involved in configuring database resource management settings.
Oracle SQL*Plus, displaying a successful execution of a PL/SQL procedure.
3) 
SQL> BEGIN
  2    DBMS_RESOURCE_MANAGER.CREATE_PLAN (
  3      plan    => 'ESALES_PLAN',
  4      comment => 'New plan.');
  5  END;
  6  /
PL/SQL procedure successfully completed.

SQL> BEGIN
  2    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
  3      consumer_group => 'WEBUSER',
  4      comment        => 'Web users');
  5  END;
  6  /
PL/SQL procedure successfully completed.

SQL>

Description: This script uses PL/SQL in Oracle SQL*Plus to perform the following actions:
  1. Create a Resource Plan:
    • The CREATE_PLAN procedure from DBMS_RESOURCE_MANAGER is called to create a new resource plan named 'ESALES_PLAN' with a comment 'New plan.'.
  2. Create a Consumer Group:
    • The CREATE_CONSUMER_GROUP procedure from DBMS_RESOURCE_MANAGER is used to create a consumer group named 'WEBUSER' with a comment 'Web users'.

The message "PL/SQL procedure successfully completed." confirms the successful execution of each block. The note at the bottom also mentions the creation of another group named `MANAGER` for the example plan, though it’s not shown in the script.

Oracle SQL*Plus, displaying a successful execution of a PL/SQL procedure
4) "Here is how you define a plan directive. The example shows a plan directive attached to the WEBUSER group. The directive calls for unlimited parallelism and 50 percent of CPU at level 1." 
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    consumer_group => 'MANAGER',
    comment        => 'Inhouse Managers');
END;
/

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan              => 'ESALES_PLAN',
    group_or_subplan  => 'WEBUSER',
    comment           => 'Top priority',
    cpu_p1            => 50,
    parallel_degree_limit_p1 => NULL);
END;
/


Here is another plan directive. This one is connected to the MANAGER consumer group
5) "Here is another plan directive. This one is connected to the MANAGER consumer group. The directive calls for up to four parallel sessions, 20 percent of CPU at level 1, and 25 percent CPU at level 2. Remember you can define up to eight levels of CPU allocation." 
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan              => 'ESALES_PLAN',
    group_or_subplan  => 'MANAGER',
    comment           => 'Medium priority',
    cpu_p1            => 20,
    cpu_p2            => 25,
    parallel_degree_limit_p1 => 4);
END;
/

Oracle PL/SQL 6
6) Oracle PL/SQL 6
Oracle PL/SQL 7
7) Oracle PL/SQL 7
Oracle PL/SQL 8
8) Oracle PL/SQL 8
Oracle PL/SQL 9
9) Oracle PL/SQL 9

Using these commands is easy. The most important part of using the Database Resource Manager is to define a plan or set of plans appropriate for your system. Look in the Oracle Administrator's Guide for examples of a variety of system scenarios and plans.

DB Resource Manager - Exercise

Click the Exercise link below to practice creating a plan directive, defining a consumer group, and assigning a user to the group.
The next lesson wraps up this module.
DB Resource Manager - Exercise

SEMrush Software