Cambios de rendimiento en una sentencia SQL al activar el trazado

Hace unos días un lector del blog me enviaba un email contándome un "extraño" problema de rendimiento que tenía con una sentencia SQL. Dicha sentencia SQL tardaba mucho tiempo en devolver resultados y, tras activar la utilidad de trazado SQL (SQL_TRACE=TRUE), el problema desaparecía y la respuesta de la sentencia SQL era inmediata.

La verdad es que el fenómeno no es tan extraño una vez que se conoce la causa. Cuando se activa el trazado haciendo SQL_TRACE=TRUE, lo que ocurre es que la sesión Oracle utiliza una nueva área de SQL compartido. Esto supone que el parsing (ver artículo sobre las fases durante el procesamiento de una sentencia SQL) de cualquier sentencia SQL que se ejecute después de activar el trazado vuelva a tener lugar o que, de existir una versión de dicha sentencia SQL ya parseada en la nueva área de SQL compartido, dicha versión no coincida con la versión existente cuando el trazado no está activo. Esto causa que, cuando la sentencia SQL utiliza variables (bind variables), puesto que los valores reales de dichas variables son tomados en el momento del parsing, muy probablemente, los planes de ejecución de la misma sentencia SQL sean diferentes antes y después de activar el trazado al haberse generado utilizando valores de variable distintos.

Veamos un ejemplo:

SQL> CREATE TABLE pepe AS
 2   SELECT CASE WHEN rownum = 1 THEN 1
 3   ELSE 0 END pepe_id, all_tab_columns.*
 4   FROM all_tab_columns
 5   /
Table created.

SQL> CREATE INDEX pepe_idx ON pepe(pepe_id);
Index created.

SQL> BEGIN
 2   DBMS_STATS.gather_table_stats (
 3   user, 'T', method_opt=>
 4   'for all'||
 5   'indexed columns'||
 6   'size 254' );
 7   END;
 8   /
PL/SQL procedure successfully completed.

De esta manera habremos generado la tabla pepe que contendrá un registro con la columna pepe_id=1, mientras que para el resto de registros (digamos que alrededor de 75.000), dicha columna tomará el valor de 0.

Resulta pues evidente que, si utilizamos el optimizador basado en costes, al haber generado los correspondientes histogramas con el procedimiento PL/SQL gather_table_stats, los planes de ejecución serán muy diferentes si ejecutamos SELECT * FROM pepe WHERE pepe_id=1, sentencia SQL para la cual el optimizador elegirá utilizar el índice pepe_idx, o si, por contra, ejecutamos SELECT * FROM pepe WHERE pepe_id=0, sentencia para la cual el optimizador elegirá realizar un full scan de la tabla.

Pero que ocurriría si utilizamos una variable a la hora de ejecutar la sentencia SQL, es decir, si ejecutamos SELECT * FROM pepe WHERE pepe_id=:id. En este caso la respuesta varía dependiendo de cual es la versión de la base de datos Oracle que estemos utilizando:

Base de datos Oracle 8i Release 3 y anteriores

En este caso el optimizador dispone de la siguiente información, la columna pepe_id puede tomar dos valores (1 y 0) y, además, la tabla tiene unos 75.000 registros. Debido al hecho de que la columna puede tomar sólo dos valores, el viejo optimizador de la versión 8i supondrá que para cualquier valor de la variable :id, la consulta SELECT asociada devolverá aproximadamente la mitad de los registros de la tabla. Así que, la decisión más probable del optimizador será realizar un escaneado completo (full scan) de la tabla.

Bases de datos Oracle 9i y 10g

Si disponemos de una versión de la base de datos Oracle incluida entre las versiones 9i y 10g, entonces la base de datos Oracle esperará a que se suministre el valor de la variable, antes de que el optimizador decida cual es el mejor plan de ejecución. Esto es lo que se conoce como bind variable peeking (que traducido vendría a ser algo como echar un vistazo a la variable). Así pues, en lo que se refiere a nuestra consulta SELECT, el optimizador elegirá bien realizar un full scan (:id=0), o bien utilizar el índice pepe_idx (:id=1), dependiendo del valor de la variable :id.

Y aquí es donde nos podemos encontrar con un problema de rendimiento ya que, el plan de ejecución que el optimizador utilice por primera vez, será el plan de ejecución que se almacene en el área de SQL compartido, y dicho plan será el que se utilice para ejecutar nuestra sentencia SQL SELECT * FROM pepe WHERE pepe_id=:id, independientemente del valor que demos a la variable :id con posterioridad.

Entonces ya sabemos que es lo que le pasó al lector que inspiró este artículo:

a) Alguien ejecuto la consulta en cuestión utilizando un determinado valor para la variable :id.

b) El plan de ejecución correspondiente a dicho valor de la variable se almacenó en el área de SQL compartido.

c) Nuestro lector ejecuto la misma consulta pero utilizando otro valor para la variable :id. El optimizador reutilizó el plan de ejecución almacenado en el área de SQL compartido. Dicho plan no era el más eficiente para el nuevo valor de la variable, de hecho se trataba de un plan de ejecución bastante pobre. Como resultado la consulta tardó en responder más tiempo del esperado.

d) Posteriormente nuestro lector activó el trazado (SQL_TRACE=TRUE) y volvió a ejecutar la consulta pero, en este caso, al estar el trazado activo, el plan de ejecución no se sacó del área de SQL compartido, sino que se elaboró un nuevo plan de ejecución mucho más eficiente, dando como resultado que la sentencia SQL se ejecutase de forma rápida.

Posibles soluciones

Existen algunas soluciones que nos permitirán evitar este tipo de situaciones provocadas por el bind variable peeking:

a) No utilizar variables (bind variables). En el ejemplo que he puesto es bastante evidente que no es necesario utilizar variables, simplemente podemos utilizar constantes (valores literales) en nuestra cláusula WHERE. Esta es, sin duda, la mejor solución.

b) No generar estadísticas que puedan hacer variar los planes de ejecución. Obviamente, si no hubiéramos generado los histogramas de la tabla pepe, el optimizador siempre generaría el mismo plan de ejecución con independencia del valor de la variable. A veces esto puede ser recomendable, pero si no generamos estadísticas, entonces perderemos algunas de las ventajas derivadas de utilizar el optimizador basado en costes.

c) Cambiar el valor del parámetro CURSOR_SHARING para que en vez de FORCE sea SIMILAR. Si utilizamos el valor SIMILAR evitaremos que el bind variable peeking entre en juego.

d) Actualizar nuestra base de datos Oracle a la versión 11g, ya que esta versión está dotada de una nueva funcionalidad conocida como intelligent cursor sharing (compartición de cursores inteligente). Así pues, para nuestra consulta ejemplo, el optimizador de la versión 11g es capaz de identificar que un plan de ejecución no es suficiente para todos los valores posibles de la variable :id, permitiendo que se generen varios planes de ejecución distintos para una misma sentencia SQL.

Optimización SQL y PL/SQL - Código compartido

Cuando alguien solicita la ejecución de algún código SQL o PL/SQL, Oracle busca dicho código o sentencia en el área de SQL compartido. Si la sentencia y su correspondiente análisis sintáctico (parsing) existen en la librería caché de la base de datos, entonces Oracle reutiliza el ejecutable correspondiente. Dentro de este área de SQL compartido, cada sentencia SQL ocupa un espacio determinado conocido como área contextual o, en terminología inglesa, cursor (ojo, esto no tiene nada que ver con los cursores PL/SQL). Cada cursor localizado en el área de SQL compartido contiene la siguiente información:

