AFINACIÓN DE UNA BD

INTRODUCCIÓN

Las bases de datos han evolucionado y con ello la gestión y afinación de las misma, esto implica que el desarrollador debe tener en cuenta parámetros que no solo optimicen las bases de datos como tal, sino que estos parámetros de configuración este predispuestos al cambio y al crecimiento, ahora bien para este proceso se consideran a los sistemas gestores de bases de datos (SGBD) cuyo principal objetivo es proporcionar una forma de almacenar y recuperar la información de manera que sea tanto practica como eficiente.

Los distintos parámetros de configuración que se tienen en cuenta y que un momento dado afectan el rendimiento son variados como distintos, pero si estos parámetros son objetivamente definidos los problemas que se generen serán factibles de identificar como de corregir.

El principal objetivo de este artículo es tomar un motor de bases de datos y analizar las posibles causas que deterioren el rendimiento de la base de datos, junto con las probables soluciones y métodos de control que se aplicarían.

Uno de los principales motores de base de datos más reconocido a nivel mundial y de los más empleados en las organizaciones es Oracle, este no solo cuenta con una estructura robusta y en constante mejora, sino que proporciona la capacidad de almacenar y acceder a estos datos de forma consecuente con un modelo definido conocido como Modelo relacional (Relational Model).

Abstract

Palabras clave

Base de datos, índices, sentencia, vista, plan de ejecución

Definición de Bases de datos

Una definición espontánea enmarcaría a una Base de Datos de ahora en adelante denominada (BD), como cualquier conjunto de datos organizados para su almacenamiento en la memoria de un ordenador, diseñado para facilitar su mantenimiento y acceso de una forma estándar

Una BD debe presentar los datos de forma que el usuario pueda interpretarlos y modificarlos.

Cabe recalcar los tres niveles principales para los procesos ejecutados por el usuario sobre la BD:

Nivel Interno: es el nivel más cercano al almacenamiento físico de los datos. Permite escribirlos tal y como están almacenados en el ordenador. En este nivel se diseñan los archivos que contienen la información, la ubicación de los mismos y su organización, es decir se crean los archivos de configuración.

Nivel conceptual: En este nivel se representan los datos que se van a utilizar sin tener en cuenta aspectos como lo que representamos en el nivel interno.

Nivel externo: es el más cercano al usuario. En este nivel se describen los datos o parte de los datos que más interesan a los usuarios.

Dichas BD comercialmente han sido manejadas en la industria de manera propietaria como libremente, por tal motivo su masificación a nivel organizacional ha permitido la creación de motores de BD que las manejan, dentro de dichos motores se encuentra uno de los más reconocidos como lo es Oracle, el cual proporciona la capacidad de almacenar y acceder a estos datos de forma consecuente con un modelo definido conocido como Modelo relacional (Relational Model). Por ello Oracle se conoce como un sistema de gestión de bases de datos relacionales. Según López (2000) La mayoría de las referencias a una BD no se refieren solo a los datos físicos, sino también a la combinación de objetos físicos, de memoria y de proceso; por tanto, es erróneo pensar que la definición de una BD se va a convertir en una simple secuencia de CREATE TABLE con su correspondiente estructura.

Ahora bien para referirse a la afinación de una BD se deben tratar los temas concernientes que un momento dado pueden afectar el rendimiento, por lo cual se plantean los siguientes aspectos los cuales serán tratados en el desarrollo del artículo:

-índices, vistas

-planes de ejecución

-parámetros de la BD: distribución y asignación de la memoria a la instancia, optimizadores de la BD

- Malas prácticas de programación

- Pruebas de carga

Índices

Un índice es una estructura de datos que permite acceder a diferentes filas de una misma tabla a través de un campo (o campos clave). Y el cual va a permitir un acceso mucho más rápido a los datos.

Para entender lo que es un índice se debe saber primero cómo se almacena la información internamente en las tablas de una BD. Cada tabla se divide en páginas de datos, imagine un libro, se puede escribir en "una sola hoja enorme" al estilo pergamino egipcio, o bien en páginas a las que se pude acceder rápidamente a través de un índice. Está idea es la que se aplica en el mundo de las BD, la información está guardada en una tabla (el libro) que tiene muchas hojas de datos (las páginas del libro), con un índice con el cual se puede buscar la información que importa.

Si se quiere buscar la palabra CARRO en un diccionario, ¿qué se hace?

