Transacciones parciales en PLSQL para lidiar con tablas muy grandes o voluminosas

Hace unas semanas alguien mencionó en un comentario que en su base de datos Oracle tenía una tabla con millones de registros y que, utilizando un bucle PLSQL, pensaba ejecutar un UPDATE y un COMMIT por cada, digamos, 500 registros procesados en dicha tabla, evitando así posibles problemas con los segmentos de rollback. El caso es que dicho lector me preguntaba si yo tenía alguna sugerencia al respecto. Este tipo de problema es, ciertamente, algo más complejo de lo que a simple vista parece, y sobre el que conviene escribir con cierta calma.

La funcionalidad de muestreo dinámico o Dynamic Sampling

La funcionalidad de muestreo dinámico (Dynamic Sampling) estuvo por primera vez disponible para la release 2 de la bases de datos Oracle 9i. Esta funcionalidad posibilita que el optimizador SQL y PL/SQL basado en costes (CBO) muestree las tablas que utiliza una consulta (query) durante la fase de parsing duro, para mejorar los valores estadísticos que utiliza el optimizador al incluir en dichas estadísticas los segmentos no analizados con anterioridad. Como ya he indicado, este muestro sólo ocurre durante la fase de parsing duro y se utiliza para mejorar las estadísticas utilizadas por el optimizador PLSQL, de ahí el nombre de muestro dinámico.

La vista ALL_IDENTIFIERS, usos y funcionalidades (2)

Ya escribí con anterioridad un articulo introductorio sobre la vista ALL_IDENTIFIERS. ALL_IDENTIFIERS muestra información relevante sobre todos los identificadores de los objetos PL/SQL o unidades de programa almacenados en la base de datos Oracle a los que tiene accesos un determinado usuario.

Las columnas clave de la vista ALL_IDENTIFIERS son:

• OWNER: El propietario de objeto PL/SQL o unidad de programa que contiene el identificador.
• NAME: Nombre del identificador.
• TYPE: Tipo de identificador (por ejemplo, FORALL OUT, CONSTANT, PACKAGE o RECORD).
• SIGNATURE: Cadena única asignada a cada identificador, útil para poder distinguir entre los diferentes identificadores que tienen el mismo nombre.
• OBJECT_NAME: Nombre del objeto o unidad de programa que contiene el identificador.
• OBJECT_TYPE: Tipo del objeto o unidad de programa que contiene el identificador (por ejemplo, PACKAGE, TRIGGER o PROCEDURE).
• USAGE: Tipo de uso del dentificador.
• USAGE_ID: Identificador único del uso del identificador.
• USAGE_CONTEXT_ID: Clave foránea que identifica el USAGE_ID de su registro padre (por ejemplo, el registro padre de un parámetro de una función o procedimiento dentro de un paquete PLSQL sería la propia función o procedimiento).
• LINE: Número de la línea de programa donde aparece el identificador.
• COL: Número de columna en la línea de programa donde aparece el identificador.

PL/Scope para analizar el código PL/SQL, la vista USER_IDENTIFIERS (1)

Ya hablamos en un artículo anterior sobre la posibilidad de ejecutar consultas sobre la vista USER_SOURCE para comprobar la presencia o ausencia de determinadas cadenas de caracteres en nuestro código PL/SQL, algo que, entre otras posibilidades, permite realizar comprobaciones simples para comprobar si nuestro código cumple con unos mínimos requisitos de calidad.

Pero la base de datos Oracle, desde la aparición de la versión 11g, ofrece medios mucho más potentes para analizar nuestro código PL/SQL utilizando PL/Sope. En este artículo hablaremos de como utilizar esta nueva funcionalidad para ampliar y mejorar lo que ya ofrecía la vista USER_SOURCE mediante la utilización de la vista USER_IDENTIFIERS. Este es el primer artículos de una serie en la que hablaremos de todas las posibilidades que ofrece esta nueva vista.

Vistas USER_ARGUMENTS y DBA_ARGUMENTS para analizar los argumentos de funciones y procedimientos

USER_ARGUMENTS y DBA_ARGUMENTS son dos vistas realmente útiles para los programadores de PL/SQL. Contienen información sobre los argumentos, también llamados parámetros, de cada uno de los programas almacenados en nuestro esquema (USER_ARGUMENTS) o en toda la base de datos Oracle (DBA_ARGUMENTS, el acceso a esta vista suele estar restringido a los administradores de la base de datos). Al mismo tiempo, ofrece una gran cantidad de información muy bien diseccionada y estructurada.

Las columnas clave de esta vista son:

• OWNER (solo en DBA_ARGUMENTS): Nombre del esquema propietario del procedimiento o función PL/SQL.
• OBJECT_NAME: Nombre del procedimiento o función.
• PACKAGE_NAME: Nombre del paquete PL/SQL dentro del cual se ha definido el procedimiento o función.
• ARGUMENT_NAME: Nombre del argumento o parámetro.
• POSITION: Posición del argumento dentro de la lista de parámetros. Si toma el valor 0, hace referencia a la cláusula RETURN dentro de una función.
• IN_OUT: Indica si el argumento es de entrada o salida o permite ambos modos (IN - entrada, OUT - salida, IN OUT - entrada salida).
• DATA_TYPE: El tipo de dato del argumento.
• DATA_LEVEL: El nivel de anidamiento del argumento para tipos compuestos. Por ejemplo, si uno de los parámetros es de tipo registro, la vista USER_ARGUMENTS incluirá una entrada para este argumento con un DATA_LEVEL de 0 y diversas entradas con un DATA_LEVEL de 1 para cada campo de dicho registro.

Gestión de errores en PLSQL - Visión general

Incluso si fuéramos capaces de escribir un programa PL/SQL absolutamente perfecto, es altamente probable que algo pueda ir mal y se produzcan errores durante la ejecución. La manera en que nuestro código responde frente a estos errores, a menudo determina la diferencia entre una aplicación que funciona correctamente y otra que da continuos problemas a los usuarios y a los encargados de su mantenimiento.

Este es el primer artículo de una serie que escribiré sobre la gestión de errores en PLSQL. En ellos podréis leer sobre: los diferentes tipos de excepciones que se pueden dar; cuándo, cómo y por qué se generan excepciones; cómo definir nuestras propias excepciones; como manejar las excepciones cuando estas se producen; y cómo es posible informar a los usuarios cuando aparece un problema.

Problemas con los triggers SQL

Mucha gente piensa que los triggers PL/SQL son una de las más potentes herramientas de las bases de datos Oracle. De hecho lo son, pero existen dos razones fundamentales por las que, personalmente, trato de evitar la utilización de triggers a la hora de implementar mis proyectos en PL/SQL.

Tablas Oracle: Claves naturales o claves sustitutivas

En algunas ocasiones me he encontrado con bases de datos en las que se aplica la norma de, a la hora de diseñar una tabla nueva, utilizar siempre una clave sustitutiva (surrogate key) , incluso existiendo una clave natural perfectamente aplicable. Cuando he preguntado por el motivo de crear tales claves sustitutivas, la razón ha sido casi siempre la de aumentar la eficiencia de la base de datos eliminando la posibilidad de tener que enlazar dos tablas utilizando más de una columna.

Objetos bloqueados en una base de datos (1)

Todo el mundo ha intentado hacer alguna llamada desde su móvil y dicha llamada no ha podido realizarse por problemas de congestión, es decir, otros usuarios han copado los canales disponibles y nosotros no hemos podido tomar posesión de ninguno de ellos. Algo parecido puede ocurrir con las sesiones de Oracle (o de cualquier otra base de datos), ya que una sesión puede bloquear (mantener un "lock") un objeto de la base de datos (tabla, columna, etc) evitando que otra sesión pueda utilizarlo.

En esta nota voy a contar como identificar que sesión es la causante del bloqueo y en posteriores mensajes iré más allá y contaré como identificar el objeto que está bloqueado.

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.

Cláusula DEFAULT para definición de columnas - Base de datos Oracle 12c

Las mejoras fundamentales que aporta la nueva cláusula DEFAULT de la versión 12c de las bases de datos Oracle para definición de las columnas: una significativa mejora del rendimiento y una mayor facilidad para inicializar los datos de los registros de una tabla, lo que al final implica menos líneas de código.

Supongamos que para una tabla concreta necesitamos que, cuando se inserta un nuevo registro, un campo determinado tome el valor de una secuencia. La forma en que implementaríamos este requerimiento en versiones anteriores a la 12c sería mediante un trigger PLSQL.

Identificación de tablas y columnas en una base de datos Oracle mediante sentencias SQL

Una vez que una tabla Oracle es creada utilizando el comando CREATE TABLE, la estructura de dicha tabla se puede visualizar utilizando las siguientes vistas (views) del sistema de la base de datos Oracle:

DBA_TABLES: Muestra la información de la tabla a nivel de cabecera.

DBA_TAB_COLUMNS: Muestra la información de la tabla a nivel de columna.

DBA_TAB_PRIVS: Muestra los privilegios de acceso a las tablas de los usuarios de la base de datos.