- El análisis sintáctico de la sentencia SQL (ver artículo sobre las fases del procesamiento de una sentencia SQL).

- El plan de ejecución.

- La lista de objetos de la base de datos que son referenciados por la sentencia.

Si dos usuarios ejecutan la misma sentencia SQL o PL/SQL, entonces ambos utilizaran el mismo cursor. Los beneficios de los cursores compartidos son evidentes:

- Reducción del tiempo de parsing o análisis sintáctico de la sentencia SQL o PLSQL.

- Las necesidades de memoria se ajustan dinámicamente y el uso de la misma puede mejorar de forma importante.

Los cursores sólo pueden ser compartidos por sentencias SQL que tienen las siguientes características:

- Se trata de sentencias SQL idénticas.

- El texto de las sentencias SQL debe ser exactamente el mismo, incluyendo mayúsculas, espacios, tabuladores, retornos de carro y comentarios.

- Los objetos de la base de datos Oracle a los que hace referencia la sentencia SQL deben ser también idénticos. Por ejemplo, dos tablas pueden recibir el mismo nombre pero pertenecer a diferentes usuarios o esquemas y, por tanto, tratarse a nivel de base de datos de tablas diferentes.

- Los tipos de las variables usadas en la sentencia SQL deben ser iguales (ver tipos de datos en PL/SQL). No es necesario que los nombres de las variables sean idénticos, es decir, sentencias SQL como SELECT * FROM clientes WHERE cliente_id = :c y SELECT * FROM clientes WHERE cliente_id = :d pueden ser sentencias idénticas ya que las variables c: y d: son renombradas internamente (la primera variable de una sentencia SQL es siempre renombrada como :b1, la segunda como :b2 y así sucesivamente).

Con respecto a este punto debo mencionar que la mayoría de las herramientas Oracle, tales como PL/SQL, precompiladores y Oracle Developer, realizan un preprocesado de las sentencias SQL para hacerlas tan idénticas como sea posible mediante la eliminación de comentarios y de espacios innecesarios, así como convirtiendo mayúsculas y minúsculas cuando esto es posible. SQL*Plus es la excepción y envía las sentencias SQL tal y como son escritas por el usuario.

Por esta razón es muy importante que, cuando escribamos código SQL y PLSQL, creemos código lo más genérico posible mediante:

- La utilización de procedimientos, funciones y paquetes PL/SQL almacenados.

- El uso de triggers PL/SQL.

- Hacer llamadas a triggers y procedimientos almacenados en la base de datos cuando utilicemos Oracle Developer.

- Escribir librerías de rutinas y procedimientos.

También es crucial que sigamos unos estándares a la hora de escribir código SQL:

- Seguir unos estándares para todo tipo de sentencias, incluyendo las sentencias en código PLSQL.

- Desarrollar normas en cuanto al uso de mayúsculas y minúsculas.

- Desarrollar normas en cuanto a la utilización de espacios, tabuladores y retornos de carro.

- Seguir unos estándares para los comentarios. Preferiblemente los comentarios deben mantenerse fuera de las sentencias SQL o PL/SQL.

- Utilizar los mismos nombres a la hora de referirse a objetos de la base de datos idénticos. Por ejemplo, aunque incomodo puede resultar interesante preceder a los nombres de las tablas con el nombre del esquema (o usuario) al que pertenecen.

SQL y PL/SQL - La nueva sentencia MERGE

La sentencia MERGE, a la que muchos denominan UPSERT debido a su funcionalidad, está disponible desde la aparición de la versión 9i de la base de datos Oracle. Se trata de una de las funcionalidades del kernel de Oracle más utiles a la hora de permitir el uso de la tecnología ETL (Extract, Transform and Load - Extraer, Transformar y Cargar) con las bases de datos Oracle. Este tipo de tecnología puede utilizarse y está especialmente enfocada para ser usada en aplicaciones de data warehousing (almacen de datos). Básicamente, lo que permite la sentencia SQL MERGE es, dependiendo de una condición lógica, actualizar registros (UPDATE) cuando la condición se cumple, o insertar registros (INSERT) cuando dicha condición no se cumple, de ahí surge la denominación de UPSERT.

Anteriormente a la versión 9i de Oracle, la alternativa en SQL era ejecutar dos sentencias DML, un UPDATE y un INSERT, cada una utilizando condiciones lógicas opuestas. En cuanto a las alternativas en PL/SQL eran: bien intentar insertar un registro y si la sentencia INSERT fallaba debido a una excepción PLSQL del tipo DUP_VAL_ON_INDEX, entonces realizar un UPDATE del registro en cuestión; bien intentar actualizar un registro y si la sentencia UPDATE devolvía SQL%NOTFOUND, entonces ejecutar la correspondiente sentencia INSERT.

Las ventajas de la sentencia SQL MERGE son claras:

- Permite con una misma sentencia realizar un UPDATE si el registro existe, o un INSERT si se trata de un nuevo registro.

- La sentencia puede paralelizarse de forma transparente.

- Se evita la necesidad de realizar actualizaciones múltiples.

- Es especialmente útil para realizar operaciones en masa y, como ya he mencionado, en aplicaciones de data warehousing.

- El rendimiento de la base de datos mejora ya que, al necesitarse menos sentencias SQL para realizar las mismas operaciones, también se necesitan menos accesos a las tablas fuente.

Ejemplo de sentencia MERGE

En el presente ejemplo voy a partir del hecho de que tenemos dos tablas en un data warehouse, una de ellas con muchos registros denominada clientes y otra más pequeña denominada datos_cli cuyos registros deben insertarse en la tabla clientes. En pocas palabras, estoy suponiendo que la tabla datos_cli contiene los cambios a realizar sobre la tabla clientes. La sentencia SQL MERGE que debemos utilizar para actualizar la tabla cliente podría ser la siguiente:

MERGE INTO clientes cli USING datos_cli dac
  ON (cli.cliente_id = dac.cliente_id)
  WHEN MATCHED THEN
    UPDATE SET 
      cli.nombre = dac.nombre,
      cli.direccion = dac.direccion
  WHEN NOT MATCHED THEN
    INSERT (cliente_id, nombre, direccion)
    VALUES (dac.cliente_id, dac.nombre, dac.direccion);

Así pues, la sentencia MERGE del ejemplo realizará las siguientes operaciones:

- Si existe un registro en datos_cli con el mismo ID de otro registro en la tabla clientes, entoces actualizará el valor de los campos nombre y dirección de la tabla clientes con los valores correspondientes de la tabla datos_cli.

- Si no existe un registro en clientes con el ID del registro de datos_cli que está siendo procesado, entonces se insertará dicho registro en la tabla clientes.

En resumen, la sintaxis de la sentencia MERGE debe incorporar:

Una cláusula INTO, que especifica la tabla destino donde los registros serán actualizados o insertados.

Una cláusula USING, especificando el origen de los datos que van a ser insertados o que van a servir para actualizar la tabla destino. El origen de los datos puede tratarse de una tabla, una vista, o del resultado de la ejecución de una consulta SELECT.

