Einsatz des Oracle Database Resource Managers

Mit Hilfe des Oracle Database Resource Managers können Datenbank Ressourcen (CPU und I/O) unterschiedlichen Nutzergruppen mit unterschiedlicher Priorität zur Verfügung gestellt werden.

Um die Funktion und Konfiguration zu demonstrieren verwenden wir eine VirtualBox VM mit einer installierten Oracle 19c Enterprise Edition Datenbank. Anschliessend wird das jeweils aktuelle RU installiert. Das Betriebssystem der VM ist Linux, jedoch ist das Vorgehen unter Windows identisch.

Vorbereitung

Innerhalb der Pluggable Database (pdb1) erstellen wir nun zwei Datenbank Benutzer (user1 und user2) und 2 Consumer Groups (cg1 und cg2). Mittels Consumer Group Mappings werden die Benutzer den jeweiligen Consumer Groups zugeordnet.

# als User oracle ausfuehren
. ora19.env
sqlplus -S / as sysdba<<EOF
alter session set container=pdb1;
-- Erstellung user1 und user2
grant create session, unlimited tablespace, create job, create table to user1 identified by changeme;
grant create session, unlimited tablespace, create job, create table to user2 identified by changeme;

BEGIN
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

  -- Erstellung Consumer Group cg1 und cg2
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    consumer_group      => 'CG1',
    cpu_mth   => 'ROUND-ROBIN');
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    consumer_group      => 'CG2',
    cpu_mth   => 'ROUND-ROBIN');

  -- Zuweisung user1 zu cg1 und user2 zu cg2
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
        'ORACLE_USER', 'USER1', 'CG1');
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
        'ORACLE_USER', 'USER2', 'CG2');

  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

EOF

[oracle@lin7 ~]$ sqlplus -S / as sysdba<<EOF
> alter session set container=pdb1;
> -- Erstellung user1 und user2
> grant create session, unlimited tablespace to user1 identified by changeme;
> grant create session, unlimited tablespace to user2 identified by changeme;
>
> BEGIN
>   DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
>   DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
>
>   -- Erstellung Consumer Group cg1 und cg2
>   DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
>     consumer_group      => 'CG1',
>     cpu_mth   => 'ROUND-ROBIN');
>   DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
>     consumer_group      => 'CG2',
>     cpu_mth   => 'ROUND-ROBIN');
>
>   -- Zuweisung user1 zu cg1 und user2 zu cg2
>   DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
>         'ORACLE_USER', 'USER1', 'CG1');
>   DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
>         'ORACLE_USER', 'USER2', 'CG2');
>
>   DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
> END;
> /
>
> EOF

Session altered.


Grant succeeded.


Grant succeeded.


PL/SQL procedure successfully completed.

[oracle@lin7 ~]$

Als nächstes erstellen wir einen Resource Manager Plan (plan1). Über diesen Plan werden wir später festlegen, welche Consumer Group (und somit welche Datenbank Sessions) welchen Anteil der Resourcen zugeteilt bekommen. Falls wir mehrere Pläne erstellt haben können diese zu verschiedenen Zeiten aktiviert werden.

# als User oracle ausfuehren
. ora19.env
sqlplus -S / as sysdba<<EOF
alter session set container=pdb1;

-- Erstellung des Resource Manager Plan plan1
BEGIN
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CREATE_PLAN(
    PLAN => 'PLAN1',
    COMMENT => '',
    MGMT_MTH => 'EMPHASIS');
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    PLAN => 'plan1',
    GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
    COMMENT => '');
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

EOF

Als MGMT_MTH stehen die beiden Werte EMPHASIS oder RATIO zur Verfügung. Bei EMPHASIS werden später die Resource Anteile prozentual (z.B. 80%/20%) und bei RATIO anteilig (z.B. 4/1) angegeben.

[oracle@lin7 ~]$ # als User oracle ausfuehren
[oracle@lin7 ~]$ . ora19.env
[oracle@lin7 ~]$ sqlplus -S / as sysdba<<EOF
> alter session set container=pdb1;
>
> -- Erstellung des Resource Manager Plan plan1
> BEGIN
>   DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
>   DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
>   DBMS_RESOURCE_MANAGER.CREATE_PLAN(
>     PLAN => 'PLAN1',
>     COMMENT => '',
>     MGMT_MTH => 'EMPHASIS');
>   DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
>     PLAN => 'plan1',
>     GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
>     COMMENT => '');
>   DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
> END;
> /
> EOF

Session altered.


PL/SQL procedure successfully completed.

[oracle@lin7 ~]$

Konfiguration einer CPU Resource Verteilung von 80% zu 20%

