Oracle Resource Manager realtime testing

Open three Terminals
Terminal 1: View the CPU utilization with top -c Linux command
Terminal 2: Login as user sys and execute the resource consumer-related SQL
Terminal 3: Login as user dilli to generate the CPU load

Terminal 1: Viewing the CPU utilization with the top -c command

Login as user sys on terminal 2 and change the SQL prompt to SYS> 

sqlplus / as sysdba
set SQLPROMPT "SYS> "

Create user dilli and grant required privileges for testing on the terminal where user SYS is logged on.

Create user dilli 

identified by oracle 

default tablespace users 

quota unlimited on users;

grant connect,resource to dilli;

grant select any dictionary to dilli;

Clear and create pending area.
exec dbms_resource_manager.clear_pending_area;
exec dbms_resource_manager.create_pending_area;

Create consumer group, plan and plan directives.

exec dbms_resource_manager.create_consumer_group(consumer_group=>'DEVELOPERS',comment=>'application developers');
exec dbms_resource_manager.create_plan (plan=>'DAYTIME',comment=>'plan for normal working hours');

Make sure to include the other_groups on the plan directives otherwise we will get the error while validating the pending area.
begin
dbms_resource_manager.create_plan_directive(plan=>'DAYTIME',
group_or_subplan=>'DEVELOPERS', 
cpu_p1=>1,
comment=>'Developers are allowed to use 1 percent of cpu during peak hours',
max_utilization_limit =>10);
end;
/

begin
dbms_resource_manager.create_plan_directive(plan=>'DAYTIME',
group_or_subplan=>'OTHER_GROUPS', 
cpu_p1=>99,
comment=>'Rest CPU utilization');
end;
/

Error on validating if we have missed the OTHER_GROUPS

Validate the pending area and submit it.
exec dbms_resource_manager.validate_pending_area; 
exec dbms_resource_manager.submit_pending_area;

On terminal 3 login as user Dilli and change SQLprompt to DILLI>


















Create table mnop with the contents of dba_objects and execute a SQL statement that will
cross join with the same tables and generate a high CPU load.

create table mnop as select * from dba_objects;
select count(*)
from (
select a.* 
from mnop a 
cross join mnop b 
cross join mnop c 
cross join mnop d
cross join mnop e
order by 1 desc);



Verify the CPU load is increasing for process ID 20853. Currently, 94.6% of the CPU is used.

Switch user DILLI to the DEVELOPERS consumer group and set the resource manager plan to DAYTIME.

BEGIN

DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER ('DILLI','DEVELOPERS'); 

END;

alter system set resource_manager_plan = 'force:DAYTIME';

We can note the CPU load for the process has been decreased. We can verify the CPU utilization on PID 20853. It is now just 10.3%.

Revert back to the DEFAULT_PLAN to check for the load on the process PID 20853.

alter system set resource_manager_plan = 'force:DEFAULT_PLAN';

CPU load is increasing again because we have reverted to DEFAULT_PLAN.

Set DAYTIME plan as the resource manage plan and note the CPU load is decreasing.

alter system set resource_manager_plan = 'force:DAYTIME';

CPU load is decreasing.




Finally, clean up all the stuff.

Cancel the execution and exit from the SQL session.
Press CTRL + C 
exit;

Revert back to the DEFAULT_PLAN before dropping the plan.
alter system set resource_manager_plan = 'force:DEFAULT_PLAN';

Drop users and delete resource consumer groups and plans.
drop user dilli cascade;

exec dbms_resource_manager.clear_pending_area;
exec dbms_resource_manager.create_pending_area;
exec dbms_resource_manager.delete_plan (plan=>'DAYTIME');
exec dbms_resource_manager.delete_consumer_group(consumer_group=> 'DEVELOPERS');
exec dbms_resource_manager.validate_pending_area; 
exec dbms_resource_manager.submit_pending_area;