Articulo especial
base de datos
11
4 value=>'david',
5 consumer_group=>'oraworldCG2');
6 END;
7 /
PL/SQL procedure successfully completed.
4. Crear un plan de recursos
SQL> EXEC dbms_resource_manager.create_plan('oraworldPDBPlan');
PL/SQL procedure successfully completed.
5. Crear directivas para el plan de recursos
SQL> BEGIN
2 dbms_resource_manager.create_plan_directive(
3 plan=>'oraworldPDBPlan',
4 group_or_subplan=>'oraworldCG1',
5 mgmt_p1=>60);
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 dbms_resource_manager.create_plan_directive(
3 plan=>'oraworldPDBPlan',
4 group_or_subplan=>'oraworldCG2',
5 mgmt_p1=>40);
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 dbms_resource_manager.create_plan_directive(
3 plan=>'oraworldPDBPlan',
4 group_or_subplan=>'OTHER_GROUPS',
5 mgmt_p1=>0);
6 END;
7 /
PL/SQL procedure successfully completed.
6. Validar el “pending area”
SQL> EXEC dbms_resource_manager.validate_pending_area();
PL/SQL procedure successfully completed.
7. Enviar el “pending area”
SQL> EXEC dbms_resource_manager.submit_pending_area();
PL/SQL procedure successfully completed.
8. Habilitar el plan de recursos en la PDB.
SQL> ALTER SYSTEM SET resource_manager_plan=oraworldPDBPlan scope=both;
System altered.
Nota: Antes de hacer cualquier mantenimiento ya sea de directivas o en el plan de recursos el paso No. 1 debe ser realizado antes del mantenimiento y los pasos 8 y 9 deben ser realizados después del mantenimiento.
Monitorear actividad del “Resource Manager”
SQL> SELECT name, active_sessions, queue_length,
consumed_cpu_time, cpu_waits, cpu_wait_time
FROM v$rsrc_consumer_group;
SQL> SELECT se.sid sess_id, co.name consumer_group,
se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time
FROM v$rsrc_session_info se, v$rsrc_consumer_group co
WHERE se.current_consumer_group_id = co.id;
SQL> SELECT group_or_subplan, mgmt_p1, mgmt_p2, mgmt_p3, mgmt_p4
FROM dba_rsrc_plan_directives WHERE plan = 'DSS_PLAN';
Vistas útiles
V$RSRC_PLAN
V$SQL_MONITOR
V$RSRCMGRMETRIC
V$RSRCMGRMETRIC_HISTORY
V$RSRC_PLAN
V$RSRC_CONSUMER_GROUP
V$RSRC_SESSION_INFO
V$RSRC_PLAN_HISTORY
V$RSRC_CONS_GROUP_HISTORY
V$RSRCMGRMETRIC
V$RSRCMGRMETRIC_HISTORY