Optimización SQL en Oracle

A no ser que el motivo por el cual empecemos a optimizar sentencias sea por quejas sobre una consulta en concreto, lo más común será que ciertas partes de una aplicación empiecen a ralentizarse a medida que va aumentando el volumen de datos que contienen las tablas y los usuarios se quejen a los responsables de la base de datos y de la aplicación.

Evidentemente, más vale prevenir que curar. Si durante la fase de análisis de una aplicación se ha involucrado a los desarrolladores y administradores, junto a los analistas, para construir un diseño eficiente de tablas e índices con una buena integridad referencial y con un estudio detallado de los procesos enfocado al rendimiento, la mitad del camino estará hecho. Si durante la fase de desarrollo se pueden realizar pruebas con volúmenes reales, similares a los existentes en producción, o se destina un tiempo para la optimización de las sentencias, el resto de causas de una posible caída de rendimiento consistirán en situaciones propias del entorno real de producción y serán puntos localizados de código que no resultará difícil aislar para un futuro estudio.

En el ajuste de rendimientos de sentencias SQL, tanto los desarrolladores como los administradores tienen un trabajo que hacer en equipo. Los unos sin los otros desconocen una parte del problema. El SQL y el PL/SQL son lenguajes de ejecución en el servidor. El desarrollador sabe lo que envía desde la aplicación, y el administrador sabe lo que se está ejecutando. Un primer documento para poner a las dos partes a debatir es un informe de rendimientos.

A lo largo de las versiones de Oracle ha existido una herramienta para registrar qué está sucediendo en el servidor. Al principio consistía en un par de scripts, uno para iniciar la recolección de estadísticas del servidor y otro para cerrar las estadísticas y generar un informe de texto. Eran los scripts utlbstat.sql y utlestat.sql (conocidos como begin stats y end stats). El fichero de texto que generaba este último script se llamaba “report.txt”. En él no había información sobre sentencias SQL concretas ni detalles sobre su actividad, únicamente se centraba en consumos de recursos de servidor como memoria, CPU, actividad de bloqueos, esperas, uso y aprovechamiento de áreas de memoria, volumen de ordenaciones en disco o en memoria, etc.

El fichero “report.txt” no proporcionaba información sobre el SQL procesado. Para ello no había más remedio que habilitar el parámetro sql_trace=true y posteriormente depurar los ficheros de traza, ordenando las sentencias por tipo de consumo.

A partir de la versión Oracle 8i, este método es sustituido por STATSPACK, un paquete de recopilación de estadísticas de consumo de servidor que incluye la generación de un tipo de informe mucho más detallado, con menciones a las sentencias SQL con mayor consumo (de disco, de memoria, de CPU, mayor número de ejecuciones, etc.), además de información adicional de asesores de memoria, en un formato de documento más comprensible para el ojo humano. En las versiones Oracle 8i y Oracle 9i, esta es la herramienta adecuada de generación de informes de rendimiento.

El paquete STATSPACK era opcional. Se instalaba en el servidor y se programaba la ejecución de una captura de actividad o snapshot cada cierto tiempo, que registraba los valores de memoria, accesos a disco, sentencias SQL procesadas y demás parámetros de modo que, lanzando un script, se podía generar un informe de actividad calculado entre dos snapshots determinados. Si la programación de los snapshots se hacía para cada hora, el informe de rendimiento permitía acotar, a partir de un snapshot de inicio y otro de fin, la actividad del servidor producida durante ese intervalo de tiempo.

Si bien se trata de un paquete opcional, sí resulta muy conveniente tenerlo instalado si aún disponemos de las versiones Oracle 8i y Oracle 9i en nuestros sistemas. Sin ese paquete, la actividad de rendimiento del servidor es mucho más difícil de ver, pues ha de hacerse en vistas dinámicas de rendimiento como por ejemplo la vista V$SQLAREA, donde reside el SQL ejecutado recientemente en memoria, o habilitando las trazas como se hacía en las versiones Oracle 7 y Oracle 8.

A partir de Oracle 10g, un componente llamado AWR (Automatic Workload Repository) se encarga de realizar, gestionar y mantener las instantáneas de rendimiento en un nuevo repositorio mucho más completo. Los informes de AWR tienen un nivel de detalle muy alto, son precisos y claros. Informan de los consumos de memoria, el aprovechamiento de las cachés, los accesos a disco, las áreas de memoria de usuarios, etc.

No obstante, un informe de rendimiento es como un análisis de sangre, o como un historial médico. Toda la información está relacionada con el funcionamiento de un sistema, de una arquitectura informática. Se detallan las métricas de consumo sin entrar a valorar si son adecuadas o no. Un proceso que lee 1 GB de bloques en disco no es bueno ni malo: simplemente es un proceso que lee 1 GB de bloques en disco, y nos tocará a los profesionales determinar si es necesario leer ese giga y cómo ha de leerse.

Fragmento de la Parte 2. Para seguir leyendo, consigue el libro.

En general, la mayoría de los objetos que contienen datos de usuario en una base de datos son tablas apiladas. En las tablas apiladas las filas van amontonándose en los bloques hasta llegar al umbral máximo de capacidad y, dado que esta inserción no sigue ningún orden, para recuperar una fila concreta Oracle debe recorrer todo el segmento de la tabla al completo, salvo que tenga forma de conocer el rowid de esa fila mediante un índice.

Estas son, básicamente, las reglas del juego en el acceso a las filas de una tabla, y se aplican para la mayoría de sentencias del tipo SELECT. Este es, de hecho, el motivo por el cual el principal recurso de optimización es crear un índice por una columna no indexada.