Una cláusula ON, que especifica la condición bajo la cual se realizará, bien la operación UPDATE (si la condición se cumple), bien la operación INSERT (si la condición no se cumple).

Las cláusulas WHEN MATCHED | NOT MATCHED, que son las que indican a la base de datos Oracle que acción realizar si se cumple o no se cumple la condición del ON. Estas dos cláusulas se pueden poner en cualquier orden.

Artículos relacionados:
La sentencia INSERT multitabla de la versión 9i de Oracle.
La cláusula WITH de la versión 9i de la base de datos Oracle.

SQL y PL/SQL - La sentencia INSERT multitabla de Oracle 9i

La versión 9i de las bases de datos Oracle ha introducido la posibilidad de utilizar sentencias INSERT multitabla. Así pues, la sentencia SQL o PLSQL INSERT... SELECT ha cambiado ligeramente su sintaxis, de manera que ahora permite la inserción de datos en más de una tabla de la base de datos de forma paralela. Existen dos formas de utilizar el comando INSERT multitabla: no condicional y condicional. En la forma no condicional, una cláusula compuesta INTO se ejecuta cada vez que la consulta SELECT devuelve un registro. En la forma condicional, las cláusulas compuestas INTO figuran dentro de cláusulas WHEN a partir de las que se determina si la correspondiente cláusula compuesta INTO se ejecuta o no.

Una claúsula compuesta INTO consiste de una o más cláusulas INTO. Una cláusula INTO debe especificar la tabla de la base de datos sobre la que se van a insertar los datos. Esta cláusula no admite alias. La cláusula INTO tambien proporciona el valor del los campos a ser insertados mediante la cláusula VALUES. La expresiones usadas en la cláusula VALUE pueden tratarse de cualquier expresión permitida, pero siempre debe hacer referencia a columnas devueltas por la consulta SELECT incluida en el INSERT.

Veamos un ejemplo de la forma no condicional:

INSERT ALL
 INTO productos
 VALUES (producto_id, producto, cantidad)
 INTO ventas
 VALUES (cliente_id, producto_id, cantidad, total)
SELECT cliente_id,
      producto_id,
      producto,
      SUM(cantidad) cantidad,
      SUM(precio) total
FROM   pedidos, lineas_pedidos
WHERE  pedidos.pedido_id = lineas_pedidos.pedido_id
GROUP BY cliente_id, producto_id, producto;

En el ejemplo vemos como la sentencia INSERT se utiliza para insertar los valores producto_id, producto y cantidad en la tabla productos y los valores cliente_id, producto_id, cantidad y total en la tabla ventas, todo ello utilizando una sola sentencia SQL o PL/SQL. Resulta obvio comentar que la sentencia que he utilizado como ejemplo, podría escribirse de forma alternativa mediante el empleo de dos sentencias INSERT.

La forma condicional de las sentencias SQL o PLSQL INSERT multitabla dispone, a su vez, de dos opciones:

1) Forma condicional FIRST, sólo se insertan los valores especificados en la primera condicion verdadera.

2) Forma condicional ALL, se insertan los valores especificados en todas las condiciones verdaderas.

La sintaxis sería:

INSERT [ALL/FIRST]
WHEN  THEN
 INTO  (col, col, ...) VALUES (val, val, ...)
 .....
 INTO  (col, col, ...) VALUES (val, val, ...)
WHEN  THEN
 INTO  (col, col, ...) VALUES (val, val, ...)
 .....
 INTO  (col, col, ...) VALUES (val, val, ...)
ELSE
 INTO  (col, col, ...) VALUES (val, val, ...)
 .....
 INTO  (col, col, ...) VALUES (val, val, ...)
SELECT ...;

Ventajas de las sentencias INSERT multitabla en SQL y PL/SQL

1) Eliminan la necesidad de utilizar múltiples sentencias INSERT ... SELECT para añadir registros en varias tablas de la base de datos.

2) Elimina la necesidad de utilizar un procedimiento PL/SQL para realizar diferentes inserciones de registros dependiendo de diferentes condiciones lógicas. Esto ya se puede realizar con una única sentencia SQL del tipo INSERT ... SELECT, mediante el uso de la cláusula WHEN.

3) Mejora el rendimiento significativamente ya que la consulta SELECT correspondiente, al existir un sólo comando INSERT, sólo tienen que ejecutarse una vez, en lugar de tener que repetirse su ejecución en cada INSERT.

Artículos relacionados: La nueva cláusula WITH de la versión 9i de la base de datos Oracle.

La claúsula WITH en SQL y PL/SQL

La versión 9i de las bases de datos Oracle permite el uso de la claúsula WITH en SQL y PLSQL. Este comando permite reusar una consulta SELECT cuando esta hay que utilizarla más de una vez en una sentencia o consulta SQL compleja. Los resultados de la consulta definida en la claúsula WITH son almacenados en una tabla temporal pudiendo de esta forma mejorar el rendimiento de la sentencia principal.

Aunque no siempre conseguiremos mejorar el rendimiento utilizando la claúsula WITH, lo que sin duda facilitaremos es la lectura y el mantenimiento del código PL/SQL o SQL. Dentro de la claúsula WITH daremos un nombre a las consultas SELECT a reutilizar (WITH admite la definición de múltiples consultas con sólo separarlas por comas), dicho nombre será visible para todas las consultas definidas posteriormente dentro del mismo WITH. Obviamente, también será visible para la sentencia o consulta principal.

Uso de la claúsula WITH

En el siguiente ejemplo encontraremos todos las divisiones de una empresa cuyos empleados tienen un salario medio un 10 por ciento por encima del salario medio de la empresa:

WITH salario_division AS (
  SELECT division,
         AVG(salario) salario_medio
  FROM   empleados
  GROUP BY division )
SELECT division,
       salario_medio
FROM   salario_division
WHERE  salario_medio > (
       SELECT AVG(salario_medio) * 1.1
       FROM   salario_division )
ORDER BY salario_medio;

Como se puede observar en la consulta existen dos bloques, una consulta principal y una subconsulta. Ambas necesitan realizar operaciones agrupando datos. Reescribamos la sentencia sin utilizar la claúsula WITH:

SELECT division,
       AVG(salario) salario_medio
FROM   empleados
GROUP BY division
HAVING AVG(salario) > (
       SELECT AVG(salario) * 1.1
       FROM   empleados )
ORDER BY AVG(salario);

Comparando ambas sentencias podemos concluir que al utilizar la claúsula SQL WITH y almacenar en una tabla temporal la consulta que hace el GROUP BY, evitamos que se tengan que agrupar los datos más de una vez. Este hecho debe hacernos pensar que el rendimiento de la sentencia que utiliza la claúsula WITH debe ser mejor que el de la sentencia que no lo utiliza.

Características de la claúsula WITH

a) Sólo se puede usar en sentencias SELECT.

b) Cuando se define una consulta con el mismo nombre de una tabla existente en la base de datos Oracle, puesto que el analizador sintáctico o parser (ver fases en la ejecución de una sentencia SQL) de las sentencias SQL o PLSQL busca de dentro a fuera, el nombre dentro de la claúsula WITH tendrá prioridad frente al nombre de la tabla.

