Create Resource Manager , Resource Group / Consumer groups in Oracle Database to limit CPU resources

 

Step to create Resource Manager consumer groups in Oracle Database 

1. First confirm the exact PROGRAM values

 

 

SELECT program,

       COUNT(*) sessions

FROM v$session

WHERE type = 'USER'

GROUP BY program

ORDER BY sessions DESC;

 

2. Create a consumer group

 

 

BEGIN

   DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

 

   DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

      consumer_group => 'MYAPP_CPU_GROUP',

      comment        => 'CPU restricted sessions for myapp.exe'

   );

 

   DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

   DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

END;

/

 

3. Map PROGRAM to the consumer group

 

 

BEGIN

   DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

 

   DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(

      attribute      => DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM,

      value          => 'myapp.exe',

      consumer_group => 'MYAPP_CPU_GROUP'

   );

 

   DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

   DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

END;

/

 

4. Create the CPU resource plan

 

 

BEGIN

   DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

 

   DBMS_RESOURCE_MANAGER.CREATE_PLAN(

      plan    => 'APP_CPU_PLAN',

      comment => 'CPU control based on application program'

   );

 

   DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

      plan             => 'APP_CPU_PLAN',

      group_or_subplan => 'MYAPP_CPU_GROUP',

      comment          => 'Restricted application',

      mgmt_p1          => 20

   );

 

   DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

      plan             => 'APP_CPU_PLAN',

      group_or_subplan => 'OTHER_GROUPS',

      comment          => 'All other sessions',

      mgmt_p1          => 80

   );

 

   DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

   DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

END;

/

 

Enable it:

 

 

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'APP_CPU_PLAN';

 

 

 

SELECT sid,

       serial#,

       username,

       program,

       module,

       service_name,

       resource_consumer_group

FROM v$session

WHERE type = 'USER'

ORDER BY program;

 SELECT name,

       active_sessions,

       execution_waiters,

       ROUND(consumed_cpu_time/1000,2) consumed_sec,

       ROUND(cpu_wait_time/1000,2) wait_sec,

       cpu_waits

FROM   v$rsrc_consumer_group

ORDER BY consumed_cpu_time DESC;

SELECT

    m.consumer_group_name,

    /* CPU capacity */

    m.num_cpus                                  AS available_cpus,

    /* CPU consumption */

    ROUND(m.avg_cpu_utilization, 2)             AS consumed_cpu_pct,

    ROUND(m.num_cpus *

          m.avg_cpu_utilization / 100, 2)       AS consumed_cpus,

    /* CPU remaining */

    ROUND(100 - m.avg_cpu_utilization, 2)       AS unused_cpu_pct,

    ROUND(m.num_cpus *

          (100 - m.avg_cpu_utilization) / 100,

          2)                                    AS unused_cpus,

    /* Resource Manager limit */

    ROUND(m.cpu_utilization_limit, 2)           AS cpu_limit_pct,

    /* CPU waiting */

    ROUND(g.cpu_wait_time / 1000, 2)            AS cpu_wait_sec,

    g.cpu_waits,

    /* Current activity */

    g.active_sessions,

    g.execution_waiters,

    /* Useful throttling indicator */

    CASE

        WHEN g.execution_waiters > 0

          OR g.cpu_wait_time > 0

        THEN 'YES - THROTTLING'

        ELSE 'NO THROTTLING'

    END                                         AS throttling_status

FROM v$rsrcmgrmetric m

JOIN v$rsrc_consumer_group g

  ON m.consumer_group_name = g.name

ORDER BY m.avg_cpu_utilization DESC;

SELECT

    MAX(num_cpus)                                      AS available_cpus,

    ROUND(SUM(avg_cpu_utilization), 2)                 AS consumed_cpu_pct,

    ROUND(100 - SUM(avg_cpu_utilization), 2)           AS unused_cpu_pct,

    ROUND(MAX(num_cpus) *

          SUM(avg_cpu_utilization) / 100, 2)           AS consumed_cpus,

    ROUND(MAX(num_cpus) *

          (100 - SUM(avg_cpu_utilization)) / 100, 2)   AS unused_cpus

FROM v$rsrcmgrmetric;