DBA_COL_PRIVS: Muestra los privilegios de acceso a nivel de columna. Es bastante raro tener la necesidad de definir permisos a nivel de columna, de hecho, yo nunca me he encontrado con una bases de datos Oracle que utilice esta funcionalidad.

Tablas temporales en las bases de datos Oracle

Además de las tablas de la base de datos permanentes, Oracle permite la creación de tablas temporales para mantener datos propios y exclusivos a una sesión Oracle determinada. Estos datos permanecerán en el sistema sólo durante el tiempo que dure la transacción o sesión involucrada. No obstante, al igual que para las tablas permanentes, la definición de las tablas temporales se almacena en las tablas del sistema.

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.

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.

Acceso remoto mediante DBLINK a una base de datos Oracle

La manera más sencilla de acceder desde una base de datos Oracle a tablas y vistas (views) de otra base de datos Oracle es mediante el uso de un DBLink (enlace a base de datos). No obstante, en muchos casos puede no ser recomendable la utilización de DBLinks, ya que el acceso a tablas y vistas remotas puede provocar importantes problemas de rendimiento en ambas bases de datos, tanto la remota como la local. En la mayoría de los casos estos problemas de rendimiento se deben a la imposibilidad de utilizar los índices de las tablas remotas.

Objetos bloqueados en una base de datos (2)

Esta nota es continuación del anterior post Objetos bloqueados en una base de datos (1). Así pretendo dejar zanjado el tema referente a la información almacenada en la tabla V$LOCK.

Tipos de locks y las columnas ID1 e ID2

En nuestro ejemplo de la nota anterior, ya sabemos que nuestro lock es un lock producido por una sentencia DML (sentencias de manipulación de datos como select, insert, update, delete, etc), pero esto es porque fuimos nosotros los que ejecutamos la sentencia bloqueadora. Evidentemente este no va a ser siempre el caso, ya que una base de datos es normalmente compartida por multitud de usuarios. Afortunadamente, podemos encontrar la información que necesitamos en la tabla V$LOCK.

Cómo analizar la interdepencia entre los objetos de una base de datos Oracle

Para analizar y conocer las dependencias existentes entre funciones, procedimientos, paquetes y triggers a los que tiene acceso un usuario de la base de datos Oracle se debe utilizar la vista USER_DEPENDENCIES.

Esta vista, sin ir más lejos, se puede utilizar para realizar un análisis de nuestro código PL/SQL, permitiendo por ejemplo identificar que programas necesitan ser revisados y actualizados si realizamos algún tipo de cambio sobre una tabla determinada de la base de datos.

Cómo obtener información sobre los procedimientos, funciones y triggers PLSQL

Obtener información sobre Procedimientos y Funciones PL/SQL

La vista USER_PROCEDURES proporciona información sobre todas la funciones y procedimientos dentro de nuestro esquema, tanto a nivel de esquema como aquellas que se encuentran definidas dentro de los paquetes PL/SQL.

Las columnas más significativas dentro de esta vista son:

• AUTHID: Identifica si el procedimiento o función se ejecutará con los permisos del usuario que lo llama (CURRENT_USER) o con los permisos del propietario del programa (DEFINER).
• DETERMINISTIC: Esta columna toma el valor de YES si la función se ha definido como deterministas (deterministic), lo que teóricamente significa que el valor devuelto por la función PLSQL queda unívocamente determinado dependiendo de los valores que tomen los argumentos de la función.
• PIPELINED: Cuando toma el valor de YES quiere decir que la función ha sido definida como pipelined, lo cual quiere decir que puede ejecutarse el paralelo como parte de una consulta con ejecución paralela.
• OVERLOAD: Este campo tendrá un valor numérico positivo si el programa correspondiente esta overloaded, o lo que es lo mismo, cuando dentro del mismo paquete PLSQL existen al menos dos subprogramas con el mismo nombre (como podéis ver, la palabra overloaded casi nada tiene que ver con su traducción directa sobrecargado).

Nueva cláusula BEQUEATH para las vistas (Oracle 12c)

Anteriormente a la versión 12c de las bases de datos Oracle, si desde una vista había que ejecutar una función PL/SQL siempre se invocaba con permisos del propietario de la vista, no los privilegios del propietario de la función. Esto implicaba que si la función había sido definida por el invocador, la conducta de la misma podía ser bastante diferente a lo esperado cuando se ejecutaba desde una vista.

Para solucionar este problema, la versión 12c de la base de datos Oracle incorpora la cláusula BEQUEATH que permite definir una vista para que si esta incluye funciones, los permisos de ejecución de las mismas se acomoden con los permisos del usuario que invoca la vista.