This forum is currently locked. You can't register or post questions at this time. (read more)

Use of DBMS_LOCK.SLEEP to force a consumer group switch

All posts relating to Oracle database administration.

Moderator: Tim...

JimK
Member
Posts: 1
Joined: Wed Apr 02, 2014 11:31 pm

Use of DBMS_LOCK.SLEEP to force a consumer group switch

Postby JimK » Thu Apr 03, 2014 1:41 am

Hi Tim -

First-time poster here.

I would like to ask you about one of the code examples you have in the "Resource Manager Enhancements in Oracle Database 11g Release 1" article in the Oracle 11g section of oracle-base.com.

Under "Built-In Resource Plans" there is an example PL/SQL routine, running while the Oracle-supplied MIXED_WORKLOAD_PLAN is enabled, which results in a consumer group switch once the routine consumes more than 60 seconds. I ran several tests of the code in my sandbox environment, but wasn't able to get the switch to take place.

My sandbox environment is Oracle 11.2.0.1 on Windows 6.0.6002 (long story). I ran the code using SQL*Plus 11.2.0.1.0.

After puzzling it over for a while, I think I figured out what was going on.

By definition, SWITCH_TIME is in CPU seconds, not wall-clock seconds. (For example, see the definition for the SWITCH_TIME parameter in the DBMS_RESOURCE_PLAN.CREATE_PLAN_DIRECTIVE procedure in http://docs.oracle.com/cd/E11882_01/app ... m#ARPLS050). So I ran a test using DBMS_LOCK.SLEEP and DBMS_UTILITY.GET_CPU_TIME() to determine CPU time used, the result of which is that DBMS_LOCK.SLEEP doesn't appear to consume CPU seconds at the same rate as clock seconds. In fact, it appears to consume hardly any CPU time at all. Here is a sample test using DBMS_LOCK.SLEEP(10) to sleep for 10 clock seconds. (DBMS_UTILITY.GET_CPU_TIME() returns the CPU time in centiseconds, thus I divide the returned value by 100 to get seconds.)


Code: Select all

begin
 dbms_output.put_line('CPU time at start:  '||dbms_utility.get_cpu_time()/100);
 dbms_lock.sleep(10);
 dbms_output.put_line('CPU time at finish: '||dbms_utility.get_cpu_time()/100);
end;
/
CPU time at start:  .09
CPU time at finish: .09

PL/SQL procedure successfully completed.


The result shows no increase in CPU time used. So I tested the following FOR loop that incremented an integer variable several hundred million times (and burned more than 60 seconds of CPU time as a result):


Code: Select all

DECLARE
  l_int integer := 0;
BEGIN
  dbms_output.put_line('CPU use at start:  ' ||dbms_utility.get_cpu_time()/100);

  FOR i in 1..400000000 LOOP
     l_int := l_int + 1;
  END LOOP;

  dbms_output.put_line('CPU use at finish: ' ||dbms_utility.get_cpu_time()/100);
END;
/
CPU use at start:  .15
CPU use at finish: 75.83

PL/SQL procedure successfully completed.


Once I replaced the DBMS_LOCK call in your example code with this FOR loop, the code worked and the consumer group switch took place.

Here is the script I ran, with its results. The script is based on most of the code in the "Built-In Resource Plans" section. I added several calls to DBMS_UTILITY.GET_CPU_TIME() at appropriate places. In the script, after setting up and verifying the environment, I run the original code that calls DBMS_LOCK.sleep(65), then I run the code again, replacing the DBMS_LOCK call with the FOR loop. (I ran the script as user TEST, which I previously granted the DBA role to.)

Code: Select all

@switch_test
set serveroutput on size 1000000
set echo on
set trimspool on
col Name format a35
col CPU format 999.99


-- Set the resource_manager_plan parameter.

show parameter resource_manager_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string

alter system set resource_manager_plan = 'MIXED_WORKLOAD_PLAN';

System altered.


show parameter resource_manager_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string      MIXED_WORKLOAD_PLAN


-- Grant access on INTERACTIVE_GROUP and BATCH_GROUP to user TEST
-- and set the initial resource group for TEST to INTERACTIVE_GROUP.

BEGIN
  -- Assign users to consumer groups
  DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
    grantee_name   => 'test',
    consumer_group => 'interactive_group',
    grant_option   => FALSE);

  DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
    grantee_name   => 'test',
    consumer_group => 'batch_group',
    grant_option   => FALSE);

  DBMS_RESOURCE_MANAGER.set_initial_consumer_group('test', 'interactive_group');
END;
/

PL/SQL procedure successfully completed.



-- Show the resource groups granted to this user and its
-- initial resource group.

select granted_group,
       initial_group
from   dba_rsrc_consumer_group_privs
where  grantee='TEST';

