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.

Generador de números aleatorios en PL/SQL

Es posible que en alguna ocasión necesitéis utilizar un generador de números aleatorios en un programa PL/SQL. Oracle proporciona el paquete estándar DBMS_RANDOM para este propósito. Obviamente podemos escribir nuestra propia rutina PLSQL que genere números aleatorios, pero paquete estándar de Oracle DBMS_RANDOM es más rápido ya que llama al generador de números aleatorios interno de la base de datos Oracle.

Los procedimientos y funciones que incluye este paquete son:

INITIALIZE: Inicializa el valor de la semilla del generador de números aleatorios.

EXEC dbms_random.initialize(12345678);

Asignación de permisos (ROLES) a los programas PLSQL (mejoras versión 12c)

Con anterioridad a la versión 12c de la base de datos Oracle, un procedimiento PLSQL o unidad de programa con privilegios definidos a través de la cláusula AUTHID DEFINER siempre se ejecutaba con los privilegios del propietario del programa. Por otro lado, cuando la misma unidad de programa se definía utilizando la cláusula AUTHID CURRENT_USER, dicho programa siempre se ejecutaba con los privilegios del usuario que lo ejecutaba.

Disponer de solo estas dos formas de utilización de la cláusula AUTHID limitaba bastante la funcionalidad de las bases de datos Oracle en cuestión de seguridad, ya que cuando un usuario necesitaba ejecutar un programa PL/SQL determinado, dicho usuario tenía que tener los mismos privilegios que el propietario del programa. El problema de seguridad se agravaba mucho más cuando eran todos los usuarios los que necesitan tener acceso a dicho programa.

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.

Creación diferida de segmentos (Deferred Segment Creation), funcionalidad de la release 2 de Oracle 11g

En las versiones anteriores a la release 2 de la base de datos Oracle 11g, cuando se creaba cualquier objeto en la base de datos utilizando una sentencia SQL o PL/SQL, ya fuera un tabla, un índice o cualquier otro objeto que requiriese ser almacenado, el gestor de la base de datos creaba los segmentos necesarios y asignaba un tamaño inicial a los mismos, un tamaño que podía ser pequeño, de unos 64 Kbytes mínimo, pero dicho espacio ya no podía ser utilizado para otras necesidades. En los tiempos actuales, un tamaño de 64 Kbytes no es nada, pero si algo tan pequeño se tienen que repetir muchas veces, entonces el consumo de recursos de almacenamiento puede llegar a ser bastante grande.

Por ejemplo, podemos pensar en alguna aplicación que necesitase crear cientos o miles de tablas, tablas que nunca van a ser utilizadas pero cuyos segmentos de almacenamiento tienen que ser creados de todas formas. Algunos pueden preguntarse por qué un aplicación puede necesitar crear tablas que nunca van a ser utilizadas, este hecho no es tan extraño ya que existen muchas aplicaciones, como por ejemplo Oracle Financials, que ofrecen funcionalidades que requieren el uso de determinadas tablas y dichas tablas sólo contienen datos cuando se hace uso de dichas funcionalidades. Para evitar el consumo de almacenamiento en este tipo de situaciones, la release 2 de la base de datos Oracle 11g incorpora una nueva funcionalidad conocida como creación diferida de segmentos (Deferred Segment Creation), funcionalidad que a continuación explicaremos y analizaremos.

Uso de ediciones para actualizar la base de datos

Desde mi punto de vista, la funcionalidad Edition-Based Redefinition, cuya traducción directa sería "redefinición basada en ediciones", se trata de la característica más interesante de la release 2 de la base de datos Oracle 11g. En pocas palabras se trata de la posibilidad de actualizar nuestra aplicación PL/SQL sin tener que poner en modo restringido la base de datos, es decir, a partir de esta versión de la base de datos Oracle es posible realizar online la actualización de nuestra aplicación. Explicado de esta manera tan sencilla la mencionada funcionalidad suena como si se ratase de un juego de niños, pero en realidad es algo cuya verdadera dimensión resulta difícil de medir por sus enormes implicaciones.

Funciones PLSQL TO_CHAR, EXTRACT, TO_DATE y TO_TIMESTAMP (conversión de fechas a caracteres y viceversa)

Este artículo es continuación del anterior en el que hablé sobre los tipos de dato fecha DATE, TIMESTAMP e INTERVAL y en él hablaré sobre las funciones PL/SQL que se pueden utilizar para convertir datos de tipo fecha en datos de tipo carácter y viceversa.

Pero antes de hablar de estas funciones, resulta conveniente conocer como se puede obtener desde PLSQL el valor de la fecha y el tiempo actual. Seguro que la gran mayoría de vosotros, si sois programadores PL/SQL, ya conocéis la clásica función SYSDATE, función que, sin duda, es la más empleada en este sentido. No obstante, la base de datos Oracle ofrece la posibilidad de utilizar otras funciones que proporcionan diferentes variantes del valor de la fecha y el tiempo actual y que veremos a continuación.

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.

Oracle 11g y la cláusula PIVOT: Como girar columnas en SQL y PL/SQL

Una tarea que puede resultarnos útil en determinadas circunstancias es conseguir girar (en inglés pivot) los resultados entregados por una consulta SQL o PLSQL. Por ejemplo, supongamos que escribimos la siguiente consulta para calcular el precio medio de los productos por almacen:

SQL> SELECT almacen, producto, AVG(precio) pmedio
2  FROM  productos
3  GROUP BY almacen, producto
4  /

ALMACEN PRODUCTO PMEDIO
------- -------- ------
Ba      ABC123       95
Ba      DEF456       75
Ba      XYZ987      160
Ma      ABC123      100
Ma      XYZ987      150
Va      DEF456       80

6 rows selected.