8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Oracle Resource Manager Enhancements in Oracle Database 12c Release 1 (12.1)

This article describes the enhancements to automatic consumer group switching in Oracle 12c Release 1 (12.1).

Related articles.

Runaway Query Management - Automatic Consumer Group Switching

Managing runaway queries using automatic consumer group switching in resource manager has a long history.

Oracle 12c introduces a number of changes related to consumer group switching. The SWITCH_GROUP parameter can now also be set to LOG_ONLY, so the trigger for a potential switch is logged, but no actual consumer group switch is performed. Plan directives can now include the SWITCH_IO_LOGICAL and SWITCH_ELAPSED_TIME parameters to trigger a consumer group switch, so the current list of parameters for the CREATE_PLAN_DIRECTIVE procedure is now as follows.

The allowable values of the SWITCH_GROUP parameter are described below.

For all the switch-related parameters, there is an equivalent NEW_* parameter for altering the values using the UPDATE_PLAN_DIRECTIVE procedure.

Here is an example of automatic consumer group switching to cancel a SQL statement.

We create a test user.

CONN sys/SysPassword1@//localhost:1521/pdb1 AS SYSDBA

--DROP USER testuser1 CASCADE;
CREATE USER testuser1 IDENTIFIED BY testuser1 QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE PROCEDURE TO testuser1;

In the following example we have a resource plan with a consumer group called NORMAL_CG for normal sessions. Any sessions with the NORMAL_CG consumer group that have a single call using CPU for more than 60 seconds will have that SQL cancelled.

BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;

  -- Create plan
  DBMS_RESOURCE_MANAGER.create_plan(
    plan    => 'long_running_query_plan',
    comment => 'Plan to handle long running queries.');

  -- Create consumer groups
  DBMS_RESOURCE_MANAGER.create_consumer_group(
    consumer_group => 'normal_cg',
    comment        => 'Consumer group for normal sessions.');

  -- Assign consumer groups to plan and define priorities
  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan                 => 'long_running_query_plan',
    group_or_subplan     => 'normal_cg',
    comment              => 'Normal Priority',
    mgmt_p1              => 100,
    switch_group         => 'CANCEL_SQL',
    switch_time          => 60,
    switch_for_call      => TRUE);

  DBMS_RESOURCE_MANAGER.create_plan_directive(
    plan             => 'long_running_query_plan',
    group_or_subplan => 'OTHER_GROUPS',
    comment          => 'Default',
    mgmt_p2          => 100);

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

We allow the TESTUSER1 user to switch to the NORMAL_CG consumer group, then we set the initial consumer group for the TESTUSER1 user to NORMAL_CG.

BEGIN
  DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
    grantee_name   => 'testuser1',
    consumer_group => 'normal_cg',
    grant_option   => FALSE);

  DBMS_RESOURCE_MANAGER.set_initial_consumer_group('testuser1', 'normal_cg');
END;
/

Finally we activate the plan.

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'long_running_query_plan';

We can monitor the current consumer group assignment with the following query.

COLUMN username FORMAT A30
COLUMN resource_consumer_group FORMAT A30

SELECT username, resource_consumer_group
FROM   v$session
WHERE  username = 'TESTUSER1';

Leave this session open so we can monitor the progress.

In a separate session we connect to the test user, create a function that just sits on CPU for the specified number of minutes, then query the function.

CONN testuser1/testuser1@//localhost:1521/pdb1

CREATE OR REPLACE FUNCTION burn_cpu (p_mins IN NUMBER)
  RETURN NUMBER
AS
  l_start_time DATE;
  l_number     NUMBER := 1;
BEGIN
  l_start_time := SYSDATE;
  LOOP
    EXIT WHEN SYSDATE - l_start_time > (p_mins/24/60);
    l_number := l_number + 1;
  END LOOP;
  RETURN 0;
END;
/

SELECT burn_cpu (5) FROM dual;

Back in our privileged session we see the initial consumer group.

COLUMN username FORMAT A30
COLUMN resource_consumer_group FORMAT A30

SELECT username, resource_consumer_group
FROM   v$session
WHERE  username = 'TESTUSER1';

USERNAME                       RESOURCE_CONSUMER_GROUP
------------------------------ ------------------------------
TESTUSER1                      NORMAL_CG

SQL>

After approximately 60 seconds the query calling the BURN_CPU function has been cancelled.

SQL> SELECT burn_cpu (5) FROM dual;

Error starting at line : 1 in command -
SELECT burn_cpu (5) FROM dual
Error report -
ORA-00040: active time limit exceeded - call aborted

SQL>

We clean up the example by disabling the plan, then deleting it.

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;

  DBMS_RESOURCE_MANAGER.delete_plan_cascade(
    plan    => 'long_running_query_plan');

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

V$SQL_MONITOR

The V$SQL_MONITOR view includes four new resource manager columns.

Resource Manager with Container Databases (CDB) and Pluggable Databases (PDB)

In a multitennent environment resource manager performs two separate tasks. At the CDB level it controls the resources allocated to each PDB, allowing you to prioritise some PDBs over others. At the PDB level it controls the resources allocated to each session connected to the PDB, allowing you to prioritise some sessions over others, just as it does in pre-12c instances.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.