Autores (as): Arnold Colindres, Nerlyn Ramos, Orlando Morazán, Julissa Castro, Karla Ríos, Allan Rodríguez.
En la presente publicación, deseamos hacer un breve repaso de algunas de las técnicas que han sido de utilidad al momento de acometer la resolución de problemas de rendimiento y disminución de tiempos de ejecución en procesos de extracción y transformación relativos a Motores de Migración de datos o ETL’s de Data warehousing en que hemos participado y que consideramos recomendables según nuestra propia experiencia como alternativas de análisis en proyectos de similar naturaleza.
I. Scheduler Chains:
Cuando se tienen múltiples procesos y estos tiene una dependencia para su ejecución, suele ser muy tedioso si se opta por una ejecución manual, incluso si se opta por una ejecución programada mediante un Scheduler Jobs; resultando ser un inconveniente ya que no sabemos cuánto tiempo tardará en terminar su ejecución un determinado proceso, por lo que podemos colocar tiempos que sean muy prolongados o muy cortos entre los distintos Scheduler Jobs que se decida programar. Es aquí donde entra en juego una funcionalidad de oracle muy importante como lo es el uso del Scheduler Chains, esta herramienta nos permite optimizar el tiempo de ejecución de forma dinámica, es decir que se puede programar que un determinado proceso o conjunto de procesos se ejecuten inmediatamente después de su antecesor, o su ejecución en paralelo de tal manera que se reducen los tiempos de espera entre la terminación de un proceso y el comienzo del siguiente. Es muy importante tener definido cuales son los procesos que dependen uno de otro y cuales no para así poder definir de manera óptima cada uno de sus pasos, programas y reglas que son los tres componentes principales en la estructura de los scheduler chains, y que permiten tener el control y orden de lo que se desea ejecutar.
Fuente: (https://docs.oracle.com/cd/B28359_01/server.111/b28310/img/admin081.gif, 2020)
II. Particionamiento de tablas:
Para situaciones en las que debemos almacenar data histórica en una tabla que la pueden volverse voluminosa y a su vez deseamos hacer uso frecuente de la misma solo para consultar cierta porción de ella como por ejemplo los registros del día, del mes actual. Podría resultar una carga innecesaria que el motor de la base de datos siempre busque la información en la tabla completa con todo su histórico para solo trabajar con ese rango de información deseado, lo que deriva en un bajo performance del query.
Fuente: (https://www.dataprix.com/, 2020)
Es por ello que Oracle a partir de la base de datos 11g, introduce la funcionalidad de particionado, el cual permite que, a través de un determinado criterio definido en la creación del objeto, la base de datos separe de forma lógica la tabla en distintas partes, de manera que al realizar las consultas, mediante lo especificado en el cláusula where, la base de datos busque la información solo las particiones requeridas y evite buscar en la tabla completa. Este particionamiento puede ser de diferentes tipos (lista de valores predefinidos, rangos, hash) y deberá definirse según la necesidad particular. En el caso del particionamiento por range podría hacerse de forma dinámica mediante un interval, así por ejemplo cada vez que entre una nueva fecha o un nuevo mes o año a la tabla haga una partición nueva para ese criterio. Es importante mencionar que para particionar una tabla es necesario que la tabla sea creada con esa definición, no se puede particionar una tabla existente que no haya sido creada con esta definición, en dicho caso, se debe hacer una tarea de migración de la tabla nueva sin particionar a la nueva particionada.
III. Uso recomendable del Exists:
Los operadores «EXISTS» y «NOT EXISTS» se emplean para determinar si hay o no datos en una lista de valores. Estos operadores pueden emplearse con subconsultas correlacionadas para restringir el resultado de una consulta exterior a los registros que cumplen la subconsulta (consulta interior). Estos operadores retornan «true» (si las subconsultas retornan registros) o «false» (si las subconsultas no retornan registros). La función EXISTS busca la presencia de una sola fila que cumpla con los criterios establecidos en comparación de la declaración IN que busca todas las ocurrencias.
Por ejemplo:
TABLA1: Contiene 7 registros (Nombres).
TABLA2: Contiene 3 registros (Lugares).
Si se realiza una consulta con la cláusula IN y otra con EXISTS
Para la Consulta 1, todas las filas de la TABLA2 se leerán para cada fila de la TABLA1. El efecto será de las 3 filas leídas (de los lugares existentes en la TABLA2) por cada línea de la consulta principal (de las personas de la TABLA1).
En el caso de la Consulta 2, se leerá un máximo de 1 fila de TABLA2 para cada fila de TABLA1, debido a que se filtra con el registro existente en ambas tablas, reduciendo así la sobrecarga de procesamiento de la declaración.
Recomendación: Si la mayoría de los criterios de filtrado están en la subconsulta, entonces la utilización del IN puede ser más eficaz. Si la mayoría de los criterios de filtrado están en la consulta superior, entonces la utilización del EXISTS puede ser más eficaz.
IV. Truncate vs Delete
En ocasiones es necesario borrar registros de una tabla, y para ello existen dos maneras comunes de hacerlo: con TRUNCATE TABLE y con DELETE FROM. El comando DELETE ayuda a eliminar uno, algunos o todos los registros de una tabla. El comando TRUNCATE ayuda a eliminar todos los registros de una tabla. Reinicia la tabla como si se hubiese creado la tabla nuevamente.
| TRUNCATE | DELETE |
| Eliminan los datos, no la estructura. | Eliminan los datos, no la estructura. |
| Es una operación DDL. | Es una operación DML. |
| No se puede revertir al ejecutar Rollback | Puede ser revertido al ejecutar Rollback |
| No permite el borrado selectivo. No se puede usar la cláusula WHERE | Permite el borrado selectivo, mediante la cláusula WHERE. |
| Elimina todo el contenido de la tabla, es decir se eliminan las filas de la tabla, no se eliminan índices ni privilegios | Elimina uno, algunos o todo el contenido de la tabla según la condición, es decir se eliminan solo las filas especificadas, no se eliminan índices ni privilegios |
| Es la forma más rápida de eliminar el contenido de una tabla. | Forma más lenta de eliminar el contenido de una tabla |
| Usa espacio para deshacer, pero no tanto como DELETE. | Utiliza el espacio para deshacer. |
| Elimina el registro permanentemente | No elimina el registro permanentemente |
| No se puede ejecutar, si la tabla tiene keys asociadas, aun si no existiesen registros en la tabla que contiene la FK. | Se puede ejecutar si hay FK asociadas a la tabla, pero siempre y cuando no tenga registros asociados o la FK este deshabilitada. |
| Operación registrada en el log de transacciones, pero como un todo, en conjunto, no por eliminación individual. se registra como una liberación de las páginas de datos en las cuales existen los datos. | Operación registrada en el log de transacciones de los registros afectados, basada en registrar cada eliminación individual. |
| Recupera espacio en la BD. | No recupera espacio en la BD. |
| Asigna un nuevo ID de object de datos. | No afecta el ID del object de datos. |
| Es más rápido en performance y no usa tanto espacio para deshacer. | Es más lento y usa mucho espacio para deshacer (El espacio de cada registro). |
V. Uso de cláusula With:
La cláusula WITH (conocida formalmente como subquery_factoring_clause) se utiliza cuando se tienen subconsultas que involucren un SELECT. Lo que esta cláusula hace es que convierte un subquery en una tabla temporal que puede ser accedida N cantidad de veces dentro del query sin ser evaluada cada vez, ya sea como un campo (excepto en la cláusula GROUP BY) o como tabla para realizar un join. De esta forma, mejora los tiempos de respuesta en una consulta grande que necesite de subqueries para obtener su información.
VI. Parallel Hint:
Los Hints indican al optimizador de Base de Datos de qué manera se desea ejecutar las consultas para obtener un mejor resultado en tiempo de ejecución. Parallel Hint permite segmentar por bloques la lectura de las tablas (estas deberán de tener una operación de lectura completa) y ser procesadas por diferentes hilos o servidores. Al paralelizar se debe tener en cuenta que existirá un maestro y un esclavo, donde los esclavos procesaran los bloques asignados y el maestro se encargara de orquestar los diferentes procesos, unir los resultados y aplicar transformación dependiendo del caso.
Parallel Hint se permite utilizar para una o varias tablas tomando la siguiente sintaxis:
VII. Envío por Lotes:
Para la descarga de fuentes de datos voluminosas, puede usarse la aproximación de crear programas hilo que puedan correrse al mismo tiempo y hagan la descarga de forma paralela. Esta solución implicaría la definición de una tabla de control en la que se determine la cantidad de hilos y los rangos de llaves únicos de la información de la fuente que descargará cada hilo. Se deben crear tantos scheduler programs como hilos sean definidos y así cada program hará un select insert según su rango configurado y finalmente que un scheduler chain ejecute todos los programas de forma paralela para que de esa manera se distribuya la carga en los diferentes hilos y se reduzca el tiempo de respuesta general de la descarga de la fuente.
VIII. Bulk Upload/Insert:
Para actualizar/insertar por lotes y liberar memoria. Esta funcionalidad puede ser combinada con un CURSOR, BULK COLLECT y FORALL. Para poder utilizarla debes tener almacenado en una tabla los datos que deseas actualizar/insertar y leerla con un cursor, el bulk collect modera los lotes dependiendo del límite de registros establecidos y se puede realizar commit por cada lote. Es necesario saber que el bulk collect funciona solo si se ejecuta en el esquema donde se actualizara la tabla, no por dblink.
IX. Append Hint:
Indica al optimizador de Base de Datos que los datos a insertar tomen nuevos bloques a los existentes en las tablas, esto permite acelerar el proceso de inserción dado que no tiene que validar que bloques se encuentran vacíos y disponibles. Hay que tener en cuenta que al aplicar el append hint este bloquea la tabla dedicando el recurso a la operación del proceso que lo instancio, quiere decir que ninguna otra sesión o proceso podrá utilizar de este recurso hasta que se genere un commit luego de la inserción. No se recomienda la utilización del append hint cuando se utiliza en procesos que se paralelizan debido al bloqueo que se genera del primero proceso que haga la operación. Append hint se permite utilizar con la siguiente sintaxis:
X. Index:
Como sabemos, el uso de índices puede contribuir substancialmente en el performance de un proceso cuando estos son correctamente creados y utilizados. Hay ocasiones en que su implicación puede ser contraproducente o efecto cero, por lo que el tener en consideración las siguientes sugerencias pueden ser de relevante ayuda:
- Unusable y Rebuild Index: En muchas ocasiones debemos hacer procesos de volcado de gran cantidad de información desde determinada fuente hacia nuestras tablas, puede resultar en una ganancia de tiempo de respuesta, el desactivar los índices de la tabla antes del insert y luego reconstruirlos al finalizar el insert.
- Monitoring Usage: Para saber si se está utilizando un índice, mientras se hacen los trabajos de tunning de nuestro desarrollo, es posible activar la funcionalidad de monitoring usage, así, luego de la ejecución de los querys, en la vista del sistema v$object_usage se puede comprobar si el índices está siendo usado o no.
- Transformaciones en índices: Es preciso tener en consideración que si determinado atributo que posee un índice, al momento de hacer los cruces con otras tablas le es aplicada alguna transformación (aplicar un to_char por ejemplo) hará que posiblemente lo pierda y el motor de base de datos no considere ese índice en su plan de ejecución.
- Bitmap Indexes: Estos son utilizados en columnas de cardinalidad baja (columnas cuyos valores posibles no son más de 100) y en tablas que sean READ-ONLY o que no sean actualizadas frecuentemente por el mantenimiento que el bitmap requiere; por lo tanto, su uso se da mayormente en ambientes de data warehouse. Este es un índice especial que utiliza un arreglo de bits, Oracle asigna un bitmap por cada índice y los índices guardan punteros con múltiples filas agrupando por un valor en la columna. Este comportamiento ayuda a que la consulta optimiza la búsqueda en tablas grandes mejorando los tiempos de respuesta.
Con los apuntes esquematizados en el presente artículo esperamos haberle ayudado en la mejor comprensión del funcionamiento y la trascendencia para el éxito empresarial de un base de datos Oracle y no nos queda más que poner a sus órdenes a GO Consultores, donde cualquiera de nuestro equipo estará gustoso de exponerle nuestro servicio.