Oracle Database Resource Manager for pluggable databases

    Apr 6, 2017 5:58:00 PM by Deiby Gomez

    Introduction

    Oracle Database Resource Manager enables you to limit database resources. Oracle has been enhanced in this regard in its new versions; for example, in 12.1.0.1, Database Resource Manager supports multi-tenant architecture. One of the most common performance issues that DBAs have when they are consolidating several databases into a single database by using pluggable databases is that all the PDBs try to use as much of the resources as they can. The PDBs compete for resources. Even more critical, sessions inside PDBs that don’t have high importance can make a critical PDB slow. This can be a serious performance problem that can impact multiple users and applications. 

    Oracle Database Resource Manager lets you specify how much of the resources you want to assign for every pluggable database; you can assign more resources to those PDBs that are more important and minimal resources for those with low importance. This prevents the low-importance PDBs from impacting the high-importance PDBs.

    Within a PDB or a non-CDB also, all the sessions compete for the resources assigned to the PDB. Sessions that have low importance can be using most of the resources, impacting the more important sessions. When Database Resource Manager is not used, a single session inside a PDB might use 95% of the resources that the whole CDB has assigned. This makes all the rest of PDB severely slow.  Inside a PDB or inside a non-CDB, Database Resource Manager enables us to assign resources to Consumer Groups, which comprise a group of users. You can group users into a Consumer Group with high importance and make another Consumer Group for the users that have low importance, and assign resources to them appropriately.

     

    New Features in Oracle Database 12.2.0.1

    • SESSION_PGA_LIMIT: The maximum amount of PGA in MB that sessions in a Consumer Group can allocate before being terminated.
    • Oracle Enterprise Manager Database Express (EM Express) supports Database Resource Manager.

     

    Example:

    In this article we will see how to set up a Database Resource Manager configuration for a Container Database with two pluggable databases. The version that we will use for this example is Oracle Database 12.2.0.1 Enterprise Edition.

    • CDB Name: CDB1
    • PDB Names: NPDB1 and NPDB2

    We will assign the following resources:

     

    Assigning resources at Container Database Level

    Go to the “Home Page” of the Container Database (CDB); in this case the CDB’s name is “CDB1”. In “Administration”-->“Resource Manager” you will see the following page.

     

    Click on the option “CDB Resource Plans” and you will see the following page:

     

    Click on the bottom “Create”. We will create a new CDB Resource Plan for our two pluggable databases NPDB1 and NPDB2.

    In this page, you will have to specify the name of the CDB Resource Plan, a description and a set of resources. The resources that you can assign are: Shares, Utilization Limit %, Parallel Server Limit %. However, there are more resources that you can assign. This is a downside of Oracle Cloud Control, because it is not synchronized with the options provided by the Oracle Database version (in this case 12.2.0.1) regarding the resources. For instance, if we were using an Oracle Database 11.2.0.4 then Oracle Cloud Control should show us the resource options that 11.2.0.4 offers us, and if we were using 12.1.0.2 then it should show us the resource options that that version offers us, and so on. 

    Unfortunately, Oracle Cloud Control 13.2 (in this example) and in previous versions offers us only the basic resource options.  Of course, you can always change the SQL Statement, but that is another story.

    Click on the Button “Add/Remove” to add the pluggable databases NPDB1 and NPDB2.  You will see the following page, where you have to transfer from the left side to the right side those pluggable databases you want to assign resources to. In this example, both pluggable databases were selected. Click in the button “Assign”.

    Then you will see the two PDBs listed; then we can assign them resources. In this example we have assigned some percentages and numbers to both PDBs and we have selected the option “Activate this Plan”.

     

    If you click on the button “Show SQL” you can see the SQL Statement that will be used to create the Resource Plan and to assign the resources. Click on the button “Return”.

     

    Click on the button “OK”. This will create the Resource Plan.

     

    SQL> select plan_id, plan, comments from dba_cdb_rsrc_plans where plan='NUVOLACDBPLAN';
     
       PLAN_ID PLAN            COMMENTS
    ---------- --------------- ------------------------------
         73553 NUVOLACDBPLAN   Resource Plan for Nuvola CDB
        
    SQL> show parameters resource
     
    NAME                         TYPE   VALUE
    ------------------------------------ ----------- -----------------resource_limit                    boolean     TRUE
    resource_manage_goldengate         boolean     FALSE
    resource_manager_cpu_allocation     integer     1
    resource_manager_plan              string      NUVOLACDBPLAN
        
    SQL> select plan, pluggable_database, shares, utilization_limit, parallel_server_limit, memory_min,
    memory_limit from DBA_CDB_RSRC_PLAN_DIRECTIVES where plan='NUVOLACDBPLAN';

     
    PLAN              PLUGGABLE_DATABASE                    SHARES  UTILIZATION  PARAL  MEMORY_MIN   MEMORY_LIMIT
    ---------------   ---------------------------- ------- ------------ ------ ------------ ------------
    NUVOLACDBPLAN   ORA$DEFAULT_PDB_DIRECTIVE   1          100  100
    NUVOLACDBPLAN   ORA$AUTOTASK                            90  100
    NUVOLACDBPLAN   NPDB1                       3           30   30
    NUVOLACDBPLAN   NPDB2                       7           70   70

    As I said before, in Cloud Control 13.2 you cannot specify all the options available. There two options that are not shown for Oracle Database 12.2.1.0; they are the following:

    • memory_limit: This parameter is applicable only to Oracle Exadata storage for configuring the Database Smart Flash Cache.
    • memory_min: This parameter is applicable only to Oracle Exadata storage for configuring the Database Smart Flash Cache.

    When I was reading the documentation I thought that these parameters were to limit the usage of the SGA for the PDBs, but it seems that they work only for Exadata. Perhaps in upcoming versions? I hope!

    If you want to add a value for those resources you have to modify the CDB Plan Directive manually, as I show you below:

    SQL> exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    PL/SQL procedure successfully completed.
     
     
    SQL> BEGIN
      DBMS_RESOURCE_MANAGER.UPDATE_CDB_PLAN_DIRECTIVE(
        plan                  => 'NUVOLACDBPLAN',
        pluggable_database    => 'NPDB1',
        new_shares                => 3,
        new_utilization_limit     => 30,
        new_parallel_server_limit => 30,
        new_memory_limit=>30,
        new_memory_min=>30);
    END;
    /  2    3    4    5    6    7    8    9   10   11 
     
    PL/SQL procedure successfully completed.
     
    SQL> BEGIN
      DBMS_RESOURCE_MANAGER.UPDATE_CDB_PLAN_DIRECTIVE(
        plan                  => 'NUVOLACDBPLAN',
        pluggable_database    => 'NPDB2',
        new_shares                => 7,
        new_utilization_limit     => 70,
        new_parallel_server_limit => 70,
        new_memory_limit=>70,
        new_memory_min=>70);
    END;
    /  2    3    4    5    6    7    8    9   10   11  
     
    PL/SQL procedure successfully completed.
     
    SQL>
    SQL> exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
     
    PL/SQL procedure successfully completed.
     
    SQL>
    SQL> exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
    PL/SQL procedure successfully completed.

    Then you can query if the values were set correctly:

    SQL> select plan, pluggable_database, shares, utilization_limit, parallel_server_limit,
    memory_min, memory_limit from DBA_CDB_RSRC_PLAN_DIRECTIVES where plan='NUVOLACDBPLAN';



    PLAN          PLUGGABLE_DATABASE          SHARES  UTILIZATION_LIMIT PARALLEL_SERVER_LIMIT MEMORY_MINMEMORY_LIMIT
    --------------- ------------------------- ------- ----------------- --------------------- ---------- -----------
    NUVOLACDBPLAN   ORA$DEFAULT_PDB_DIRECTIVE    1                 100               100
    NUVOLACDBPLAN   ORA$AUTOTASK                                             90               100
    NUVOLACDBPLAN   NPDB1                        3                  30                30         30          30
    NUVOLACDBPLAN   NPDB2                        7                  70                70         70          70

    Assigning resources at pluggable database level:

    Now go back to the “Home page” of the Container Database “CDB1”.  “Administration”--> “Resource Manager”.  We will select this time the option “Consumer Groups”. The Consumer Groups receive the resources assigned by Resource Plan Directives.

     

    We will see the following page where we have to select in which pluggable database we want to create the Consumer Groups. In this example, we will select the PDB called “NPDB1”. Click on the button “Continue”.

     

    In the following page, all the Consumer Groups will be listed. There are several already created by default. We will click on the button “Create”.

     

    In the following page we have to specify the name of the Consumer Group and a description. The name of the Consumer Group will be called “nuvola_cg1”. Click on the button “Add” so what we can add Users that will inside this Consumer Group.

    The users that exist in the PBD will be listed. In this example, I have filtered the users by the string “Nuvola”. The users “NUVOLA_USER_1” and the user “NUVOLA_USER_2” will be added to this Consumer Group. Click on the button “Select”.

     

    We will repeat the last two steps to create another Consumer Group.This time it will be called “nuvola_cg2”. For this Consumer Group, the users “NUVOLA_USER_3” and “NUVOLA_USER_4” will be added.

     

    You will see the two Consumers Group created in the following page:

    Now there is only one step pending. To Create the Resource Plan Directive. A Resource Plan Directive specifies how the resources will be assigned to Consumer Groups. For this Go back to the “Home page” of the pluggable database “NPDB1”.  “Administration”--> “Resource Manager”.  We will select this time the option “Plans”. 

     

    There are some Resource Plans already created by default. We will click on the button “Create”.

     

    On this page we have to specify the name of the PDB Resource Plan and a description; in this case the plan will be called “NUVOLA_PDB1_PLAN”. Also on the second part of the page, “Resource Allocations”, we will specify the “Directive” where we specify how much of the resources will be assigned to each Consumer Group. Click on the button “Add/Remove” to add the Consumer Groups that we created previously.

     

    Select the two Consumer Groups that we created; in this example they are “NUVOLA_CG1” and “NUVOLA_CG2”. Click on the button “OK”.

    The two Consumer Group will be added and then we can assign them resources. In this section, it is important to note that only the following resources can be added:

    General Tab:

    • Shares
    • Utilization Limit %

    Parallelism Tab:

    • Bypass Queue
    • Max Degree of Parallelism
    • Parallel Server Limit
    • Parallel Statement Queue Timeout

    Runaway Query Tab:

    • Elapsed time Limit (Secs)
    • CPU Time limit (Secs)
    • IO Limit (MBs)
    • IO Request Limit (Requests)
    • Action

    Idle Time Tab:

    • Max idle time
    • Max idle time if blocking another session

    However, as in the CDB Resource Plan, there are others resources that also can be specified manually but that are not present in Cloud Control/ for instance, UNDO limit. Again, a downside of Oracle Cloud Control. If you want to see all the resources that you can specify review the documentation: http://docs.oracle.com/database/122/ARPLS/DBMS_RESOURCE_MANAGER.htm#ARPLS73823

    Click on tab “General”. Specify the resources that you want and click on the button “OK”.

     

    Click on tab “Parallelism”. Specify the resources that you want and click on the button “OK”.

     

    Click on tab “Runaway Query”. Specify the resources that you want and click on the button “OK”.

    Click on tab “Idle Time”. Specify the resources that you want and click on the button “OK”.

    We can review with SQL Statements whether the PDB Resource Plan, its Directive, and the Consumer Groups were created successfully. We will login to the PDB called “NPDB1”:

    SQL> alter session set container=npdb1;
    Session altered.

    We can review a couple of Resources using the view DBA_RSRC_PLAN_DIRECTIVES:

    SQL> select plan, group_or_subplan, max_idle_time, max_utilization_limit, parallel_queue_timeout,
    utilization_limit from dba_rsrc_plan_directives where plan like '%NUVOLA%'

     
    PLAN            GROUP_OR_SUBPLA MAX_IDLE_TIME MAX_UTILIZATION_LIMIT
    ---------------- --------------- ------------- ---------------------
    NUVOLA_PDB1_PLAN NUVOLA_CG1         500                       30   
    NUVOLA_PDB1_PLAN NUVOLA_CG2         250                       70  
    NUVOLA_PDB1_PLAN OTHER_GROUPS                                  0        

    As I said before, Oracle Cloud Control doesn’t show all the options for resources. For instance, if you want to assign Undo Limit or PGA limit for a Consumer Group then you have to modify the Plan Directive manually as I show below:

    SQL> exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

    PL/SQL procedure successfully completed.
     
    SQL> BEGIN
    DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
    plan=>'NUVOLA_PDB1_PLAN',
    group_or_subplan=>'NUVOLA_CG1',
    new_undo_pool=>100,
    new_session_pga_limit=>120);
    END;
    /  2    3    4    5    6    7    8 
     
    PL/SQL procedure successfully completed.
     
    SQL> exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
    PL/SQL procedure successfully completed.
     
    SQL> exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
    PL/SQL procedure successfully completed.
     
     
    SQL>  select plan, group_or_subplan, UNDO_POOL,max_idle_time, max_utilization_limit from dba_rsrc_plan_directives where plan like '%NUVOLA%';
     
    PLAN           GROUP_OR_SUBPLA UNDO_POOL MAX_IDLE_TIME MAX_UTILIZATION_LIMIT
    ---------------- ----------------- ---------- --------------- ---------------------
    NUVOLA_PDB1_PLAN NUVOLA_CG1              100           500                   30
    NUVOLA_PDB1_PLAN NUVOLA_CG2                            250                   70
    NUVOLA_PDB1_PLAN OTHER_GROUPS                                                 0      

     

    Conclusion

    If you are consolidating non-CDBs databases into a CDB database with several PDBs it is highly recommended that you implement Database Resource Manager. In this article I presented a step-by-step ecample that you can use as a recipe to implement a Database Resource Manager configuration and assign resources properly across PDBs and across Consumer Groups.

    Tags: Oracle

    Deiby Gomez

    Written by Deiby Gomez

    Deiby Gómez is the youngest Oracle ACE and Oracle ACE Director in the world and the first Guatemalan with these awards. Deiby is the youngest Latin American with the highest certifications “Oracle Certified Master 11g” and “Oracle Certified Master 12c”. He received In Vegas, United States the "SELECT Journal Editor’s Choice Award 2016", he became the first Guatemalan with that award. He is a frequent speaker in several Oracle Events around the world like “Technology Network Latin American Tour 2013, 2014, 2015 and 2016 in several countries like Guatemala, Costa Rica, Nicaragua, El Salvador, Uruguay, Argentina, Mexico, Brazil, Ecuador, Colombia, Peru; Collaborate in Vegas, USA; Latin American Oracle Open World in Brazil (2015 and 2016) and Oracle Open World in San Francisco, USA (2015 and 2016). He is the first Guatemalan who was accepted by Oracle Corporation as “Beta Tester” for the version “12cR2” in 2015. He is the official Technical Reviewer of the Book “Oracle Database 12c Release 2 Multitenant (1st Edition, McGraw-Hill)" and Co-Author of the book "Oracle Database 12c Release 2 Testing Tools and Techniques for Performance and Scalability", both can be found in Amazon. He is the first Guatemala who appeared as an outstanding expert in the official magazine of Oracle Corporation called “Oracle Magazine” in the Edition November/December in 2014, this magazine is delivered around the world in several countries. He has published several articles in the Official Website of Oracle (www.oracle.com) in Portuguese, Spanish and English. Currently he is President of Guatemalan Oracle Users Group (GOUG). Director of Support Quality en Latin American Oracle Users Group Community (LAOUC) for 2016-2017, founder of a very well known group in Oracle Community called “Oraworld Team” that has members from India, Guatemala, Brazil, France and Switzerland having in total 4 Oracle ACE Directors, 3 Oracle ACE, 5 Oracle Certified Masters (OCM) and 1 PhD. Deiby was part of Oracle ACE Hackaton in Amsterdam, Netherlands in April 2016 where he was building several solutions using Oracle Cloud Products. Currently Deiby Gómez is CEO in Nuvola Consulting Group, a company that provides excellence on Support and Consulting services with Oracle Technology like Databases, Middleware, Cloud and Engineered systems. Deiby is well known in the community because of his resilience, entrepreneurship and his availability to help and share his knowledge. Deiby loves to travel, to play chess and to enjoy a good cup of coffee with friends. Oracle ACE (at the age of 23) Oracle Certified Master 11g (at the age of 24) Oracle ACE Director (at the age of 25) Oracle Certified Master 12c (at the age 26) SELECT Journal Editor’s Choice Award 2016 Speaker in several Universities, OTN Tour 2013,2014,2015. Collaborate15, LA Oracle Open World. Technical Reviewer of the Book "Oracle Database 12c Release 2 Multitenant (Oracle Press) 1st Edition" Blogger. Oracle Certifications: Oracle Linux Certified Implementation Specialist. Oracle Database 11g Administrator Certified Professional. Oracle Database 11g Administrator Certified Master (OCM 11g) Oracle Database 12c Administrator Certified Master (OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Master (MAA OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Expert (MAA OCE 12c) Oracle Database 12c Administrator Certified Professional. Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert. Oracle Exadata Database Machine Administrator. Oracle RAC 11g and Grid Infraestructure Administrator Oracle RAC 12c and Grid Infraestructure Administrator Oracle Real Application Clusters 12c Certified Implementation Specialist Oracle Database 12c: Data Guard Administrator