De hecho, las tablas y los índices comunes son las estructuras más versátiles y proporcionan un rendimiento adecuado para la mayoría de los casos. No obstante, ni los índices son siempre la mejor solución, ni todas las tablas tienen por qué tener esta estructura apilada. Existen estructuras físicas que, por su definición, pueden almacenar las filas ordenándolas o clasificándolas de forma que su lectura sea más eficiente en ciertas situaciones. Estas estructuras son tablas con organización de índice (llamadas también tablas IOT), clusters indexados y clusters hash.

Encadenamiento y migración de filas en tablas apiladas

En el almacenamiento de las filas existen dos situaciones en las cuales una fila puede no caber en un bloque: bien porque la longitud de la fila sea mayor que el tamaño de bloque de la tabla, o bien porque una modificación de la fila incremente el tamaño de la fila y esta no quepa en el bloque donde fue insertada junto con otras filas.

Estas dos situaciones provocan que las filas se encadenen o migren a otro bloque.

El encadenamiento es simple. Cuando una fila ocupa, en la inserción, un espacio mayor que el tamaño del bloque, esta debe romperse en trozos y almacenarse de forma “encadenada” sobre varios bloques reservados en la tabla. El encadenamiento es inevitable si el tamaño natural de las filas no cabe en un bloque simple, por ejemplo cuando las tablas están formadas por campos LONG y LONG RAW, que pueden almacenar hasta 2 GB por valor de columna por cada fila. En estos casos poco hay que hacer, sino considerar que la lectura de una fila implica recorrer más de un bloque.

La migración de filas sucede cuando un bloque está lleno, y un UPDATE sobre una fila la hace crecer por encima del espacio reservado en los bloques para alojar ese crecimiento dinámico. Este espacio libre va marcado por el parámetro PCTFREE, que por defecto está a un 10 % de espacio libre en el bloque. Cuando esto sucede, la cabecera de la fila se mantiene en el bloque donde se insertó, pero el contenido de esta se migra a otro bloque.

La migración de filas implica un coste considerable en la lectura. La fila no se encuentra en el bloque donde se esperaba, y por tanto es necesario una segunda lectura de otro bloque.

Imaginemos el caso de una aplicación en la que la inserción de las filas se hace con los valores mínimos (usuario y contraseña), y los datos de los usuarios se completan en una fase posterior. Suponiendo que la sentencia UPDATE llega a hacerse días después de su inserción, cuando el bloque está lleno, la propia naturaleza de la tabla está condenada a la migración de la mayoría de sus filas.

Fragmento de la Parte 3. Para seguir leyendo, consigue el libro.

A medida que las bases de datos van creciendo, a lo largo del tiempo, surge la creciente necesidad de almacenar externamente toda esta información histórica con tres propósitos, principalmente.

El primero es apartarla de los datos de acceso cotidiano para que los objetos que contienen la información actual tengan un tamaño más manejable y accesible. El segundo propósito podría ser el de archivar y clasificar la información para mantenerla en el tiempo: balances, totales, resúmenes, etc., limpios de aquella información innecesaria (por ejemplo, cambios de estado de un proceso de cobro); y el tercero, y no menos importante, el de poder explotar esta información histórica como soporte para la toma de decisiones y, si es posible, con fines predictivos (tendencias de consumo, perfiles de clientes, eficacia en tratamientos para ciertas enfermedades con procesos médicos e historiales complejos, etc.).

Estos entornos se denominan data warehouse o almacenes de datos. En ellos se acumulan tablas inmensas con millones de elementos, donde la información suele almacenarse denormalizada y donde los diseños convencionales de bases de datos en tercera forma normal (modelos de entidad-relación donde en una tabla no hay ninguna columna que corresponda a un atributo externo de la entidad) no sirven y dan paso a diseños multidimensionales como el de la Figura 21.

En este escenario, en el que la tabla COMPRAS podría contener millones de elementos, obtener un informe mediante NESTED LOOPS o búsquedas ordinarias por índices estándar es completamente inviable. Aunque aparentemente sean modelos similares, con tablas e índices, con relaciones y claves primarias y ajenas, comparar un sistema transaccional con un data warehouse sería como comparar el kárate y el sumo. Ambas son artes marciales de competición, pero la estrategia de ataque, y el “cómo procesar las transacciones” son completamente distintos.

Los entornos data warehouse tienen un escenario particular. Un pequeño número de procesos realiza un gran porcentaje de carga de datos, en muchos casos por la noche y en una atención dedicada. Son lo que se llama, comúnmente, “cargas nocturnas”, pues se realizan en los momentos de menor concurrencia de usuarios.

Toda la información diaria se copia al data warehouse y de esta se deriva una serie de resúmenes, accesibles para el usuario final, compactados en tablas o vistas materializadas con la información clasificada por dimensiones. De este modo, las cargas de datos, que en muchos casos pueden ser del orden de millones, finalizan con los totales por mes o trimestre clasificados por tipos de artículo o por zona geográfica.

Esta información resumida es minúscula comparada con las tablas originales que contienen todo el volumen histórico de las aplicaciones a lo largo de los años. Hablamos de tablas con millones de filas resumidas en totales por años/trimestres o por zonas geográficas, por ejemplo.\n\nEsa sería la disciplina ideal para el arte marcial de las cargas a un sistema data warehouse. El ataque accede a un volumen de datos muy numeroso y la optimización debe basarse en encontrar la mejor forma de hacer esa carga pesada.

En el traspaso de datos de un entorno transaccional a uno data warehouse, la información pasa por diferentes estadios y, en cada uno de ellos, la optimización de los tiempos pasa por una serie de estrategias distintas.

Fragmento de la Parte 4. Para seguir leyendo, consigue el libro.