c) Puede contener más de una consulta. Cada consulta se separa mediante comas. Las consultas definidas después de otras consultas pueden utilizar las definiciones previas.

Artículos relacionados:
Vistas materializadas o materializaed views.
Cursores PL/SQL.
Tablas externas en el SQL de Oracle.

Hints avanzados en PL/SQL para forzar la forma de acceder a una tabla

Ya he hablado anteriormente acerca de los hints PLSQL más comunes (FULL, ROWID, INDEX, NO_INDEX) para forzar el método de accesso a una tabla Oracle. En este artículo voy a presentar algunos hints más de este tipo, que se utilizan menos frecuentemente, pero no por ello menos útiles.


CLUSTER (nombre_de_tabla): Fuerza el accesso a la tabla indicada utilizando un índice de tipo cluster. Los índices de este tipo se utilizan para localizar registros que comparten valores comunes de una forma rápida. La clave del índice cluster puede estar constituida por una o más columnas de la tabla. Los registros de la tabla son agrupados según la clave y almacenados físicamente juntos en el disco duro.

Es decir, suponiendo que tenemos una tabla de empleados, si definimos un índice cluster basado en el país del empleado, cuando un usuario inserta un nuevo empleado, el índice cluster asegura que el nuevo empleado se almacene en el mismo bloque de datos donde figuran los empleados correspondientes a ese país.

Nota: Pueden definirse clusters en los que se almacene más de una tabla. Obviamente la única limitación es que dicho cluster se defina sobre columnas con valores comunes.

HASH (nombre_de_tabla): Fuerza a que la tabla se combine utilizando un algoritmo de hash. Sólo aplica a los clusters tipo hash que utilizan una función hash para calcular la localización de los registros de una tabla Oracle. En este caso, los registros son almacenados en el disco duro según sea el valor devuelto por la función hash. La función se utiliza para localizar los registros sin necesidad de utilizar el tiempo de entrada/salida o de CPU que requiere la búsqueda a través de un índice normal.

La unión de tablas a través de un cluster hush es recomendable para tablas con un gran número de registros ya que, para dichas tablas, los índices pueden estar estructurados en mucho niveles, siendo el acceso al índice más lento.

HASH_AJ (nombre_de_tabla): Transforma una subquery NOT IN en una anti-unión de tipo hash.

Una anti-únión (anti-join) es una forma de unir tablas que utiliza una lógica inversa. En vez de devolver registros cuando ambas partes cumplen con una condición, una anti-unión devuelve aquellos registros de una de las partes que no están en la otra (exactamente lo que hace una subquery NOT IN).

Nota explicativa: Una subquery no es más que una sentencia SELECT que está dentro de otra sentencia SQL o PL/SQL.

El optimizador PL/SQL basado en normas (Rule-Based Optimizer)

En este artículo voy a mencionar algunas de las características del optimizador PL/SQL basado en normas (Rule-Based Optimizer). Lo primero que quiero mencionar es que Oracle recomienda utilizar el optimizador PLSQL basado en costes (cost-based optimizer), no obstante, en algunos casos, el hecho de tener que activar las estadísticas de la base de datos para poder utilizar este último optimizador, puede hacer que resulte interesante utilizar el optimizador basado en normas y dejar las estadísticas desactivadas para no afectar al rendimiento de la base de datos.

El optimizador PLSQL basado en normas utiliza siempre que puede los índices, incluso cuando las tablas son pequeñas o cuando el número de registros que devuelve la sentencia SELECT es un porcentaje elevado con respecto al número total de registros de la tabla, casos para los que es mejor realizar un escaneado total (full scan) ya que la respuesta es más rápida (mejora el rendimiento). Esto es debido a que el optimizador basado en normas no hace uso de valores estadísticos, tales como el número total de registros de una tabla.

El optimizador PL/SQL basado en normas hace uso del siguiente orden de prioridades para determinar cual va a ser la forma de acceder a las tablas y determinar finalmente cual va a ser el plan de ejecución:

Prio  Forma de acceso
   1  Single row by ROWID
   2  Single row by cluster join
   3  Single row by hash cluster key with unique or
      primary key
   4  Single row by unique or primary key
   5  Cluster join
   6  Hash cluster key
   7  Indexed cluster key
   8  Composite index
   9  Single-column index
  10  Bounded range search on indexed column
  11  Unbounded range search on indexed column
  12  Sort-merge join
  13  MAX or MIN of indexed column
  14  ORDER BY on indexed column
  15  Full table scan

Los distintos métodos de acceso los he dejado en inglés, ya que es bastante complicado traducir esta terminología. En el presente artículo no voy a explicar cuales son las diferencias existentes entre las distintas formas de acceso. No obstante, en sucesivos artículos pondré algunos ejemplos que permitirán diferenciar estos conceptos.

Siguiendo con el tema que concierne a este post, el optimizador basado en normas analiza la sintaxis de la sentencia SQL para establecer los distintos métodos de acceso a las tablas. Básicamente lo que hace es determinar todas las formas de acceso posibles y escoger aquella que tiene una prioridad menor.

Este esquema siempre asume que un escaneado total (full scan) es el peor método de accesso (prioridad 15). Sin embargo, ya he mencionado al principio del artículo que esto no siempre es verdad.

Estos métodos de acceso, así como otros adicionales, están también disponibles para el optimizador PL/SQL basado en costes. Sin embargo, este optimizador ignora las prioridades, y determina el coste esperado de ejecución de la sentencia SQL para cada uno de las formas de acceso posibles basándose en las estadísticas, escogiendo después aquella forma de acceso con el menor coste estimado. Muchas funcionalidades del Oracle, como los hash joins, star queries e histogramas, sólo están disponibles para el optimizador PLSQL basado en costes.

Artículos relacionados:
- Hints PLSQL para determinar el modo de optimización.
- Hints PL/SQL para forzar la forma de accesso.

Análisis de la salida del comando TKPROF en PL/SQL y SQL

Ya hemos hablado en otro artículo acerca de las opciones y parámetros del comando TKPROF y de su uso para analizar los fichero de trazado PLSQL. Ahora es el momento de escribir acerca de cómo interpretar la salida de dicho comando. La salida del comando TKPROF muestra las estadíticas resultantes de la ejecución de una sentencia SQL o PLSQL agrupadas por fases de procesamiento. En el artículo "Pasos seguidos durante la ejecución de una sentencia SQL", ya hice una exposición detallada de lo que acontece en cada una de estas fases, no obstante, a continuación, voy a realizar un pequeño resumen.

Fases de procesamiento analizadas por el comando TKPROF

Fase de parsing (parse): Durante este paso se genera el plan de ejecución de la sentencia SQL o PL/SQL, se comprueban los permisos de ejecución y se verifica la existencia de los objetos referenciados en dicha sentencia (tablas, vistas, columnas, etcétera).

El parsing puede ser duro (hard) o blando (soft). El parsing duro se realiza cuando se genera el plan de ejecución, incluyendo la optimización del mismo, y dicho plan de ejecución se almacena en la librería cache. El parsing blando hace referencia a que cuando la sentencia SQL o PLSQL es enviada al kernel para generar el plan de ejecución, el kernel encuentra dicho plan en la librería cache, por lo que no es necesario generar el plan de ejecución, y sólo se necesitan verificar los permisos de acceso a los objetos Oracle utilizados en la sentencia.