Im folgenden Beispiel wird eine CPU Resourcen Verteilung von 80% für alle Mitglieder der Consumer Group cg1 (bei uns: Datenbank Benutzer: user1) und 20% für alle Mitglieder der Consumer Group cg2 (bei uns: Datenbank Benutzer: user2) konfiguriert. Dies wird über sogenannte Group Directives erreicht:

# als User oracle ausfuehren
. ora19.env
sqlplus -S / as sysdba<<EOF
alter session set container=pdb1;

-- Erstellung der Group Directives für cg1 und cg2
BEGIN
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    PLAN => 'PLAN1',
    GROUP_OR_SUBPLAN => 'CG1',
    SHARES => 80);
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    PLAN => 'PLAN1',
    GROUP_OR_SUBPLAN => 'CG2',
    SHARES => 20);
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

-- Aktivieren des Resource Manager Plan plan1
alter system set resource_manager_plan = 'PLAN1';

EOF

[oracle@lin7 ~]$ # als User oracle ausfuehren
[oracle@lin7 ~]$ . ora19.env
[oracle@lin7 ~]$ sqlplus -S / as sysdba<<EOF
> alter session set container=pdb1;
>
> -- Erstellung der Group Directives für cg1 und cg2
> BEGIN
>   DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
>   DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
>   DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
>     PLAN => 'PLAN1',
>     GROUP_OR_SUBPLAN => 'CG1',
>     SHARES => 80);
>   DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
>     PLAN => 'PLAN1',
>     GROUP_OR_SUBPLAN => 'CG2',
>     SHARES => 20);
>   DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
> END;
> /
>
> -- Aktivieren des Resource Manager Plan plan1
> alter system set resource_manager_plan = 'PLAN1';
>
> EOF

Session altered.


PL/SQL procedure successfully completed.


System altered.

[oracle@lin7 ~]$

Um zu testen, ob die Direktiven funktionieren verbinden wir uns mit den Datenbank Benutzern user1 und user2 und erstellen jeweils 8 jobs, welche CPU Last erzeugen:

# als User oracle ausfuehren
. ora19.env
sqlplus -S /nolog<<EOF
-- user1
connect user1/changeme@//localhost/pdb1.fritz.box
SET SERVEROUTPUT ON;

DECLARE
  job_name         VARCHAR2(30);      
  cpu_count        NUMBER := 8;          
  loop_iterations  NUMBER := 500000000; 

BEGIN
  FOR i IN 1..cpu_count LOOP
    job_name := 'CPU_LOAD_JOB_' || i;

    DBMS_SCHEDULER.create_job(
      job_name        => job_name,
      job_type        => 'PLSQL_BLOCK',
      job_action      => 'DECLARE num NUMBER := 1; BEGIN FOR idx IN 1..' || loop_iterations || 
                         ' LOOP num := MOD(num + SQRT(idx), 13); END LOOP; END;',
      enabled         => TRUE
    );

    DBMS_OUTPUT.put_line('Created job: ' || job_name);
  END LOOP;
END;
/
-- user2
connect user2/changeme@//localhost/pdb1.fritz.box
SET SERVEROUTPUT ON;

DECLARE
  job_name         VARCHAR2(30);      
  cpu_count        NUMBER := 8;          
  loop_iterations  NUMBER := 500000000; 

BEGIN
  FOR i IN 1..cpu_count LOOP
    job_name := 'CPU_LOAD_JOB_' || i;

    DBMS_SCHEDULER.create_job(
      job_name        => job_name,
      job_type        => 'PLSQL_BLOCK',
      job_action      => 'DECLARE num NUMBER := 1; BEGIN FOR idx IN 1..' || loop_iterations || 
                         ' LOOP num := MOD(num + SQRT(idx), 13); END LOOP; END;',
      enabled         => TRUE
    );

    DBMS_OUTPUT.put_line('Created job: ' || job_name);
  END LOOP;
END;
/
EOF

Created job: CPU_LOAD_JOB_1
Created job: CPU_LOAD_JOB_2
Created job: CPU_LOAD_JOB_3
Created job: CPU_LOAD_JOB_4
Created job: CPU_LOAD_JOB_5
Created job: CPU_LOAD_JOB_6
Created job: CPU_LOAD_JOB_7
Created job: CPU_LOAD_JOB_8

PL/SQL procedure successfully completed.

Created job: CPU_LOAD_JOB_1
Created job: CPU_LOAD_JOB_2
Created job: CPU_LOAD_JOB_3
Created job: CPU_LOAD_JOB_4
Created job: CPU_LOAD_JOB_5
Created job: CPU_LOAD_JOB_6
Created job: CPU_LOAD_JOB_7
Created job: CPU_LOAD_JOB_8

PL/SQL procedure successfully completed.

Die pro Consumer Group verbrauchte CPU können wir uns im SQL Developer unter DBA => Resource Manager => Statistics anschauen:

Die Sessions können mit folgendem Kommando gestoppt werden:

for i in `ps -eaf|grep ora_j00|grep -v grep|awk {'print $2'}` ; do echo $i ; kill $i ; done

Beschränkung der erlaubten I/O Menge für Sessions einer Consumer Group

Um dies zu demonstrieren setzen wir die (CPU) shares zurück auf -1 was den Resource Manager in Hinblick auf die CPU Verteilung deaktiviert. Um die erlaubte I/O Menge der Consumer Group cg2 zu beschränken nutzen wir den Parameter SWITCH_IO_MEGABYTES. Durch Angabe der SWITCH_GROUP CANCEL_SQL wird das Statement abgebrochen, sobald es die erlaubte I/O Menge überschreitet:

# als User oracle ausfuehren
. ora19.env
sqlplus -S / as sysdba<<EOF
alter session set container=pdb1;

-- Erstellung der Group Directives für cg1 und cg2
BEGIN
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
    PLAN => 'PLAN1',
    GROUP_OR_SUBPLAN => 'CG1',
    NEW_SHARES => NULL);
  DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
    PLAN => 'PLAN1',
    GROUP_OR_SUBPLAN => 'CG2',
    NEW_SHARES => NULL,
    NEW_SWITCH_GROUP => 'CANCEL_SQL',
    NEW_SWITCH_IO_MEGABYTES => 20);
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

EOF

[oracle@lin7 ~]$ # als User oracle ausfuehren
[oracle@lin7 ~]$ . ora19.env
[oracle@lin7 ~]$ sqlplus -S / as sysdba<<EOF
> alter session set container=pdb1;
>
> -- Erstellung der Group Directives für cg1 und cg2
> BEGIN
>   DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
>   DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
>   DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
>     PLAN => 'PLAN1',
>     GROUP_OR_SUBPLAN => 'CG1',
>     NEW_SHARES => NULL);
>   DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
>     PLAN => 'PLAN1',
>     GROUP_OR_SUBPLAN => 'CG2',
>     NEW_SHARES => NULL,
>     NEW_SWITCH_GROUP => 'CANCEL_SQL',
>     NEW_SWITCH_IO_MEGABYTES => 20);
>   DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
> END;
> /
>
> EOF

Session altered.


PL/SQL procedure successfully completed.

[oracle@lin7 ~]$

Nun versuchen wir mit beiden Datenbank Benutzern eine Tabelle zu erstellen:

# als User oracle ausfuehren
. ora19.env
sqlplus -S /nolog<<EOF
-- user1
connect user1/changeme@//localhost/pdb1.fritz.box
-- drop table t;
create table t tablespace users as select a2.* from all_objects a1, all_objects a2 where rownum<=1e6;
-- user2
connect user2/changeme@//localhost/pdb1.fritz.box
-- drop table t;
create table t tablespace users as select a2.* from all_objects a1, all_objects a2 where rownum<=1e6;
EOF

[oracle@lin7 ~]$ sqlplus -S /nolog<<EOF
> -- user1
> connect user1/changeme@//localhost/pdb1.fritz.box
> -- drop table t;
> create table t tablespace users as select a2.* from all_objects a1, all_objects a2 where rownum<=1e6;
> -- user2
> connect user2/changeme@//localhost/pdb1.fritz.box
> -- drop table t;
> create table t tablespace users as select a2.* from all_objects a1, all_objects a2 where rownum<=1e6;
> EOF

Table created.

create table t tablespace users as select a2.* from all_objects a1, all_objects a2 where rownum<=1e6
                                                    *
ERROR at line 1:
ORA-56720: I/O data limit exceeded - call aborted


[oracle@lin7 ~]$

Wie wir sehen kann user1 aus der Consumer Group cg1 die Tabelle t erstellen, jedoch bricht das create table statement von user2 aus der Consumer Group cg2 mit dem Fehler ORA-56720: I/O data limit exceeded - call aborted ab, da er die erlaubte I/O Menge überschritten hat.

Nützliche Infos

ORA-29370: pending area is already active

Wenn beim Ausführen eines DBMS_RESOURCE_MANAGER Befehls folgender Fehler kommt:

ERROR at line 1:
ORA-29370: pending area is already active
ORA-06512: at "SYS.DBMS_RMIN_SYS", line 3694
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 659
ORA-06512: at line 1

Dann könnte dies im Zusammenhang mit Oracle Doc ID 2244655.1 stehen. Als Workaround empfiehlt Oracle:

• alter system flush shared_pool;
• oder
• Neustart der Oracle Datenbank

Dokumentation

• Oracle 19 PL/SQL Packages and Types Reference DBMS_RESOURCE_MANAGER
• Oracle 19 Oracle Database Administrator’s Guide Managing Resources with Oracle Database Resource Manager