Leer todo el diccionario hasta encontrar la palabra, entonces se habrá leído el diccionario

Se busca en el índice en que página está la letra C, y es en esa página donde se procede a buscar.

Según el análisis anterior la opción dos es la correcta, y es de esta forma como se utiliza un índice en las BD, se define el índice a través de un campo (o campos) y es a partir de este punto desde donde de busca.

Los índices se actualizan automáticamente cuando se realizan operaciones de escritura en la base de datos. Este es un aspecto muy importante de cara al rendimiento de las operaciones de escritura, ya que además de escribir los datos en la tabla se escribirán también en el índice. Un número elevado de índices hará más lentas estas operaciones. Sin embargo, salvo casos excepcionales, el beneficio que aportan los índices compensa (de largo) esta penalización.

Hay dos tipos básicos de índices:

Índices ordenados. Estos índices están determinados en una disposición ordenada de los valores.

Índices asociativos (hash índices). Estos índices están basados en una distribución uniforme de los valores a través de una serie de compartimientos (buckets). El valor asignado a cada compartimiento está determinado por una función, llamada función de asociación (hash function). Silberschatz., Korth., Sudarshan(2002)

Figura No 1. Tipos de índices

Se consideran varias técnicas de indexación y asociación. Ninguna de ellas es la mejor. Aun así, cada técnica es la más apropiada para una aplicación específica de BD. Cada técnica debe ser evaluada según los siguientes criterios:

Tipos de acceso. Los tipos de acceso que se soportan eficazmente. Pueden incluir búsquedas concretas por valores de un registro, o buscar dentro de un rango dado.

Tiempos de acceso. El tiempo en que se tarda en buscar un determinado elemento de datos, o conjunto de elementos, determinado por el uso de alguna técnica.

Tiempo de inserción. El tiempo empleado en insertar un nuevo elemento de datos. En este tiempo se incluye el tiempo empleado en buscar el lugar especifico donde insertar el nuevo elemento de datos, así como el tiempo para actualizar la estructura del índice.

Tiempo de borrado: el tiempo empleado en borrar un elemento de datos. Este valor incluye el tiempo utilizado en buscar un elemento a borrar, así como el tiempo empleado en actualizar la estructura del índice.

Espacio adicional requerido: el espacio adicional ocupado por la estructura del índice.

El desarrollador debe determinar no sólo qué tipo de índice es el apropiado, sino evaluar los criterios a evaluar anteriormente mencionados, esto depende en gran parte del tipo, el tamaño, los procesos, las llamadas que se le realizan a la BD.

En segunda medida se tiene que hacer referencia a Vistas, entendido por vista como el nivel más alto de abstracción, donde para el usuario no es importante toda la información de la BD, sino que tales usuarios requieren acceder solo a una parte de la BD.

Vistas

Una vista de base de datos es un resultado de una consulta SQL de una o varias tablas; también se le puede considerar una tabla virtual.

Las vistas tienen la misma estructura que una tabla: filas y columnas. La única diferencia es que sólo se almacena de ellas la definición, no los datos. Los datos que se recuperan mediante una consulta a una vista se presentarán igual que los de una tabla. De hecho, si no se sabe que se está trabajando con una vista, nada hace suponer que es así. Al igual que como sucede con una tabla, se pueden insertar, actualizar, borrar y seleccionar datos en una vista. Aunque siempre es posible seleccionar datos de una vista, en algunas condiciones existen restricciones para realizar el resto de las operaciones sobre vistas.

Una vista se especifica a través de una expresión de consulta (una sentencia SELECT) que la calcula y que puede realizarse sobre una o más tablas. Sobre un conjunto de tablas relacionales se puede trabajar con un número cualquiera de vistas.

La vistas se definen utilizando la instrucción create view, para definir una vista hay que darle un nombre e indicar la consulta que se va a calcular. La forma de la instrucción create view es; create view v as<expresión de consulta>, donde expresión de consulta es cualquier expresión legal de consulta del algebra relacional.

Como ejemplo se considera la vista Facultad y sus programas. Ahora bien esta vista se denominara programas y se define la instrucción de la siguiente forma:

Create view programas as

Πnombre_faculta, nombre_programa(faculta x programa)

Una vez definida la vista se puede emplear el nombre de la vista para hacer referencia a la relación virtual que genera la vista. Utilizando la vista Programas para averiguar el nombre de los programas asociados a un la facultad de ingeniería escribiendo

