PLSQL dinámico con las funciones DBM_SESSION.SET_CONTEXT y SYS_CONTEXT (¿Por qué?)

En programación PL/SQL siempre tenemos lectores que nos hacen preguntas interesantes, en esta ocasión se nos ha preguntado acerca del motivo por el cual al utilizar PLSQL dinámico hay mucha gente que utiliza las funciones estándar de Oracle SYS_CONTEXT y DBM_SESSION.SET_CONTEXT, de manera que en la cláusula WHERE de cualquier consulta SQL, en lugar de utilizar simplemente literales se utiliza la función SYS_CONTEXT.

Es decir, por qué utilizar "WHERE valor = SYS_CONTEXT('mi_contexto','valor')", en vez de, por ejemplo, la simple y más corta sentencia "WHERE valor = 15".

Tuning o puesta a punto de consultas SELECT COUNT(*) en PL/SQL

De vez en cuando recibo consultas sobre cómo sería posible mejorar el rendimiento de sentencias PL/SQL concretas. En la mayoría de los casos contestar a estas preguntas puede ser poco menos que imposible, más que nada porque realizar el tuning de una consulta PL/SQL sin conocer el contexto en que se ejecuta dicha consulta resulta muy complicado. Cada vez que esto ocurre siempre me asaltan preguntas como: ¿por qué se ejecuta dicha consulta?, ¿puede eliminarse la consulta y ser incluida en otro proceso?, ¿está la consulta dentro de un bucle LOOP y realmente debe formar parte del bucle?, ¿están creados todos los índices que podrían acelerar su ejecución? Por si esto fuera poco, una vez que tenemos la respuesta a preguntas como las antes mencionadas, sin duda, surgirán nuevas preguntas.

No obstante, el otro día un asiduo lector de este blog me envió una consulta SELECT bastante sencilla que, aún utilizando los índices de forma adecuada y ejecutándose bastante rápido, terminaba consumiendo muchos recursos de CPU en su base de datos Oracle debido a que era ejecutaba con mucha frecuencia dentro un procedimiento PLSQL. Dicho lector me pedía ayuda para realizar el tuning o puesta a punto de la mencionada consulta.

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:

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.

Componentes del Optimizador Oracle

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.

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 o PL/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.

SQL y PL/SQL - La sentencia INSERT multitabla

La versión 9i de las bases de datos Oracle introdujo la posibilidad de utilizar sentencias INSERT multitabla. Así pues, la sentencia SQL o PLSQL INSERT... SELECT cambió 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.

Cláusula BULK COLLECT para mejorar el rendimiento al realizar procesamiento masivo

Yo siempre he dicho que cuando para hacer algo se pueden utilizar sentencias SQL sencillas, no resulta conveniente emplear complicados procedimientos PL/SQL que implementen la misma solución. Sin embargo, hay situaciones en que para mejorar el rendimiento de determinados bucles FOR en los que se realizan actualizaciones masivas sobre una determinada tabla de la base de datos Oracle, resulta conveniente utilizar técnicas PLSQL de procesamiento masivo (lo que en inglés se denomina BULK COLLECT).

Para entender mejor en qué consiste esta técnica, primero hay que comprender los motivos por los que un simple bucle FOR puede generar importantes problemas de rendimiento. Veamos el siguiente código PL/SQL:

¿Qué es mejor para el rendimiento, utilizar consultas PLSQL con subqueries o con joins?

Alguna vez me han llegado cuestiones en la que se me preguntaba qué es mejor, en términos de rendimiento de las bases de datos Oracle, si utilizar en las consultas PLSQL subqueries (subconsultas) o utilizar joins (es decir, listar todas las tablas en la clausula FROM y unirlas en el WHERE). Lo primero que hay que tener claro es que escribir una consulta PL/SQL utilizando subqueries o utilizando joins es semánticamente diferente; además, utilizar una u otra forma puede derivar en que ambas consultas devuelvan resultados diferentes y que no sean directamente intercambiables.

Lo que yo recomiendo para elegir entre un tipo de consulta u otro es, en general, hacer lo siguiente:

• Utilizar una subquery o subconsulta cuando no se necesita ninguna columna de la tabla que es referenciada en la subquery.
• Utilizar un join en caso de necesitar alguna de las columnas.

SQL y PL/SQL - La 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.

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.

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.

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).

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.

Almacenamiento de subconsultas (subqueries PL/SQL) en la caché de las bases de datos Oracle

El almacenamiento caché de subconsultas o subqueries PL/SQL se trata de una funcionalidad de las bases de datos Oracle, denominada en inglés scalar subquery caching, que se encarga de optimizar internamente la ejecución de aquellas consultas que incorporan subconsultas. El funcionamiento es bastante intuitivo, si durante la ejecución de una consulta PLSQL compleja, dicha consulta incluye alguna subquery, la base de datos Oracle intentará almacenar en la caché la salida de dicha subconsulta con el objetivo de poder reutilizar dichos datos, una y otra vez, durante la ejecución de la consulta PL/SQL principal. Obviamente esto será mucho mejor para el rendimiento de la base de datos que el tener que re-ejecutar la subconsulta múltiples veces.

Los resultados de la subconsulta quedan almacenados en una estructura de datos interna o hash table que, mientras dura la ejecución de la consulta PLSQL, queda residente en la memoria caché de la sesión Oracle correspondiente. Dicha estructura de datos desaparece de la caché en el momento que la consulta PL/SQL termina.

Puesta a punto de sentencias SQL (Tuning PLSQL 1)

Si la siguiente sentencia SQL SELECT:

SELECT *
FROM empleados
WHERE nombre = 'Francisco'
AND estado_civil = 'S' -- Soltero

Nos está dando tiempos de ejecución largos, esto querrá decir que:

   1. Obviamente la tabla empleados es de un tamaño considerable.

   2. La tabla no está adecuadamente indexada o que, aún habiéndose creado el índice adecuado, lógicamente un índice sobre la columna "nombre", el optimizador SQL decide utilizar el índice sobre otra columna.

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:

Diferencias entre restricciones PLSQL (cláusula CONSTRAINT) a nivel de tabla y a nivel de columna

Resulta de perogrullo decir que una restricción PLSQL (cláusula CONSTRAINT) a nivel de columna sólo aplica a la columna sobre la que se ha definido, mientras que una restricción a nivel de tabla puede incluir todas las columnas de dicha tabla. Esta es la diferencia básica en PL/SQL, pero también conviene señalar que cualquier restricción a nivel de columna puede definirse también a nivel de tabla, sin embargo, no todas las restricciones a nivel de tabla pueden definirse a nivel de columna.

Ojo, no estoy diciendo que todas las restricciones deban definirse a nivel de tabla, simplemente estoy diciendo que es posible hacerlo. De hecho, mi opinión es que, siempre que se pueda expresar una restricción utilizando una CONSTRAINT PLSQL a nivel de columna, debemos hacerlo de esta manera. Una razón evidente es que una restricción a nivel de columna es sintácticamente más clara ya que resulta obvio que aplica a sólo una columna, mientras que si utilizamos una CONSTRAINT a nivel de tabla para definir la misma restricción, deberemos prestar más atención al código PL/SQL para comprender lo que hace. Pero esta no es la única razón, las hay todavía de mayor peso.

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.

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.

Memoria PGA frente a espacio de almacenamiento temporal en las bases de datos Oracle

Recientemente he recibido en este blogs sobre programación PL/SQL algunas preguntas referentes a las diferencias entre el tipo de operaciones que la base de datos Oracle realiza en la memoria del área global de programa (PGA - Program Global Area) frente a las que realiza en el espacio del almacenamiento temporal (TEMP space). Primero os comentaré que las versiones antiguas de las bases de datos Oracle solían utilizar los parámetros SORT_AREA_SIZE y HASH_AREA_SIZE para controlar cuanta memoria PGA era posible utilizar antes de empezar a tener que usar el espacio de disco temporal (TEMP).

No obstante en las nuevas versiones de las bases de datos Oracle, la gestión de la memoria PGA se realiza de forma automática y mucho más dinámica.