Fase de ejecución (execute): Este paso se corresponde con la ejecución propiamente dicha de la sentencia SQL o PLSQL por parte del servidor de la base de datos Oracle. Para las sentencias INSERT, UPDATE y DELETE, este paso implica la modificación de los datos y, a veces, su ordenación. Para la sentencia SELECT, esta fase supone la identificación de los registros seleccionados.

Fase de fetching (fetch): Durante este paso el servidor Oracle devuelve los registros resultantes de la ejecución de una sentencia SELECT y, si es necesario, los ordena. Esta fase sólo afecta a las sentencias SELECT.

Columnas del informe

Las estadísticas entregadas por el comando TKPROF se organizan en siete categorías o columnas:

Count: Número de veces que una sentencia SQL o PL/SQL fue analizada según fases. A la hora de interpretar los resultados que aparecen en las otras columnas del informe generado por el comando TKPROF, es importante comprobar si esta columna toma valores mayores que uno, ya que si hemos utilizado la opción AGGREGATE=YES, el comando TKPROF agrupará la ejecución de todas las sentencias SQL o PLSQL idénticas en una sola tabla de resultados.

CPU: Tiempo total en segundos de uso de CPU según fases.

Elapsed: Lapso de tiempo total en segundos según fases.

Disk: Número de bloques de datos físicamente leídos desde disco según fases.

Query: Número de bloques de memoria intermedia (buffers) leídos en modo consistente. Las lecturas consistentes se producen generalmente para las sentencias SELECT. Una lectura consistente indica que los registros leídos que han sido modificados y que no han sido confirmados mediante el comando COMMIT, tienen que reconstruirse a partir de los segmentos de rollback.

Current: Número de bloques de memoria intermedia (buffers) leídos en modo normal (current). Las lecturas en modo normal se producen generalmente para las sentencias DML (sentencias de manipulación de datos).

Rows: Número de registros procesados por la sentencia SQL o PL/SQL. Este valor no incluye los registros procesados por las subqueries (sentencias SELECT incluídas en otras sentencias SQL o PLSQL). Para las sentencias SELECT, el número de registros devueltos se mostrará en la fase de fetching. Para las sentencias INSERT, UPDATE y DELETE, el número de registros procesados se mostrará en la fase de ejecución.

Ejemplo de salida resultado de la ejecución del comando TKPROF:

select categoria, trabajo, nombre, salario
from empleados, salarios
where salario between infsal and supsal
order by categoria, trabajo

call    count   cpu elapsed disk query current rows
------- -----  ---- ------- ---- ----- ------- ----
Parse       1  0.06    0.08    2    18       1    0
Execute     1  0.00    0.00    0     0       0    0
Fetch       1  0.01    0.01    1    11      12   14
------- -----  ---- ------- ---- ----- ------- ----
total       3  0.07    0.09    3    29      13   14

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 8 (SCOTT)

Rows     Execution Plan
-------  -------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
     14   SORT (ORDER BY)
     14    NESTED LOOPS
      5     TABLE ACCESS (FULL) OF 'SALARIOS'
     70     TABLE ACCESS (FULL) OF 'EMPLEADOS'

Obviamente, si el fichero de trazado que estamos analizando con el comando TKPROF, contiene información sobre la ejecución de varias sentencias SQL o PL/SQL, la salida mostrará la información arriba indicada para cada una de las sentencias SQL que han sido analizadas.

Uso del comando TKPROF para formatear los ficheros de trazado PL/SQL

Ya hablamos en un articulo anterior acerca de como activar y desactivar la funcionalidad de trazado SQL o PLSQL. En este artículo voy a escribir acerca de como utilizar el comando del sistema operativo TKPROF, cuyo cometido es formatear los ficheros binarios generados mientras la traza SQL esta activa, de manera que éstos sean legibles. La sintaxis del comando es como sigue:

UNIX> tkprof fichero_trazado fichero_salida [opciones]

Cuando el comando TKPROF es ejecutado sin utilizar ningún argumento, la salida del comando muestra un mensaje de ayuda junto con la descripción de todas las opciones del comando disponibles.

Opciones del comando TKPROF

Sort=opción - Ordena las sentencias SQL de la traza en base a distintos criterios. Los criterios de ordenación más útiles a mí entender son:

    - prscpu: Ordenado según el tiempo de CPU.
    - prsela: Ordenado según el tiempo de ejecución.

Print=n - El informe mostrará únicamente las n primeras sentencias SQL o PL/SQL. Esta opción es especialmente útil si se utiliza conjuntamente con las opción sort.

Explain=usuario/password - Se conecta a la base de datos utilizando dicho usuario y genera los planes de ejecución (EXPLAIN PLAN) de las sentencias SQL o PL/SQL en el esquema de la base de datos especificado.

Insert=nombre_fichero - Crea un script SQL para cargar los resultados del comando TKPROF dentro de una tabla de la base de datos Oracle.

Sys=no - El informe no muestra las sentencias SQL ejecutadas por el usuario SYS. Esta opción es bastante útil ya que elimina todas sentencias SQL internas. Estas sentencias, al ejecutarlas internamente el sistema de la base de datos Oracle, no pueden ser optimizadas y no es necesario, por lo general, tenerlas en consideración.

Aggregate=no - No acumula los resultados de las sentencias SQL idénticas en un solo registro.

Record=nombre_fichero - Crea un script SQL con todas las sentencias SQL no recursivas encontradas en el fichero de trazado. Este script se puede utilizar posteriormente para repetir la sesión de optimización o para modificar y optimizar las sentencias SQL sobre dicho script y comprobar que el rendimiento mejora al ejecutarlo tras la modificación.

Table=esquema.nombre_tabla - Especifica el esquema y el nombre de la tabla donde se almacenará temporalmente el plan de ejecución antes de que se genere el fichero de salida. Este parámetro es ignorado si no se utiliza la opción explain. Puede ser útil cuando simultáneamente varios usuarios utilizan el comando TKPROF para optimizar el mismo esquema de la base de datos.

Ya he hablado acerca de como ejecutar el comando TKPROF, ahora queda la ardua tarea de escribir acerca de como interpretar la salida de dicho comando, pero esto será objeto de otro artículo en esta bitácora.

Como avance mencionaré algunas circunstancias que nos pueden llevar a malinterpretar la salida del comando TKPROF:

Problemas con el número de lecturas consistentes: Si en el momento de la ejecución de una sentencia SQL o PLSQL hay transacciones no confirmadas (uncommitted transactions) sobre la tabla utilizada, entonces el número de bloques leídos aumentará ya que la base de datos Oracle necesitará construir y leer bloques adicionales para mantener la integridad.

Interpretación del tiempo de ejecución: Si una sentencia DML (sentencias de manipulación de datos) muestra un tiempo de ejecución elevado, puede ocurrir que esto sea debido a que otra transacción esté bloqueando la tabla involucrada. Por esta razón, el tiempo de CPU es un mejor indicador que el tiempo de ejecución.