GRANTED_GROUP                  INI
------------------------------ ---
INTERACTIVE_GROUP              YES
BATCH_GROUP                    NO


SELECT resource_consumer_group
FROM   v$session
WHERE  audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

RESOURCE_CONSUMER_GROUP
--------------------------------
INTERACTIVE_GROUP



-- Display the DBA_RSRC_PLAN_DIRECTIVES values for the
-- MIXED_WORKLOAD_PLAN and the INTERACTIVE_GROUP resource group.

select switch_group,
       switch_time,
       switch_time_in_call
from   dba_rsrc_plan_directives
where  plan = 'MIXED_WORKLOAD_PLAN'
  and  group_or_subplan = 'INTERACTIVE_GROUP';

SWITCH_GROUP                   SWITCH_TIME SWITCH_TIME_IN_CALL
------------------------------ ----------- -------------------
BATCH_GROUP                             60                  60



-- Show the current CPU time usage for this session.
-- (The value is in centiseconds, so we divide by 100 for seconds.)

select dbms_utility.get_cpu_time()/100 CPU from dual;

    CPU
-------
    .15



-- Run the original routine (which has the call to DBMS_LOCK.SLEEP)
-- and show that the resource group has not switched.

DECLARE
  l_cg   v$session.resource_consumer_group%TYPE;
BEGIN
  SELECT resource_consumer_group
  INTO    l_cg
  FROM    v$session
  WHERE  audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

  DBMS_OUTPUT.put_line('Current Consumer Group: ' || l_cg);

  -- Sleep to cause switch based on time.
  DBMS_LOCK.sleep(65);

  SELECT resource_consumer_group
  INTO    l_cg
  FROM    v$session
  WHERE  audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

  DBMS_OUTPUT.put_line('Current Consumer Group: ' || l_cg);
END;
/
Current Consumer Group: INTERACTIVE_GROUP
Current Consumer Group: INTERACTIVE_GROUP

PL/SQL procedure successfully completed.



-- Show the session CPU time usage again and note that
-- it didn't increase enough to cause a switch.

select dbms_utility.get_cpu_time()/100 CPU from dual;

    CPU
-------
    .15



-- Run the routine again, this time replacing the call to
-- DBMS_LOCK.sleep(65) with a FOR loop that adds 1 to
-- an integer variable.  Execute the loop 400 million times.  The
-- resulting amount of CPU time used will exceed the SWITCH_TIME
-- limit in the resource plan directive, causing the session's
-- resource group to switch to BATCH_GROUP.  (In my environment
-- this routine takes about 80 seconds of clock time to run and
-- uses about 76 seconds of CPU time.)

DECLARE
  l_cg   v$session.resource_consumer_group%TYPE;
  l_int integer := 0;
BEGIN
  SELECT resource_consumer_group
  INTO    l_cg
  FROM    v$session
  WHERE  audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

  DBMS_OUTPUT.put_line('Current Consumer Group: ' || l_cg);

  -- FOR loop appears here instead of DBMS_LOCK.sleep(65).
  FOR i in 1..400000000 LOOP
     l_int := l_int + 1;
  END LOOP;

  SELECT resource_consumer_group
  INTO    l_cg
  FROM    v$session
  WHERE  audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

  DBMS_OUTPUT.put_line('Current Consumer Group: ' || l_cg);
END;
/
Current Consumer Group: INTERACTIVE_GROUP
Current Consumer Group: BATCH_GROUP

PL/SQL procedure successfully completed.



-- Show the session CPU time usage once again to confirm that
-- it increased enough to cause a switch.

select dbms_utility.get_cpu_time()/100 CPU from dual;

    CPU
-------
  78.53



-- Reset the resource_manager_plan parameter.

alter system set resource_manager_plan = '';

System altered.


spool off


So my question is, does DBMS_LOCK.SLEEP consume CPU time differently in different operating systems? Is it something else? Or did I get something wrong? (other than using an obsolete OS, but like I said, long story...)

Apologies if I'm a bit verbose in this post, especially with the sample script/results. I wanted to make sure I described and verified everything as well as possible. Please let me know if you need additional information.

Thanks,
Jim

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Use of DBMS_LOCK.SLEEP to force a consumer group switch

Postby Tim... » Sat Apr 05, 2014 2:41 pm

Hi.

Interestingly, we are both correct... :)

The article was written against 11gR1, where the switch time was clock time. As mentioned here:

http://docs.oracle.com/cd/B28359_01/app ... sthref5866

As you quite rightly point out, in 11gR2 it is not clock time, but CPU time...

http://docs.oracle.com/cd/E11882_01/app ... sthref7347

I'll put a note in my article to make the distinction... :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 5 guests