{"id":5301,"date":"2021-06-10T09:16:37","date_gmt":"2021-06-10T09:16:37","guid":{"rendered":"https:\/\/swiv.com.br\/using-resource-manager-with-cdb-and-pdbs\/"},"modified":"2026-05-27T20:02:31","modified_gmt":"2026-05-27T19:02:31","slug":"using-resource-manager-with-cdb-and-pdbs","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/06\/10\/using-resource-manager-with-cdb-and-pdbs\/","title":{"rendered":"Using Resource Manager with CDB and PDBs"},"content":{"rendered":"\n

O Resource Manager nos permite gerenciar recursos de hardware alocados para o nosso database, e assim como j\u00e1 era implementado em ambientes non-CDB’s, tamb\u00e9m temos essa op\u00e7\u00e3o para a arquitetura Multitenant. Com ele, podemos controlar, por exemplo, recursos de CPU Time, Degree de Paralelismo, Limite de Sess\u00f5es em Idle, etc. Nesse artigo, vamos explorar a cria\u00e7\u00e3o de 2 planos de Resource Manager, com “Shares Directives” distintos, para vermos o reflexo disso entre 2 PDBs. A tabela abaixo ilustra o cen\u00e1rio de teste:<\/p>\n\n\n\n

Resource Manager Plan<\/td>PDB<\/td>Shares Directive<\/td><\/tr>
BALANCEADO<\/td>HIPOFISE1<\/td>1<\/td><\/tr>
BALANCEADO <\/td>HIPOFISE2<\/td>1<\/td><\/tr>
DESBALANCEADO<\/td> HIPOFISE1 <\/td>3<\/td><\/tr>
DESBALANCEADO <\/td> HIPOFISE2 <\/td>1<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Validando nossos PDB’s:<\/p>\n\n\n

\n[oracle@quiasma admin]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Thu Jun 10 05:36:56 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL> SHOW PDBS;\n \n    CON_ID CON_NAME                       OPEN MODE  RESTRICTED\n---------- ------------------------------ ---------- ----------\n         2 PDB$SEED                       READ ONLY  NO\n         3 HIPOFISE2                      READ WRITE NO\n         4 HIPOFISE1                      READ WRITE NO\n<\/pre><\/div>\n\n\n
Agora vamos conectar em cada PDB, e criar o objeto PL\/SQL abaixo, que realiza opera\u00e7\u00f5es aritm\u00e9ticas, consumindo assim o recurso de CPU, que ser\u00e1 gerenciado pelos planos de Resource Manager:<\/p>\n\n\n
\nCREATE OR REPLACE PROCEDURE SYSTEM.HIT_CPU ( P_LOOP NUMBER ) \nIS \n    V_BEGIN DATE; \n    V NUMBER; \n BEGIN \n  V_BEGIN := SYSDATE; \n  FOR I IN 1..P_LOOP*10000 LOOP \n   V := SQRT(I); \n  END LOOP; \n  DBMS_OUTPUT.PUT_LINE('ELAPSED TIME: ' || ROUND((SYSDATE-V_BEGIN)*24*60*60,2 )|| ' seconds'); \n  END HIT_CPU; \n\/\n<\/pre><\/div>\n\n\n
PDB HIPOFISE1:<\/p>\n\n\n
\n[oracle@quiasma admin]$ sqlplus system\/oracle@HIPOFISE1\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Thu Jun 10 05:38:26 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle.  All rights reserved.\n \nLast Successful login time: Thu Jun 10 2021 05:36:48 -03:00\n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL> CREATE OR REPLACE PROCEDURE SYSTEM.HIT_CPU ( P_LOOP NUMBER )\nIS\n        V_BEGIN DATE;\n        V NUMBER;\n BEGIN\n  V_BEGIN := SYSDATE;\n  FOR I IN 1..P_LOOP*10000 LOOP\n   V := SQRT(I);\n  END LOOP;\n  DBMS_OUTPUT.PUT_LINE('ELAPSED TIME: ' || ROUND((SYSDATE-V_BEGIN)*24*60*60,2 )|| ' seconds');\n  END HIT_CPU;\n\/  2    3    4    5    6    7    8    9   10   11   12\n \nProcedure created.\n<\/pre><\/div>\n\n\n
PDB HIPOFISE2:<\/p>\n\n\n
\n[oracle@quiasma admin]$ sqlplus system\/oracle@HIPOFISE2\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Thu Jun 10 05:41:05 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle.  All rights reserved.\n \nLast Successful login time: Thu Jun 10 2021 05:38:26 -03:00\n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL> CREATE OR REPLACE PROCEDURE SYSTEM.HIT_CPU ( P_LOOP NUMBER )\nIS\n        V_BEGIN DATE;\n        V NUMBER;\n BEGIN\n  V_BEGIN := SYSDATE;\n  FOR I IN 1..P_LOOP*10000 LOOP\n   V := SQRT(I);\n  END LOOP;\n  DBMS_OUTPUT.PUT_LINE('ELAPSED TIME: ' || ROUND((SYSDATE-V_BEGIN)*24*60*60,2 )|| ' seconds');\n  END HIT_CPU;\n\/  2    3    4    5    6    7    8    9   10   11   12\n \nProcedure created.\n<\/pre><\/div>\n\n\n
Agora, conectado no CDB$ROOT, vamos criar o primeiro plano, onde a prioridade de CPU Time resource \u00e9 igualmente distribu\u00edda entre os 2 PDBs:<\/p>\n\n\n
\n[oracle@quiasma admin]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Thu Jun 10 05:42:27 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL> EXEC DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();\n \nPL\/SQL procedure successfully completed.\n \nSQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();\n \nPL\/SQL procedure successfully completed.\n \nSQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN('BALANCEADO', 'One share to HIPOFISE1 and HIPOFISE2');\n \nPL\/SQL procedure successfully completed.\n \nSQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE('BALANCEADO', 'HIPOFISE1', shares => 1);\n \nPL\/SQL procedure successfully completed.\n \nSQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE('BALANCEADO', 'HIPOFISE2', shares => 1);\n \nPL\/SQL procedure successfully completed.\n<\/pre><\/div>\n\n\n
Criando o segundo plano, onde o PDB HIPOFISE1 tem prioridade 3 vezes maior que o PDB HIPOFISE2:<\/p>\n\n\n
\nSQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN('DESBALANCEADO', 'HIPOFISE1 tem prioridade');\n \nPL\/SQL procedure successfully completed.\n \nSQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE('DESBALANCEADO', 'HIPOFISE1', shares => 3);\n \nPL\/SQL procedure successfully completed.\n \nSQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE('DESBALANCEADO', 'HIPOFISE2', shares => 1);\n \nPL\/SQL procedure successfully completed.\n \nSQL> EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();\n \nPL\/SQL procedure successfully completed.\n \nSQL> EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();\n \nPL\/SQL procedure successfully completed.\n<\/pre><\/div>\n\n\n
Confirmando a exist\u00eancia dos 2 planos:<\/p>\n\n\n
\nSQL> col plan format a20\nSQL> SELECT Plan from CDB_CDB_Rsrc_Plans WHERE CON_ID = 1 AND PLAN IN ('BALANCEADO','DESBALANCEADO') ORDER BY 1;\n \nPLAN\n--------------------\nBALANCEADO\nDESBALANCEADO\n \nSQL> col pluggable_database format a30\nSQL> SELECT PLAN, PLUGGABLE_DATABASE, SHARES FROM CDB_CDB_RSRC_PLAN_DIRECTIVES WHERE CON_ID = 1 AND PLAN IN ('BALANCEADO','DESBALANCEADO') ORDER BY 1, 2;\n \nPLAN                 PLUGGABLE_DATABASE                 SHARES\n-------------------- ------------------------------ ----------\nBALANCEADO           HIPOFISE1                               1\nBALANCEADO           HIPOFISE2                               1\nBALANCEADO           ORA$AUTOTASK\nBALANCEADO           ORA$DEFAULT_PDB_DIRECTIVE               1\nDESBALANCEADO        HIPOFISE1                               3\nDESBALANCEADO        HIPOFISE2                               1\nDESBALANCEADO        ORA$AUTOTASK\nDESBALANCEADO        ORA$DEFAULT_PDB_DIRECTIVE               1\n \n8 rows selected.\n<\/pre><\/div>\n\n\n
Habilitando o primeiro plano:<\/p>\n\n\n
\nSQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = BALANCEADO;\n \nSystem altered.\n \nSQL> col name format a20\nSQL> SELECT NAME FROM V$RSRC_PLAN WHERE CON_ID = 1;\n \nNAME\n--------------------\nBALANCEADO\n<\/pre><\/div>\n\n\n
Agora, vou abrir 2 sess\u00f5es de Putty, logando como system no PDB HIPOFISE1, e mais 2 sess\u00f5es conectando como system no PDB HIPOFISE2:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Com o “SET SERVEROUTPUT ON”, vou disparar a execu\u00e7\u00e3o do comando “EXEC HIT_CPU(10000)” nas 4 sess\u00f5es, o mais r\u00e1pido poss\u00edvel, para rodarem em paralelo:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Podemos perceber que os tempos ficaram muito pr\u00f3ximos (120,121,117 e 118 segundos), o que nos mostra que a distribui\u00e7\u00e3o de recurso de CPU Time foi uniforme aos 2 PDBs:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Alterando o plano do Resource Manager, para que passe a utilizar o que d\u00e1 mais prioridade ao PDB HIPOFISE1:<\/p>\n\n\n
\nSQL> conn \/ as sysdba\nConnected.\nSQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = DESBALANCEADO;\n \nSystem altered.\n \nSQL> SELECT NAME FROM V$RSRC_PLAN WHERE CON_ID = 1;\n \nNAME\n--------------------------------\nDESBALANCEADO\n<\/pre><\/div>\n\n\n
Disparando o mesmo teste, de forma simult\u00e2nea, percebemos que as sess\u00f5es do HIPOFISE1 tiveram prioridade (78 e 79 segundos), se comparadas com as sess\u00f5es do HIPOFISE2 (116 e 117 segundos). Percebemos tamb\u00e9m que a diferen\u00e7a de tempo n\u00e3o \u00e9 de 3 vezes, por\u00e9m devemos considerar que uma vez finalizada o processamento necess\u00e1rio no HIPOFISE1, o Resource Manager pode engajar mais recursos para o processamento do HIPOFISE2. A prioriza\u00e7\u00e3o s\u00f3 acontece quando existe a concorr\u00eancia de um contra o outro.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Alterando o CDB$ROOT para n\u00e3o utilizar nenhum plano customizado por n\u00f3s:<\/p>\n\n\n
\nSQL> conn \/ as sysdba\nConnected.\nSQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';\n \nSystem altered.\n \nSQL> col name format a30\nSQL> SELECT NAME FROM V$RSRC_PLAN WHERE CON_ID = 1;\n \nNAME\n------------------------------\nORA$INTERNAL_CDB_PLAN\n<\/pre><\/div>\n\n\n
Deletando os 2 planos usados neste artigo:<\/p>\n\n\n
\nSQL> begin\nDBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();\nDBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();\nDBMS_RESOURCE_MANAGER.DELETE_CDB_PLAN('BALANCEADO');\nDBMS_RESOURCE_MANAGER.DELETE_CDB_PLAN('DESBALANCEADO');\nDBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();\nDBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();\nend;\n\/  2    3    4    5    6    7    8    9\n \nPL\/SQL procedure successfully completed.\n<\/pre><\/div>\n\n\n
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laborat\u00f3rio pessoal para fins de aprendizado.<\/p>\n","protected":false},"excerpt":{"rendered":"
O Resource Manager nos permite gerenciar recursos de hardware alocados para o nosso database, e assim como j\u00e1 era implementado em ambientes non-CDB’s, tamb\u00e9m temos essa op\u00e7\u00e3o para a arquitetura Multitenant. Com ele, podemos controlar, por exemplo, recursos de CPU Time, Degree de Paralelismo, Limite de Sess\u00f5es em Idle, etc. Nesse artigo, vamos explorar a […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,8],"tags":[],"class_list":["post-5301","post","type-post","status-publish","format-standard","hentry","category-administration","category-multitenant"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5301","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=5301"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5301\/revisions"}],"predecessor-version":[{"id":9106,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5301\/revisions\/9106"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=5301"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=5301"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=5301"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}