Diferencias en el tiempo: Si el plan de ejecución de la sentencia SQL analizada indica que la tabla en cuestión es accedida a través de un índice, pero las estadísticas del comando TKPROF muestran un número de bloques leídos muy elevado y, además, los valores para la columna current del informe no son cero, entonces probablemente lo que haya ocurrido es que la tabla fue realmente accedida realizando un escaneado completo (full scan). Este tipo de situaciones se pueden dar cuando la generación del fichero de trazado haya sido anterior a la creación del índice o, si estamos utilizando el optimizador basado en costes, a la regeneración de las estadísticas asociadas con la tabla involucrada.

La existencia de triggers: Los valores mostrados por el comando TKPROF a la hora de analizar una sentencia SQL incluyen todas las sub-sentencias SQL ejecutadas desde la sentencia principal. Es decir, si una sentencia SQL dispara un trigger, los recursos utilizados por dicho trigger serán incluidos en el informe final acerca de la sentencia principal.

Vistas materializadas (materialized views) en SQL y PL/SQL (1)

El SQL de las bases de datos Oracle permite crear vistas materializadas o materialized views. Estas vistas materializadas, a parte de almacenar la definición de la vista propiamente dicha, también almacenan los registros que resultan de la ejecución de la sentencia SELECT que define la vista. Como las vistas normales, la sentencia SELECT es la base de la vista, pero la sentencia SQL se ejecuta cuando se crea la vista y los resultados se almacenan físicamente constituyendo una tabla real que ocupa sitio en el disco duro. Esta tabla puede definirse utilizando los mismos parámetros de almacenamiento que se pueden utilizar para una tabla normal (tablespace, etcétera). Las vistas materializadas también admiten índices, esta funcionalidad resulta muy útil a la hora de mejorar el rendimiento de las sentencias PLSQL o SQL que utilicen vistas materializadas.

Cuando una sentencia SQL o PL/SQL accede a una vista materializada el servidor de la base de datos Oracle, transforma la sentencia dirigiéndose directamente a los datos de la vista que están ya almacenados, en lugar de utilizar los datos de las diferentes tablas utilizadas en la definición de dicha vista.

Evidentemente, si una vista (view) utiliza muchas tablas base enlazadas de forma compleja, y dicha vista va a ser utilizada frecuentemente, será muy conveniente definirla como una vista materializada o materialized view. Esto contribuirá enormemente a mejorar el rendimiento de la base de datos, ya que la sentencia SQL base de la vista sólo se ejecutará una vez.

Por otro lado, está el inconveniente de que si la vista materializada o materialized view va a tener que reutilizarse en el futuro, entonces necesitaremos un mecanismo para actualizar o refrescar dicha vista materializada, ya que las tablas base de la vista pueden haber sufrido modificaciones desde la creación de la misma.

Por todo esto, a la hora de determinar si una vista debe definirse como vista o es mejor definirla como vista materializada, debemos valorar los costes de tener que ejecutar la sentencia SQL base de una vista normal siempre que se acceda a dicha vista, frente a los costes de almacenamiento y actualización de una vista materializada.

Sintaxis del comando SQL utilizado para crear vistas materializadas

CREATE MATERIALIZED VIEW nombre_vistam
 [TABLESPACE nombre_ts]
 [PARALELL (DEGREE n)]
 [BUILD {INMEDIATE|DEFERRED}]
 [REFRESH {FAST|COMPLETE|FORCE|NEVER|ON COMMIT}]
 [{ENABLE|DISABLE} QUERY REWRITE]
AS SELECT ... FROM ... WHERE ...

Los valores por defecto de las distintas opciones están subrayados.

Si se elige la opción BUILD INMEDIATE, entonces la tabla asociada con la vista materializada se puebla con datos en el momento de la ejecución del comando SQL CREATE. Por el contrario, si se utiliza BUILD DEFERRED, el comando CREATE creará sólo la estructura de la vista, pero la tabla física asociada no se poblará con datos hasta que se realice el primer refresco o actualización de la vista materializada.

La opción REFRESH permite indicar el mecanismo que la base de datos utilizará para refrescar o actualizar la vista materializada. Los diferentes mecanismos y la forma en que una vista materializada o materialized view puede refrescarse, serán objeto de otro artículo en este blog. Como anticipo diré que un refresco completo o COMPLETE, significa que la tabla asociada con la vista materializada se borra completamente, volviéndose a insertar todos los registros devueltos por la ejecución de la sentencia SQL base de la vista, y que un refresco rápido o FAST, significa que la vista materializada se actualiza sólo según hayan sido los cambios realizados sobre las tablas base de la vista desde el último refresco. Para poder utilizar el refresco rápido o FAST, hay que crear previamente los logs de la vista materializada utilizando el comando CREATE MATERIALIZED VIEW LOG.

La opción ENABLE/DISABLE QUERY REWRITE determina si el optimizador Oracle puede o no reescribir las sentencias SQL de manera que, de ser posible, en la fase de ejecución se utilice la vista materializada en lugar de las tablas base de la vista incluidas en la sentencia SQL original. Este es un tema ciertamente complejo y que será objeto de un artículo completo en este blog. Como anticipo indicaré que la reescritura de sentencias SQL sólo está disponible cuando se utiliza el optimizador Oracle basado en costes.

Artículos relacionados: El refresco de las vistas materializadas o materialized views en PLSQL.

Cómo usar la utilidad de trazado del SQL de Oracle

La utilidad de trazado del SQL de las bases de datos Oracle nos permite analizar el rendimiento de un determinado programa PL/SQL. Esta funcionalidad nos va a permitir obtener información acerca del rendimiento de todas las sentencias SQL que se ejecuten durante la ejecución del programa PLSQL.

Para utilizar la herramienta de trazado del PL/SQL de Oracle deberemos seguir cinco pasos:

1) Inicializar los parámetros relativos a esta funcionalidad SQL.
2) Activar la traza SQL.
3) Ejecutar la aplicación que queremos analizar y desactivar la traza cuando termine.
4) Formatear el fichero producido por la traza SQL con el comando TKPROF.
5) Interpretar la salida del comando TKPROF y, si es necesario, optimizar nuestro programa PLSQL.

En este artículo voy a hablar sobre los tres primeros apartados.

Inicialización de los parámetros de trazado Oracle

La utilidad de trazado puede, opcionalmente, proporcionar información acerca de los tiempos de ejecución. Para que esta información quede almacenada es necesario activar el parámetro TIMED_STATISTICS.

Dicho parámetro se puede activar a nivel de base de datos mediante su inclusión en el fichero de parámetros de la base de datos Oracle (nota: una vez incluido el parámetro es necesario reinicializar la base de datos para que el cambio tenga efecto):

TIMED_STATISTICS = TRUE

Este parámetro también se puede asignar dinámicamente a nivel de sesión ejecutando el siguiente comando:

SQL> ALTER SESSION SET timed_statistics=true;

La activación de este parámetro puede afectar ligeramente al rendimiento de la base de datos por lo que normalmente este parámetro está desactivado

Existen otros dos parámetros que nos permiten controlar el tamaño y el nombre del directorio donde se generará el fichero de trazado:

MAX_DUMP_FILE_SIZE = n

USER_DUMP_DEST = nombre_directorio

El valor por defecto del parámetro MAX_DUMP_FILE_SIZE es 500, es decir, que nuestro fichero de trazado podrá ocupar 500 bloques del disco duro. Este parámetro puede cambiar también a nivel de sesión con el comando ALTER SESSION.

