Cuarentena a Planes de Ejecución Perjudiciales - Oracle Database 19c

Por Francisco Riccio
Publicado en Mayo 2019




Introducción



Oracle Database 19c nos provee una nueva funcionalidad que permite bajo ciertos parámetros establecidos aislar un plan de ejecución de una sentencia SQL ya identificada como perjudicial para evitar su ejecución. Esta funcionalidad se puede integrar con Resource Manager para evitar correr planes de ejecución donde ya se identificaron que son perjudiciales en el pasado.

La implementación de la nueva funcionalidad puede ser realizada de manera explícita a través del paquete DBMS_SQLQ o automáticamente a través de Resource Manager cuando una sentencia SQL es terminada por algún criterio establecido, ambos métodos definen si una sentencia SQL o un plan de ejecución deben estar en cuarentena por defecto por 53 semanas.






Implementación



A. Método Explícito a través del paquete DBMS_SQLQ

Las pruebas realizadas serán hechas sobre una tabla llamada TEST la cual cuenta con 20 millones de filas.
Se ejecutará una consulta SQL para posteriormente definirla a cuarentena, esto a través de 3 opciones:

Sentencia SQL:



Sentencia SQL: select count(*) from test t1, test t2;

SQL ID: dwmg00cvtnhz2

PLAN HASH: 4067767418

Opción 1: Configurando en Cuarentena una sentencia SQL y todos sus planes de ejecución a través de su SQL ID

Función: DBMS_SQL.CREATE_QUARANTINE_BY_SQL_ID




Si ejecutamos nuevamente la consulta obtendremos el siguiente mensaje:


Opción 2: Configurando en Cuarentena una sentencia SQL y todos sus planes de ejecución a través de la codificación literal de la sentencia

Función: DBMS_SQL.CREATE_QUARANTINE_BY_SQL_TEXT


Opción 3: Configurando en Cuarentena un plan de ejecución específico para una sentencia SQL

Función: DBMS_SQL.CREATE_QUARANTINE_BY_SQL_ID o DBMS_SQL.CREATE_QUARANTINE_BY_SQL_TEXT



A diferencia de las opciones previas, esta última opción permitirá la ejecución de la sentencia SQL siempre y cuando el plan de ejecución escogido por el optimizador no esté en cuarentena.






B. Resource Manager

A través de Resource Manager se puede limitar los recursos que podrán ser consumidos por una sentencia SQL e inclusive cancelarla.

A continuación, se muestra una configuración de Resource Manager la cual servirá para integrarla con esta nueva funcionalidad:



El plan configurado de Resource Manager permitirá que el usuario FRICCIO no pueda consumir más de 50% de procesamiento de los cores asignados a la base de datos considerando un 100% de consumo. Además estará limitado a ejecutar consultas no mayores a 300 segundos (5 minutos), en caso contrario su consulta será anulada.

Validamos el plan definido en Resource Manager con la nueva funcionalidad de Cuarentena:



Como se puede apreciar, la sentencia SQL fue cancelada por Resource Manager al consumir más de 5 minutos y por ende es registrado para estar en cuarentena. Al volver a ejecutar la sentencia y usar el mismo plan de ejecución está es evitada.





C. Operaciones sobe las Cuarentenas


C1. Establecer umbrales sobre cuarentenas creadas.

Es posible crear umbrales basado en los siguientes recursos:

  • CPU Time (segundos)
  • Elapsed Time (segundos)
  • I/O MB
  • # Physical Reads
  • # Logical Reads

En el siguiente ejemplo se modificará la cuarentena previamente creada para definir un umbral que defina la ejecución de la consulta SQL a 30 segundos.

La implementación se realizará a través del procedimiento: DBMS_SQLQ.ALTER_QUARENTINE




Las constantes son:

CPU_TIME

ELAPSED_TIME

IO_MEGABYTES

IO_REQUESTS

IO_LOGICAL

ENABLED

AUTOPURGE

 



Las constantes:

  • ENABLED = Habilita o deshabilita la configuración de la cuarentena.
  • AUTOPURGE = Habilita o deshabilita la eliminación de la cuarentena. Por defecto está activada y su periodo de eliminación de una cuarentena es de 53 semanas.

Nota: Si se crea o modifica una política de Resource Manager y se define un tiempo menor o igual al definido en el umbral, la sentencia SQL no se ejecutará, en caso contrario, se ejecutará con la finalidad de tener una probabilidad de finalizar la consulta a un tiempo menor al establecido en el plan de Resource Manager.




C2. Exportar cuarentenas.

La exportación se ejecuta a través del procedimiento: DBMS_SQLQ.CREATE_STGTAB_QUARENTINE



La herramienta creará una tabla llamada TABLA_CUARENTENA y lo almacenará en el tablespace del Usuario quien ejecutó el procedimiento además de colocarlo como owner.

Añadida la tabla en la nueva base de datos se ingresa las cuarentenas a través de la función DBMS_SQLQ.PACK_STGTAB_QUARANTINE.

Poblada la tabla se exporta e importa hacia la nueva base de datos y se inicia el proceso de carga de cuarentenas a través de la función DBMS_SQLQ.UNPACK_STGTAB_QUARANTINE.





C3. Listar las cuarentenas creadas.

Las cuarentenas creadas pueden ser consultas a través de la vista: DBA_SQL_QUARANTINE

Nota 1: En la vista V$SQL se cuentan con los campos SQL_QUARANTINE y AVOIDED_EXECUTIONS que podrían dar información adicional sobre el número de veces que la consulta fue prevenida de ejecutar.

Nota 2: Es posible revisar la configuración de los umbrales a través de la función DBMS_SQLQ.GET_PARAM_VALUE_QUARANTINE




C4. Eliminación de una cuarentena

La eliminación se realiza a través del procedimiento: DBMS_SQLQ.DROP_QUARANTINE.




Conclusión



Esta nueva funcionalidad provista en Oracle Database 19c permite complementar el beneficio de Resource Manager para controlar los recursos del servidor extendiéndolo a prevenir planes de ejecución y sentencias SQL ya identificadas como peligrosas bajo experiencias previas y así mantener una estabilidad adecuada en la base de datos.




Francisco Riccio, actualmente se desempeña como Arquitecto de Soluciones en Oracle Perú y es instructor de cursos oficiales de certificación Oracle. Es un Oracle Certified Professional en productos de Oracle Application, Base de Datos, Cloud & Virtualización.

Este artículo ha sido revisado por el equipo de productos Oracle y se encuentra en cumplimiento de las normas y prácticas para el uso de los productos Oracle.