Πnombre_programa(σnombre_faculta=<<ingeniería>>(programas))

Ahora bien, si la relación de vista se calcula y se guarda, puede quedar divergida si las relaciones utilizadas para definirlas se modifican. Como medida, se implementan las vistas de otra forma, así. Cuando se define una vista, el sistema de la base de datos guarda la definición de su propia vista, en lugar del resultado de la evaluación de la expresión del algebra relacional que la define. Ahora siempre que se utiliza una relación de vistas en una consulta, se sustituye por la expresión de la consulta guardada. Por lo cual, siempre que se llama la consulta la vista vuelve a calcularse.

Ahora bien, ya se ha hablado de índice, vista los cuales implican la ejecución de sentencias SQL. De acuerdo con Niño (2010) El rendimiento de las sentencias SQL depende en gran medida de las estadísticas que el optimizador tenga. Estas son usadas para la generación de apropiados planes de ejecución.

Planes de Ejecución

Cada vez que ejecutamos una sentencia una de las cosas que hace oracle es crear un plan de ejecución de la sentencia. Un plan de ejecución define la forma

en que Oracle busca o graba los datos. Decide, por ejemplo, si va a usar o no los índices en una sentencia SELECT. (ORACLE DBA)

El proceso por el que el gestor decide que plan de ejecución ha de seguir para

ejecutar una determinada sentencia depende de varios factores. Entre los más

importantes están los parámetros de configuración de la Base de Datos, por lo que

es importante asegurar que estén igual en Desarrollo y Producción. Por defecto se

suele indicar que se utilice el método CHOOSE, o sea, que se deja que sea el

propio software de Oracle el que decida como ejecutar las sentencias. No

obstante, este comportamiento se puede variar en cualquier momento, para

cualquier sentencia SQL individual, mediante el uso de HINTS, que no son más

que palabras reservadas que se escriben dentro de las propias sentencias a modo

de metadatos para instruir al gestor según nuestra conveniencia. Por ejemplo, se

puede escribir "SELECT /*+ INDEX(TBL IDX1)*/ ... FROM ..." para forzar que se

utilice el índice IDX1 sobre la tabla TBL en vez de dejar que sea el gestor que

decida que tipo de acceso a la tabla es el mejor.

Oracle facilita una forma de conocer el plan de ejecución de una sentencia SQL

mediante el comando “EXPLAIN PLAN FOR sentencia_sql;” con el que se

almacenan en una tabla, llamada normalmente EXPLAIN_PLAN, los distintos

pasos que seguirá el gestor para evaluar la sentencia dada, así como el coste

asociado a cada uno de esos pasos. El comando admite varios parámetros más

que resultan interesantes de investigar y que están lo suficientemente

documentado en la propia documentación de Oracle, amén de existir múltiples

ejemplos repartidos por toda la red.

Pero sin lugar a dudas uno de los factores más importantes de los que hace uso

Oracle para decidir qué plan de ejecución seguir son las estadísticas que

almacena asociadas a distintos objetos como las tablas o índices. Las estadísticas

almacenan cosas tales como el número de registros asociados a cada objeto, y

son clave para determinar como acceder a ellos. Para una tabla con muy pocos

registros puede resultar ventajoso en términos de rendimiento cargarla entera en

memoria y recorrerla por completo, mientras que para una tabla con muchos

registros puede ser mejor buscar un índice adecuado en función de las columnas

utilizadas para la consulta que se está realizando.

Oracle suministra el comando ANALYZE para forzar el cálculo de las estadísticas

sobre un objeto. Por ejemplo, con la sentencia "ANALYZE TABLE TBL COMPUTE

STATISTICS;" se estaría indicando que se calculen las estadísticas sobre el

contenido completo de la tabla TBL. Otra posibilidad es calcularlas sólo sobre

parte de la información con una sentencia similar a la siguiente: "ANALYZE TABLE

TBL ESTIMATE STATISTICS SAMPLE 33 PERCENT;". E incluso podemos borrar

las estadísticas para que no haya ningún información de referencia sobre el objeto

con la sentencia "ANALYZE TABLE TBL DELETE STATISTICS;".

Otro hecho notable del gestor de Oracle es que permite copiar las estadísticas de

una Base de Datos a otra, independientemente del número y la distribución de