El valor por defecto del parámetro USER_DUMP_DEST depende del sistema operativo y no puede ser cambiado a nivel de sesión. Por lo tanto, al ser un parámetro global del sistema, su valor sólo pueden cambiarlo los administradores de la base de datos utilizando el comando ALTER SYSTEM.

Para obtener información acerca de los valores que toman los distintos parámetros podemos ejecutar la siguiente sentencia:

SELECT name, value
FROM v$parameter
WHERE name LIKE ‘%dump%’

Obviamente se necesita tener acceso a la vista (view) V$PARAMETER para poder visualizar esta información.

Activación de la traza SQL

Podemos activar la traza a nivel de sistema mediante la inclusión en el fichero de parámetros de la siguiente línea:

SQL_TRACE = TRUE

Esta posibilidad es poco recomendable y no se debe implementar en ningún sistema en producción. La activación de la traza a nivel de sistema puede afectar seriamente el rendimiento de la base de datos Oracle.

Por ello es mucho más recomendable activar la traza a nivel de sesión con el comando:

SQL> ALTER SESSION SET sql_trace = true;

También se puede utilizar el paquete estándar DBMS_SESSION. Esta posibilidad es particularmente útil si queremos activar y desactivar la traza dentro del un procedimiento o función PL/SQL.

SQL> EXECUTE dbms_session.set_sql_trace (true);

Por otro lado, los DBA (Database Administrators) pueden activar la traza sobre una sesión de usuario concreta utilizando el siguiente comando:

SQL> EXECUTE dbms_system.set_sql_trace_in_session (session_id, serial_id, true);

Los valores session_id y serial_id adecuados los tiene que identificar el DBA mediante el análisis de los registros incluidos en la vista V$SESSION, los campos de esta vista que se corresponden con estos valores son SID y SERIAL#.

Desactivación de la traza SQL

Cuando la ejecución del programa que estamos optimizando termina, debemos proceder a desactivar la traza utilizando cualquiera de los métodos mencionados anteriormente, sustituyendo la palabra TRUE por FALSE.

Si la traza se activó a nivel de sesión, entonces cuando la sesión termina, la traza se desactiva automáticamente.

Identificación del fichero de trazado

Para identificar los ficheros de trazado debemos ir al directorio especificado por el parámetro USER_DUMP_DEST y, normalmente, el fichero de trazado será aquel que se ha generado más recientemente.

La identificación puede complicarse cuando hay varios usuarios generando ficheros de trazado al mismo tiempo. En este caso podemos utilizar un script estándar denominado readtrace.sql. Este script crea un procedimiento que abre nuestro fichero de trazado utilizando el paquete UTL_FILE. El nombre por defecto del fichero de trazado que se genera es username.trc pero puede cambiarse fácilmente.

SQL> @readtrace.sql
SQL> ALTER SESSION SET sql_trace = true;
SQL> SELECT * FROM nombre_tabla;
SQL> execute gettrace (‘nombre_fichero_trazado’);

El siguiente paso sería utilizar el programa TKPROF para poder interpretar el contenido binario del fichero de trazado, pero esto será objeto de otro artículo.

Artículos relacionados: Obtención del plan de ejecución de una sentencia SQL o PLSQL.

Cómo obtener el plan de ejecución de una sentencia SQL o PL/SQL

Una de las formas más usuales de mejorar el rendimiento de una sentencia SQL o PL/SQL es analizar el plan de ejecución que devuelve el optimizador Oracle. En SQL*Plus se puede obtener dicho plan de ejecución, además de algunas estadísticas referentes al resultado de la ejecución de la sentencia SQL o PLSQL, utilizando el comando AUTOTRACE. Para obtener el plan de ejecución no hay necesidad de ejecutar dicho comando pero, ciertamente, si no lo utilizamos, la poca amigabilidad del comando que debemos ejecutar (EXPLAIN PLAN), el formato de dicho comando y lo complejo que resulta analizar el contenido de la tabla V$SQL_PLAN, hacen que, por mi parte, recomiende encarecidamente el uso del comando SQL*Plus AUTOTRACE.

En mi opinión, AUTOTRACE es una buenísima herramienta de diagnóstico y una excelente ayuda para optimizar sentencias SQL y PL/SQL. El comando AUTOTRACE es puramente declarativo, por lo que es mucho más fácil de utilizar que el comando EXPLAIN PLAN. La sintaxis del comando AUTOTRACE es como sigue:

SET AUTOTRACE OFF - Deshabilita el análisis (traceado) de las sentencias SQL.

SET AUTOTRACE ON - Habilita el análisis (traceado) de las sentencias SQL.

SET AUTOTRACE TRACEONLY - Habilita el análisis (traceado) de las sentencias SQL pero no devuelve la salida de dicha sentencia. Su uso es recomendable si sólo estamos analizando el rendimiento de la sentencia y no nos interesa conocer los registros que pueda devolver.

SET AUTOTRACE ON/TRACEONLY EXPLAIN - Muestra el plan de ejecución de la sentencia pero no muestra las estadísticas.

SET AUTOTRACE ON STATISTICS - Muestra las estadísticas pero no muestra el plan de ejecución de la sentencia.

Nota: Si se omiten las opciones EXPLAIN y STATISTICS, entonces al ejecutar una sentencia SQL se mostrarán tanto el plan de ejecución como las estadísticas.

Para poder utilizar la opción EXPLAIN del comando AUTOTRACE, es necesario crear la tabla PLAN_TABLE en el esquema del usuario, es por eso que este comando sólo puede ser ejecutado por determinados usuarios, aquellos para los que la mencionada tabla ya ha sido creada. Es importante pues, conocer los usuarios Oracle que han sido configurados para poder ejecutar el comando AUTOTRACE.

Por otro lado, para acceder a las estadísticas, hay que tener acceso a varias tablas del sistema en las que se almacenan los datos del rendimiento de las sentencias SQL. Los DBA pueden dar este acceso utilizando el script plustrce.sql. El nombre de este script puede variar dependiendo del sistema operativo. El DBA tiene que ejecutar dicho script como usuario SYS y, asignar al usuario en cuestión, el papel (role) correspondiente.

Una vez que se ha configurado convenientemente un usuario para que pueda acceder al plan de ejecución y a las estadísticas, basta habilitar el AUTOTRACE para que, al ejecutar una sentencia SQL, nos aparezca el plan de ejecución así como los correspondientes valores estadísticos.

Los valores estadísticos más importantes mostrados por la base de datos Oracle, una vez activado el comando AUTOTRACE, son los siguientes:

- DB block gets: Número de operaciones de entrada/salida realizadas sobre la memoria caché.
- Consistent gets: Número de operaciones de entrada/salida realizadas sobre los segmentos de rollback debido a cambios en la memoria caché.
- Physical reads: Número de bloques leídos desde el disco.
- Sorts (memory): Número de operaciones realizadas en memoria para ordenar los datos.
- Sorts (disk): Número de operaciones realizadas en disco para ordenar los datos.

A la hora de mejorar el rendimiento de una sentencia SQL o PL/SQL, debemos conseguir que el número de db block gets, consistent gets y physical reads sea bajo comparado con el número de registros devueltos por dicha sentencia. Por otro lado, la ordenación de los datos debe realizarse, siempre que sea posible, en memoria.

