OracleMania en Español Volumen 1 - Page 11

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