registros que tengan unas tablas en una Base de Datos u otra. De hecho, es

recomendable que cada cierto tiempo se copien las estadísticas del entorno de

Producción al de Desarrollo para tener los dos entornos lo más similares posible.

El optimizador de consultas es el componente del sistema de gestión de base de datos que intenta determinar la forma más eficiente de ejecutar una consulta SQL, es decir, cual es, de los posibles planes de ejecución para una consulta dada, el más eficiente. Los optimizadores basados en costo asignan un costo (que intenta estimar el costo de la consulta en términos de operaciones de entrada-salida requeridas, requerimientos de CPU y otros factores) a cada uno de esos planes, y elige el que tiene menor costo. El conjunto de planes de ejecución se forma examinando los posibles caminos de acceso (mediante índices o secuenciales), algoritmos de “join” (sort-merge join, hash join, bucles anidados). El optimizador no puede ser accedido directamente por los usuarios, sino que, una vez enviadas las consultas al servidor, pasan primero por el analizador y recién entonces llegan al optimizador.

Para optimizar sentencias SQL, especialmente consultas, lo primero que se debe hacer es determinar los pasos que sigue el optimizador cuando las resuelve. El comando EXPLAIN PLAN de Oracle permite ver el plan de ejecución de cualquier sentencia y como usarlo, una lista de las operaciones mostradas en los resultados del EXPLAIN PLAN y distintos métodos para modificar el plan de ejecución escogido por Oracle para la sentencia analizada.

CÓMO GENERAR EL EXPLAIN PLAN DE UNA CONSULTA

Se puede conocer el plan de ejecución escogido por Oracle para una consulta sin necesidad de lanzarla, para ello debe usarse el comando de Oracle EXPLAIN PLAN. EXPLAIN PLAN evalúa las distintas etapas del plan de ejecución de la consulta e inserta una fila para cada etapa en la tabla PLAN_TABLE. Los registros de la tabla describen las operaciones utilizadas en las distintas etapas y la relación entre la ejecución de cada una de ellas. Si se está utilizando el optimizador en modo costes (CBO) el EXPLAIN PLAN mostrará el “coste” relativo de cada etapa en el plan de ejecución.

Antes de poder utilizar el EXPLAIN PLAN se necesita crear la tabla PLAN_TABLE. Oracle proporciona un script para crear dicha tabla, el UTLXPLAN.SQL que suele encontrarse en el directorio (ORACLE_HOME)/rdbms/admin. Se puede lanzar desde el SQL*Plus como se muestra en el siguiente ejemplo:

     > sqlplus hobbes/tiger

     SQL> @$ORACLE_HOME/rdbms/admin/utlxplan

     Table created.

Conclusiones

Para las organizaciones donde se emplean cualquier tipo de base de datos, es fundamental que lo procesos que allí se desarrollan se ejecuten en las mejores condiciones de optimización, y para ello que se emplean los SGBD; los cuales cuentan con un modelo de ejecución que garanticen la ejecución de estos procesos.

Uno de los principales motores de base de datos más reconocido a nivel mundial y de los más empleados en las organizaciones es Oracle, este no solo cuenta con una estructura robusta y en constante mejora, sino que proporciona la capacidad de almacenar y acceder a estos datos de forma consecuente con un modelo definido conocido como Modelo relacional (Relational Model).

El administrador de la base de datos desempeña un papel fundamental y ocupa un lugar de vital importancia en la organización, dedican el máximo de sus capacidades y pleno conocimiento, que le brinda las habilidades y competencias necesarias para diseñar, crear, gestionar, proteger y ajustar una Base de Datos.

REFERENCIAS BIBLIOGRÁFICAS

(2000)López Pastor Oscar, Blesa pons Pedro. GESTIÓN DE BASES DE DATOS. España. Universidad Politecnica de Valencia. Pag 85-86. ISBN.:980-244-447-2

(2002) Silberschatz, Abraham. Korth, Henry F. Sudarshan S. FUNDAMENTOS DE BASES DE DATOS. Cuarta Edición. McGraw-Hill. Pág. 283. ISBN 84-481-3654-3.

Niño, Guillermo. (2010). Mantenimiento de la Base. Mantenimiento de la Base Oracle. Recuperado el 12, 02, 2011 en http://oraclehelper.wordpress.com/2010/03/19/mantenimiento-de-la-base-de-datos-oracle/