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.
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.
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:
this is EM before run the code:
this is execution:
this is EM running scripts (cpu is overload):
and finally, this after the complete of plsql:
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.
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:
TIME CONSUMER_GROUP_NAME AVG_RUNNING_SESSIONS AVG_WAITING_SESSIONS ALLOCATION 06:10 MANAGER_GROUP 1,96 0 4,8 06:10 REPORTING_GROUP 3,91 0 0,4 06:11 MANAGER_GROUP 1,96 0 4,8 06:11 REPORTING_GROUP 3,91 0 0,4 06:12 MANAGER_GROUP 1,93 0 4,8 06:12 REPORTING_GROUP 3,85 0 0,4 06:13 MANAGER_GROUP 1,96 0 4,8 06:13 REPORTING_GROUP 3,91 0 0,4 06:14 MANAGER_GROUP 1,96 0 4,8 06:14 REPORTING_GROUP 3,92 0 0,4 06:15 MANAGER_GROUP 1,96 0 4,8 06:15 REPORTING_GROUP 3,92 0 0,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%.
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.
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:
is there any ideas about this ?
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
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.
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.
thanks a lot for help.