En cuanto a lo que se refiere al plan de ejecución, desde este enlace podéis acceder a un ejemplo de sentencia SQL con su correspondiente plan de ejecución y una breve interpretación de dicho plan: Ejemplo de plan de ejecución.

Como un primer consejo a la hora de analizar un plan de ejecución, me gustaría indicar que lo primero que hay que evitar son los FULL SCAN (recorrido de todos los registros de una tabla). No obstante, hay determinadas circunstancias bajo las que un FULL SCAN puede ser recomendable; así, cuando una tabla tiene pocos registros, puede ser conveniente realizar un FULL SCAN, en vez de acceder a la misma a través de un índice.

Ya he escrito algunos artículos que pueden ayudar a interpretar un plan de ejecución desarrollado por el optimizador Oracle:

- Puesta a punto de sentencias SQL.
- Bucles y problemas de rendimiento.
- Hints en PL/SQL para el modo de optimización.
- Hints en PL/SQL para determinar el método de acceso.

No os preocupéis que próximamente escribiré alguno más.

Fases durante el procesamiento de una sentencia SQL

Durante el procesamiento de una sentencia SQL, ya sea mediante un script o un programa PL/SQL, se distinguen cuatro fases: análisis de la sintaxis (parsing), análisis de las variables (binding), ejecución (executing) y recuperación de datos (fetching).

Fase de parsing

Durante esta fase el servidor de la base de datos Oracle realiza las siguientes acciones:

- Busca la sentencia SQL en la memoria compartida (shared pool).

- Chequea la sintaxis de la sentencia siguiendo las especificaciones y la gramática del lenguaje SQL.

- Chequea la semántica, asegurando que los objetos Oracle referenciados en la sentencia SQL son válidos (existen en la base de datos) y satisfacen las restricciones de seguridad (es decir, el usuario que ejecuta la sentencia tienen los permisos adecuados sobre dichos objetos).

- Determina si el proceso que lanza la sentencia SQL tiene los permisos apropiados para ejecutarlo.

- Si la sentencia SQL incluye una vista (view) o una subquery (una subquery no es más que una sentencia SELECT que está dentro de otra sentencia SQL), transforma dicha sentencia en una sentencia SQL equivalente e intenta simplificar la sentencia resultante.

- Determina y almacena el plan de ejecución o, si es posible, utiliza un plan de ejecución existente.

Fase de binding

En esta fase el servidor de la base de datos Oracle:

- Identifica las variables (bind variables) en la sentencia SQL.

- Asigna o reasigna un valor a cada variable.

Esto quiere decir que en el momento de la optimización del plan de ejecución, el servidor Oracle no conoce los valores de estas variables. Esto facilita una re-ejecución de la sentencia más rápida, ya que no es necesario volver a hacer el parsing de la sentencia. No obstante, está el inconveniente de que el optimizador puede seleccionar un plan de ejecución equivocado causando problemas de rendimiento, cosa que no ocurriría si en vez de variables utilizamos constantes y, además, se recolectan estadísticas. El tema de la recolección de estadísticas para mejorar el rendimiento de las bases de datos Oracle requiere un análisis muy detallado y, quizás, escriba algún artículo más adelante sobre el tema.

Fase de ejecución

En la fase de ejecución el servidor Oracle realiza las siguientes acciones:

- Ejecuta la sentencia SQL siguiendo el plan de ejecución determinado durante la fase de parsing.

- Realiza las operaciones de entrada/salida necesarias para la ejecución de las sentencias de manipulación de datos (DML) y ordena los datos en caso de ser necesario.

Fase de fetching

Durante esta fase el servidor Oracle devuelve los registros de una sentencia SELECT. En cada iteración (fetch) el servidor devuelve múltiples registros. El número de registros que Oracle devuelve por iteración es configurable, es decir, se puede cambiar el tamaño de la matriz de registros. En SQL*Plus podemos cambiar dicho tamaño utilizando el comando SET ARRAYSIZE. Por ejemplo:

   SQL> show arraysize
   arraysize 15
   SQL> set arraysize 1

Tras la ejecución de este comando, el servidor Oracle procesará un solo registro en cada iteración (fetch). El valor por defecto es quince.

Hints en PL/SQL para determinar el método de acceso

Ya hemos hablado de los hints para el modo optimización. En este segundo artículo continuaré hablando de los hints pero, en concreto, de aquellos que permiten indicar al optimizador Oracle el modo en que se debe acceder a los datos de las tablas. Este tipo de hints resultan extremadamente eficaces a la hora de optimizar una sentencia SQL.

En su día ya indiqué cual es la sintaxis de los hints pero creo que no está de más que la muestre de nuevo:

{ DELETE | INSERT | SELECT | UPDATE } /*+ HINT (parámetros) */

o

{ DELETE | INSERT | SELECT | UPDATE } --+ HINT (parámetros)

Los hints básicos que sirven para determinar el metodo de acceso a los datos de una tabla Oracle son los siguientes:

/*+ FULL (nombre_tabla) */ - Fuerza a que se realice la búsqueda accediendo a todos los registros de la tabla indicada. Cuando las tablas tienen un número reducido de registros puede resultar bueno para el rendimiento de una sentecia DML el forzar un escaneado completo de la tabla en lugar de que el optimizador decida acceder a dicha tabla mediante un índice, ya que, en estos casos, el acceso por índice suele ser más lento.

/*+ ROWID (nombre_tabla) */ - Fuerza a que se acceda a la tabla utilizando el ROWID (identificativo único de los registros de una tabla). Este tipo de hint, por si solo, no es muy útil.

/*+ INDEX (nombre_tabla [nombre_índice] ...) */ - Fuerza a que se acceda a la tabla utilizando, en sentido ascendente, el índice indicado. Muchos problemas de rendimiento vienen causados por el hecho de que el optimizador Oracle decide acceder a una tabla utilizando un índice incorrecto. Mediante este hint podemos indicarle al optimizador que utilice el índice que nosotros consideremos adecuado.

/*+ INDEX_DESC (nombre_tabla [nombre_índice] ...) */ - Idéntico al anterior hint pero en este caso el acceso a través del índice se hace en sentido descendente.

/*+ AND_EQUAL (nombre_tabla [nombre_índice] ...) */ - Este hint se utiliza para forzar el uso de más de un índice (se utilizarían los índices indicados como parámetros) y, después, fusionar los índices quedándose con los registros encontrados en todas las búsquedas por índice realizadas.

/*+ INDEX_FFS (nombre_tabla [nombre_índice] ...) */ - Fuerza el acceso a los datos de la tabla mediante una búsqueda (Scan) rápida (Fast) y total (Full) sobre el índice indicado. Es parecido a utilizar el hint FULL pero sobre un índice en lugar de una tabla, lo cual, difícilmente, puede ser bueno para el rendimiento de una sentencia DML.

/*+ NO_INDEX (nombre_tabla [nombre_índice] ...) */ - Indica al optimizador que no se utilicen los índices indicados. Puede ser útil cuando no tengamos claro cual es el mejor índice que debe ser utilizado para acceder a una tabla pero, por contra, sepamos que podemos tener problemas de rendimiento si se accede a la tabla por un determinado índice y queramos evitar que esto ocurra.

Otros artículos relacionados: Puesta a punto de sentencias SQL (Tuning).