Oracle Database Discussions

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.