Hoy  vamos a abordar los problemas de bloqueos desde otro punto de vista.

A pesar de que los bloqueos sonun problema propio de  la aplicación y debe de ser desde esta donde se solucione el evitar sesiones bloqueantes, un código de  aplicacion defectuoso  puede llegar a tirar abajo nuestra instancia , con lo que deberemos de acabar con este tipo de sesiones antes de que afecten a la disponibilidad del sistema.

Oracle nos da la opcion de limitar el tiempo en el que un usuario está IDLE a la vez que  bloquea otra sesion con el parámetro NEW_MAX_IDLE_BLOCKER.

Esta es la primera de un grupo de entradas en las que utilizaremos el gestor de recursos de Oracle para llevar acabo workarrounds que nos solucionen este problema.

Lo primero que haremos es crear un plan al que llamaremos «NO_LOCKS», para ello lo mas sencilllo es copiar el DEFAULT_PLAN desde el EM, si por el contrario queremos hacerlo desde linea de sqlplus tenemos que:

1. Crear una pending area (si lo hacemos desde sqlplus)
2. Crear el plan de recursos
3. Validar el pending area
4. Enviar/presentar  (submit) esta pending area

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan( plan => 'NO_LOCKS',
 comment => 'Plan para eliminar bloqueos desde el gestor de recursos, copiado de DEFAULT_PLAN'
);
dbms_resource_manager.create_plan_directive(
    plan => 'NO_LOCKS',
    group_or_subplan => 'SYS_GROUP',   
    comment => 'Asignamos 75% de cpu al grupo SYS ',
    mgmt_p1 => 75
);
dbms_resource_manager.create_plan_directive(
    plan => 'NO_LOCKS',
    group_or_subplan => 'DEFAULT-CONSUMER_GROUP',   
    comment => 'Asignaremos el 90% de CPU al grupo de consumidores por defecto ',
    mgmt_p2 => 90
);
dbms_resource_manager.create_plan_directive(
    plan => 'NO_LOCKS',
    group_or_subplan => 'OTHER_GROUPS',   
    comment => 'Este grupo debe de aparecer ',
    mgmt_p2 => 5
);
dbms_resource_manager.create_plan_directive(
    plan => 'NO_LOCKS',
    group_or_subplan => 'ORA$DIAGNOSTICS',   
    comment => 'Asignamos 5% de cpu al grupo ORA$DIAGNOSTICS en el segundo nivel ',
    mgmt_p2 => 5
 );
 DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;

Con esto tenemos  un nuevo plan llamado «NO_LOCKS» que es similar al DEFAULT_PLAN.

Lo que vamos ha hacer ahora es modificar el subplan que se aplica a todos los usuarios (DEEFAULT_CONSUMER_GROUP) para que no los deje bloquear recursos mas de 5 segundos.

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
    plan => 'NO_LOCKS',
    group_or_subplan => 'DEFAULT_CONSUMER_GROUP',   
    new_comment => 'Subplan para bloqueadores, similar a DEFAULT_CONSUMER_GROUP pero  limitado',
    new_max_idle_blocker_time=>5
    );
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
END;

Si miramos los cambios desde el EM podremos ver como   esta el parámetro

Ahora solamente nos quedará activar el nuevo plan y probarlo

Esto lo comprobamos con

SQL>ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'NO_LOCKS';

SQL> SELECT VALUE FROM V$PARAMETER  WHERE name = 'resource_manager_plan';

VALUE
--------------------------------------------------------------------
NO_LOCKS

Ahora vamos ha hacer la prueba de que funciona

Veamos en que grupo de consumidores está el usuario HR

SQL> select initial_rsrc_consumer_group from dba_users where username='HR';

INITIAL_RSRC_CONSUMER_GROUP
------------------------------
DEFAULT_CONSUMER_GROUP

Nos conectamos con el usuario HR y bloquearemos la tabla REGIONS

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'HH24:MI:SS';
Session altered.
SQL>  select sysdate from dual;
SYSDATE
--------
12:08:13
SQL> select * from  regions where  REGION_ID=1 for update;
 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
SQL>  select sysdate from dual;
SYSDATE
--------
12:30:18

En el segundo select sysdate vemos como,  pesar de que la tabla esta bloqueada, y que han pasado 15 segundos el gestor de recursos no nos ha hechado.¿habremos hecho algo mal?

La respuesta es que no hemos hecho nada mal, lo que está ocurriendo es que la consulta que hemos ejecutado esta bloqueando algo, pero no esta bloqueando  a nadie .

Que ocurre si intentamos ejecutar una consulta en otra sesion que  se vea bloqueada por esta?

En el momento en l que el gestor de recursos detecta que nuestra primera sesion esta bloqueando a otra empieza a contar los segundos que nuestra primera consulta esta «idle».Al llegar a los 5 segundos matará nuestra consulta bloqueante y liberará sus recursos.

Si tras esos 5 segundos  volvemos a recargar la ventana del EM  veremos que no hay bloqueos, y  si nos vamos a la ventana donde hemos ejecutado nuestra consulta bloquante nos encontraremos con el error:

ORA-02396: ha excedido el tiempo máximo de inactividad, vuelva a conectarse

Con lo que , de manera algo brusca y desde la base de datos hemos conseguido acabar con todos los bloqueos de usuario que estén inactivas mas de 5 segundos.

LLegados a este punto tenemos a TODOS los usuarios de la base de datos sin poder llevar a cabo un bloqueo de mas de 5 segundos en cualquier hora del día.

Esto nos va a generar muchos problemas a la hora de los procesos batch e incluso en alguna transaccion pesada, con lo que lo mejor que podemos hacer el volverlo atrás .

El único valor de esta entrada es como ejercicio didactico, asi que, mas nos valdría volver a activar en la base de datos el DEFAULT_PLAN o el INTERNAL_PLAN

En las siguientes entradas afinaremos este plan NO_LOCKS para limitarlo por esquemas y por franja horaria, haciendo de la limitación NEW_MAX_IDLE_BLOCKER_TIME  algo util.