13 Replies Latest reply: Sep 12, 2013 9:49 AM by 888157 RSS

    resource manager cpu limit problem

    888157

      Hi everyone,

       

      I am trying to test resource manager, resource limit. in all documents I've read, after creating consumer groups, if I gave a limit for resource in resource plan, it says, that consumer group won't be able to consume more cpu than that. for ex, 2 consumer group I gave one of them 90% of cpu and to the other (let's call it test_group) 5% but when I connect with a user which is in test_group consumer group. after running some sql/plsql I saw that it is using all cpu.

       

      EDIT: at fifth message there is complete sample code and test results with screenshots.

       

      for ex in this page : http://pic.dhe.ibm.com/infocenter/mdm/v10r0m0/index.jsp?topic=%2Fcom.ibm.mdmhs.smpl.gd.doc%2Fr_oracle_db_resource_manager_working_example.html

       

      there is a sample code which explain the plan. it says REPORTING_GROUP is not be able to occupy more than of 5% of the CPU time in total. I used that code but reporting_group still be able to consume all cpu. I tested this code (also I've created a new group and plan via enterprise manager) on windows 7 64bit, intel i7 cpu with 4 core and on same machine, oralinux using virtual machine. as database, enterprise edition 11.2.0.1

       

      also resource plan is active and working, active session limit or undo pool size for example, is working when I tested but CPU limits is not. to test I used this plsql code.

       

      create or replace function sf_test(p number) return number as
        x number;
      begin
        select 1 into x from sh.sales where prod_id = p and rownum = 1;
        return 1;
      exception
        when no_data_found then
          return 0;
      end;
      /
      
      
      declare
        x number;
      begin
        for i in 1..50 loop
          for i in 1..10000 loop
            x := sf_Test(i);
          end loop;
        end loop;
      end;
      
      

       

      I created four session from report1 user (in the link I mentioned before) and run that plsql code, at most 5% of cpu should they got but my cpu is almost overloaded every time.

       

      do I missed something here or I got the concept all wrong ?

       

      ps: resource_limit parameter is true and resouce_plan is the plan that I've created. as I said before plan is active and working, I know that but can not limit the cpu. I would be appreciate if you help. thanks a lot.

        • 1. Re: resource manager cpu limit problem
          JustinCave

          Is your system actually at 100% CPU utilization?  Is there a session in the 90% resource group that is trying to get CPU? 

           

          Resource Manager only kicks in when the system is fully utilized.  And it only throttles a session if there is another higher priority session that wants that resource.  My guess from your description is that you're expecting that test_group would only be able to use 5% of the CPU even if nothing else was running, leaving 95% of the CPU idle.  That is not how Resource Manager works.  If you have spare CPU, Resource Manager will let the lower priority process use it unless there is a higher priority process waiting.

           

          Justin

          • 2. Re: resource manager cpu limit problem
            888157

            Hi justin,

             

            Thanks for reply, I also think about that and so I connect to the system with 6 different session, 4 of them "test_group" (5% of cpu) and 2 of them another users (which is in OTHER_GROUPS automatically ) and run the code above 3 test_group user was the most consumer of the cpu and then 1 other user, 1 test_gorup and 1 other user again. I confirm the usage from using EM and acording to EM again, 4 test_group user was consuming more then 70% of cpu...

             

            I can put a screen shot for EM if necessary.

            • 3. Re: resource manager cpu limit problem
              JustinCave

              I'm afraid I'm not quite following exactly what you are doing and what you are seeing.

               

              When you ran your test, was your CPU actually 100% utilized?  Or was it 70% utilized?

               

              Justin

              • 4. Re: resource manager cpu limit problem
                888157

                Hi Justin,

                 

                you are right, I couldn't explain it well.

                 

                I created a test case with screen shots. I wrote a plsql code base on the on in the link above.

                 

                there are 2 users "REPORT1" and "MANAGER", 1 consumer group "REPORTING_GROUP", an active plan DAYTIME.

                 

                REPORT1 user is in REPORTING_GROUP consumer group. MANAGER is not member of any consumer group so it is DEFAULT_CONSUMER_GROUP which can be represent as OTHER_GROUPS in plan.

                 

                in DAYTIME plan, %5percent of resource belongs to REPORTING_GROUP ("report1" user) and 95% belongs to OTHER_GROUPS ("manager" user).

                 

                I've created 6 different session, 4 of them belongs to REPORT1 (5% of resource) and 2 of them belongs to MANAGER(95% of resource) and run same plsql code from all of them.

                 

                here is starter script:

                /**********USERS*************/
                DROP USER REPORT1 CASCADE;
                CREATE USER REPORT1 IDENTIFIED BY REPORT1 ;
                GRANT CONNECT TO REPORT1;
                GRANT RESOURCE TO REPORT1;
                
                
                DROP USER MANAGER CASCADE;
                CREATE USER MANAGER IDENTIFIED BY MANAGER ;
                GRANT CONNECT TO MANAGER;
                
                
                
                /*************RESOURCE PLAN & CONSUMER GROUP*******************/
                
                
                ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';
                ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
                
                
                BEGIN
                  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
                
                
                  DBMS_RESOURCE_MANAGER.DELETE_PLAN( 'DAYTIME' );
                
                
                  DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP ( 'REPORTING_GROUP' );
                
                
                  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
                
                
                  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
                
                
                  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
                
                
                EXCEPTION
                  WHEN OTHERS THEN
                    NULL; /* Ignore any exception */
                    DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
                END;
                /
                
                
                
                
                -- Create the resource plan and consumer groups
                BEGIN
                  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
                
                
                  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
                    CONSUMER_GROUP => 'REPORTING_GROUP',
                    COMMENT        => 'Reporting Group');
                
                
                  DBMS_RESOURCE_MANAGER.CREATE_PLAN(
                    PLAN    => 'DAYTIME',
                    COMMENT => 'Resource Management for REPORTING_GROUP applications');
                
                
                  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
                    PLAN                    => 'DAYTIME',
                    GROUP_OR_SUBPLAN        => 'REPORTING_GROUP',
                    COMMENT                  => 'Reporting Group',
                    ACTIVE_SESS_POOL_P1      => 5,
                    MGMT_P1                  => 5 );
                
                
                  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
                    PLAN                    => 'DAYTIME',
                    GROUP_OR_SUBPLAN        => 'OTHER_GROUPS', /* built-in group */
                    COMMENT                  => 'Others',
                    MGMT_P1                  => 95 );
                
                
                  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
                
                
                  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
                
                
                  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
                
                
                END;
                /
                
                
                
                
                
                
                
                
                
                
                
                
                
                -- Add the users to the consumer groups
                BEGIN
                
                
                  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
                
                
                  /* This GRANT is needed so that REPORTING_GROUP can be used as the initial consumer group for REPORT1 */
                  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
                    GRANTEE_NAME  => 'PUBLIC',
                    CONSUMER_GROUP => 'REPORTING_GROUP',
                    GRANT_OPTION  => FALSE );
                
                
                  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
                    (DBMS_RESOURCE_MANAGER.ORACLE_USER, 'REPORT1', 'REPORTING_GROUP');
                
                
                  dbms_resource_manager_prIvs.grant_switch_consumer_group('REPORT1', 'REPORTING_GROUP', FALSE);
                
                
                  DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('REPORT1', 'REPORTING_GROUP');
                
                
                  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
                
                
                  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
                
                
                  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
                
                
                END;
                /
                
                
                -- Enable the resource plan
                alter system set RESOURCE_MANAGER_PLAN = 'DAYTIME';
                
                
                DROP TABLE REPORT1.TEST_TABLE ;
                
                
                create table REPORT1.TEST_TABLE as select * from DBA_OBJECTS;
                
                
                drop function REPORT1.SF_TEST;
                
                
                create or replace function REPORT1.sf_test(p number) return number as
                  x number;
                begin
                  select 1 into x from REPORT1.TEST_TABLE where OBJECT_ID = p and rownum = 1;
                  return 1;
                exception
                  when no_data_found then
                    return 0;
                end;
                /
                
                
                DROP PUBLIC SYNONYM SF_TEST ;
                CREATE PUBLIC SYNONYM SF_TEST FOR REPORT1.SF_TEST;
                
                
                GRANT EXECUTE ON REPORT1.SF_TEST TO PUBLIC;
                
                
                
                
                
                
                
                
                
                
                /****** the code will be run 
                
                
                declare 
                  x number;
                begin
                  for i in 1..30 loop
                    for i in 1..10000 loop
                      x := sf_Test(i);
                    end LOOP;
                  end loop;
                end;
                /
                
                
                
                
                
                
                ********/
                
                
                

                 

                here is the screen shots...

                 

                this is plan:

                View image: plan

                 

                this is EM before run the code:

                View image: performans before run

                 

                this is execution:

                View image: execute plsql

                 

                this is EM running scripts (cpu is overload):

                View image: performans on test

                 

                and finally, this after the complete of plsql:

                View image: after run

                 

                as you can see in screen shots, my cpu is highly loaded and REPORT1 user sessions are highly consuming CPU. also as you see at the last SS, one of each MANAGER and REPORT1 users sessions completed the plsql code almost at the same time.

                 

                so that makes me nervous. other limits on plan are working, like active session count or undo limit and so I can say that plan is actually working but cpu resource are not ? what do I miss here ?

                 

                edit: added the code I run in the sessions.

                • 5. Re: resource manager cpu limit problem
                  888157

                  I also create another group for MANAGER user and test it too (in case of OTHER_GROUPS group might have a problem) but result is the same...

                   

                  I run this query from Oracle:

                   

                  SELECT TO_CHAR(m.begin_time, 'HH:MI') TIME,
                    m.consumer_group_name,
                    round(m.cpu_consumed_time / 60000,2) avg_running_sessions,
                    m.cpu_wait_time     / 60000 avg_waiting_sessions,
                    d.mgmt_p1           *
                    (SELECT value FROM v$parameter WHERE name = 'cpu_count'
                    )/100 allocation
                  FROM v$rsrcmgrmetric_history m,
                    dba_rsrc_plan_directives d,
                    v$rsrc_plan p
                  WHERE m.consumer_group_name = d.group_or_subplan
                  AND p.name                  = d.plan
                  and m.begin_time between to_Date('08092013 18:10', 'ddmmyyyy hh24:mi') and to_Date('08092013 18:16', 'ddmmyyyy hh24:mi')
                  and consumer_group_name in ('MANAGER_GROUP', 'REPORTING_GROUP')
                  ORDER BY m.begin_time,
                    m.consumer_group_name;
                  

                   

                  and the result is:

                   

                  TIMECONSUMER_GROUP_NAMEAVG_RUNNING_SESSIONSAVG_WAITING_SESSIONSALLOCATION
                  06:10MANAGER_GROUP1,9604,8
                  06:10REPORTING_GROUP3,9100,4
                  06:11MANAGER_GROUP1,9604,8
                  06:11REPORTING_GROUP3,9100,4
                  06:12MANAGER_GROUP1,9304,8
                  06:12REPORTING_GROUP3,8500,4
                  06:13MANAGER_GROUP1,9604,8
                  06:13REPORTING_GROUP3,9100,4
                  06:14MANAGER_GROUP1,9604,8
                  06:14REPORTING_GROUP3,9200,4
                  06:15MANAGER_GROUP1,9604,8
                  06:15REPORTING_GROUP3,9200,4

                   

                  AVG_RUNNING_SESSIONS column indicate that how much cpu ratio is used for that group. as you see, REPORTING_GROUP consumes twice as MANAGER_GROUP.

                   

                  in plan, MANAGER_GROUP has 60% of cpu and REPORTING_GROUP has 5%... as in my previous example, 4 report1 user and 2 manager user run the codes and CPU resource distributed as equally...

                   

                  why is that ? report1 users should not be able to consume 5% of cpu and manager should have 60%.

                  • 6. Re: resource manager cpu limit problem
                    JustinCave

                    In the screenshot where you are saying that "CPU is overloaded", the CPU is only at 88%.  If the CPU is only at 88%, Resource Manager won't kick in.

                     

                    If you have 8 cores, you're probably going to need more than 16 threads in order to ensure that you're actually overloading the CPU.

                     

                    Justin

                    • 7. Re: resource manager cpu limit problem
                      888157

                      Hi justin,

                       

                      thanks for reply again. I made more tests and I just added one more session that I just did and my cpu become %100 percent. My system has 4 cores and via hyper threading it seems like 8 cores. even so in my case shouldn't it be less that report1 user' cpu consuming ? I just give 5% of cpu in resource manager but it is much more than that.

                       

                      edit: by the way in EM cpu is over the limit that it shows.

                      • 8. Re: resource manager cpu limit problem
                        888157

                        hi,

                         

                        I tested more, create more session and I finally see "resmgr: cpu quantum" event which is resource manager wait event. It still a bit strange to me. for my understanding, I gave 5% of cpu to REPORTING_GROUP so this group members will never exceed this limit even system has resources. okey, as I see this is not true if system has resources it will use it.

                         

                        in test case above, I have 4 cpu but because fo hyper threading db and os pretend like it is 8. so I decided to create more sessions and run the code using almost 10 session (manager and report1 users) . when I did that, I saw report1 users has resmgr: cpu quantum event and their codes takes more time to finish (no much but there is)

                         

                        it is still repot1 users has more cpu then I specified but it seems working too.

                         

                        here is screen shot:

                         

                        View image: cpu wait

                         

                        is there any ideas about this ?

                        • 9. Re: resource manager cpu limit problem
                          jgarry

                          It looks like each REPORT1 user is getting 5% of the total.  So each user is getting 5% of a cpu.  Is that so wrong?

                          • 10. Re: resource manager cpu limit problem
                            888157

                            hi jgarry,

                             

                            are you saying this according to last screen shot I sent ? if so yes I think you are right but I gave %5 percent of cpu to REPORTING_GROUP consumer group so each report1 user shouldn't get 5% of cpu , sum of all report1 users cpu consume should be 5%, isn't it right ?

                            • 11. Re: resource manager cpu limit problem
                              jgarry

                              You seem to have demonstrated that each user gets 5% of a cpu and the group gets 5% of all cpus.  I could be wrong though, maybe it will be more clear if you run many more, or limit the active session pool. Managing Resources with Oracle Database Resource Manager

                              • 12. Re: resource manager cpu limit problem
                                JustinCave

                                It looks like Resource Manager is behaving correctly.

                                 

                                If Manager sessions aren't asking for 95% of the CPU resources, whatever remains will be given to the Report users.  Oracle isn't going to starve the Report sessions for CPU if there is a bunch of idle CPU.

                                 

                                If there are 8 virtual cores and a bunch of single-threaded sessions, you would need at least 8 manager sessions before Manager could use 95% of the available CPU.  Most likely, you'd need 9 or 10. 

                                 

                                If you have only 6 Manager threads, those threads could consume at most 6/8 = 75% of the available CPU but probably more like 67% (to pick a number that makes the math easy).  That leaves 33% of the CPU for Report.  There are 4 Report sessions in your screenshot with a bit more than 2 cores available and they each seem to be spending a bit more than 50% of their time on the CPU, that seems about right.

                                 

                                Justin

                                • 13. Re: resource manager cpu limit problem
                                  888157

                                  yes Justin you are right. I thought that when I assign a resource limit to a consumer group, I thought, members of this group can not be consume more than that no matter what. I know that wrong.

                                   

                                  from oracle documentation:

                                  The currently active resource plan does not enforce allocation limits until CPU usage is at 100%. If the CPU usage is below 100%, the database is not CPU-bound and hence there is no need to enforce limits to ensure that all sessions get their designated resource allocation.

                                  About Oracle Database Resource Manager

                                   

                                  thanks a lot for help.