7 Replies Latest reply: Feb 18, 2013 2:19 PM by ji li RSS

    Resource Mangement question(s)

    ji li
      Hello,

      I'm running Oracle 11.2.0.2 (on Solaris 10).

      I'm reading an Oracle whitepaper on Resource Management and hoping someone has experience with this subject.

      Re: http://www.oracle.com/technetwork/database/performance/resource-manager-twp-133705.pdf

      I have gone through the steps in this paper in attempt to set two different users (actually these are main user accounts that are shared) to two separate consumer groups, but I'm a little lost how to assign them to those groups.

      So, here are the steps I've done:
      =======================
      SQL> alter system set resource_limit=true scope=both;
      
      System altered.
      
      SQL> alter system set resource_manager_plan = 'DEFAULT_PLAN' sid='*';
      
      System altered.
      
      SQL> exec dbms_resource_manager.create_pending_area();
      
      PL/SQL procedure successfully completed.
      
      
      SQL> BEGIN dbms_resource_manager.set_consumer_group_mapping(attribute => dbms_resource_manager.service_name,value => 'REMEDYT',consumer_group =>'INTERACTIVE_GROUP'); END;
      
      PL/SQL procedure successfully completed.
      
      ----
      
      SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'ARADMIN',consumer_group => 'INTERACTIVE_GROUP',grant_option => FALSE);
      
      PL/SQL procedure successfully completed.
      
      SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'ARRDONLY',consumer_group => 'DSS_GROUP',grant_option => FALSE);
      
      PL/SQL procedure successfully completed.
      
      ----
      
      SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'public',consumer_group => 'DSS_GROUP',grant_option => FALSE);
      
      PL/SQL procedure successfully completed.
      So basically I'm allowing the main application which uses schema ARADMIN to use the "INTERACTIVE_GROUP", and the schema ARRDONLY (or any other user) to use the "DSS_GROUP".
      But, this seems to only "allow" these users to switch to these groups, rather than assign them to these groups.
      Also, how do I see (or adjust) what allocation of resources are given to these groups respectively by default.
      These are all Oracle default groups.

      I'm a little lost on this one whereas in the past I've just considered to use profiles for managing resources, but this is different, (and perhaps better for what I'm wanting to accomplish). What I'm wanting to accomplish, is exactly as noted in this whitepaper:
      You can also use Resource Manager to explicitly allocate CPU resources to the multiple
      workloads or applications that are sharing the database. For example, you can prioritize
      “sys” over regular users and OLTP transactions over reports and batch jobs
        • 1. Re: Resource Mangement question(s)
          P.Forstmann
          ji li wrote:
          But, this seems to only "allow" these users to switch to these groups, rather than assign them to these groups.
          In your case DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING will assign user session to the right group based on the service name used by the client to establish a connection because you have used :
          dbms_resource_manager.service_name
          Also, how do I see (or adjust) what allocation of resources are given to these groups respectively by default.
          You need either to create a simple plan using CREATE_SIMPLE_PLAN http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm004.htm
          or a complex plan using differents steps described in http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm005.htm.

          You can query following dictionary views for resource manager allocated resources:

          DBA_RSRC_CONSUMER_GROUPS
          DBA_RSRC_IO_CALIBRATE
          DBA_RSRC_PLAN_DIRECTIVES


          Edited by: P. Forstmann on 15 févr. 2013 21:20
          • 2. Re: Resource Mangement question(s)
            JohnWatson
            Are you looking for the dbms_resource_manager.set_initial_consumer_group procedure? You can see a schema's default group in dba_users.initial_rsrc_consumer_group.
            The clever part of the Resource Manager is dynamic switching of sessions from one group to another, after logon depending on what they are doing.
            --
            John Watson
            Oracle Certified Master DBA
            http://skillbuilders.com
            • 3. Re: Resource Mangement question(s)
              ji li
              Hi P. Fortsman...

              I'm starting to understand this a little better, but I'm still confused.

              So, if users are assigned to a resource plan based on the service they connect to, then I'm assuming I would need to create multiple services.
              But if I did that, what would prevent someone from just using a different service (with higher resource privileges) to connect to the database?

              Is there another way to assign resource plans to a user that I could control better?

              Thanks.

              (I'm beginning to think I need a whole course to understand Oracle's Resource Management.
              It sure seems a lot easier just to use profiles.)
              • 4. Re: Resource Mangement question(s)
                ji li
                Oh... that looks interesting.

                So, does that mean I can do an alter user <username> INITIAL_RSRC_CONSUMER_GROUP <some_resource_group>?

                So, how can I do this on a large scale without doing it at the user level?

                But this is at least somewhat promising.
                • 5. Re: Resource Mangement question(s)
                  P.Forstmann
                  ji li wrote:
                  So, if users are assigned to a resource plan based on the service they connect to, then I'm assuming I would need to create multiple services.
                  But if I did that, what would prevent someone from just using a different service (with higher resource privileges) to connect to the database?
                  Service information is not public by default: this depends on the privileges you give to query DBA_SERVICES or related dynamic performance views. It also depends on the way you manage Oracle Net client configuration files/ Oracle connect strings and make this public or not.
                  Is there another way to assign resource plans to a user that I could control better?
                  You could map session to a consumer group based on OS client user, client program, client machine for example http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_resmgr.htm#i1004827.
                  • 6. Re: Resource Mangement question(s)
                    P.Forstmann
                    ji li wrote:
                    So, does that mean I can do an alter user <username> INITIAL_RSRC_CONSUMER_GROUP <some_resource_group>?
                    No.
                    So, how can I do this on a large scale without doing it at the user level?
                    You could write PL/SQL code similar to:
                    for cu in (select username from dba_users where ... )
                    loop
                     dbms_resource_manager.set_initial_consumer_group(cu.username, <initial consumer group>);
                    end loop;
                    But note that:
                    >
                    This procedure is deprecated in Release 11gR1. While the procedure remains available in the package, Initial Consumer Group is set by the session-to-consumer group mapping rules.
                    • 7. Re: Resource Mangement question(s)
                      ji li
                      Hmmm... okay. Thank you.
                      Sounds like Oracle still has some work to do in this area.
                      Thank you very much for your help.