Anuncios en tutorial de programación PLSQL

lunes, 18 de marzo de 2024

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.

ORA-01555 Snapshot Too Old

Si dividimos una transacción muy larga en muchas otras más pequeñas, existen bastantes probabilidades de que a mitad de la transacción global se produzca un error ORA-01555 (snapshop too old), problema generalmente causado por la alta frecuencia en la ejecución de sentencias COMMIT, o que simplemente se produzca un fallo del sistema. Entonces nos encontraremos con una transacción realizada parcialmente. Por lo tanto, antes de implementar este tipo de solución, deberemos asegurarnos de la que la transacción global puede re-ejecutarse. La cuestión es que en muchos casos esto no será posible y, por tanto, tendremos que escribir bastante código PL/SQL para hacer posible esta re-ejecución.

miércoles, 28 de febrero de 2024

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.

Muestreo dinámico en las bases de dato Oracle para SQL y PL/SQL

El optimizador de las bases de datos Oracle usa una gran variedad de datos de entradas para generar los planes de ejecución. Utiliza todos los índices y restricciones (constraints) definidos en las tablas, las estadísticas del sistema (velocidad de entrada/salida de los servidores, velocidad de CPU), y las estadísticas recogidas de los segmentos involucrados en la ejecución de la consulta. El optimizador utiliza estas estadísticas para estimar el número de registros involucrados en cada uno de los pasos de los que consta un plan de ejecución específico, siendo este volumen de registros la variable principal a la hora de calcular el coste de ejecución de una consulta. Cuando el optimizador calcula incorrectamente el volumen de registros, probablemente escogerá un plan de ejecución ineficiente causando problemas de rendimiento en la base de datos. De hecho, la principal razón por la que el optimizador Oracle puede decidirse por un plan de ejecución incorrecto, es precisamente por esta causa, es decir, una estimación incorrecta del volumen de registros a procesar en cada paso del plan de ejecución.

jueves, 8 de febrero de 2024

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

martes, 23 de enero de 2024

El paquete PL/SQL DBMS_SCHEDULER para programación de trabajos

DBMS_SCHEDULER es el paquete PLSQL que reemplazó en la versión de la base de datos Oracle 10g al paquete DBMS_JOB. Aunque el paquete DBMS_JOB sigue existiendo por razones de compatibilidad, no debe utilizarse ya que es muy probable que deje de existir en futuras versiones de la base de datos Oracle. El paquete DBMS_SCHEDULER permite programar la ejecución, en los instantes que deseemos, de bloques PLSQL, así como de procedimientos y funciones PL/SQL. Por otro lado, también permite programar la ejecución de binarios y shell-scripts.

DBMS_SCHEDULER programación de trabajos y procesos en PLSQL

Permisos necesarios

Con permisos de DBA se tiene acceso a todas las funciones del paquete DBMS_SCHEDULER. Para administrar la programación de procesos se necesita tener la rol (role) de SCHEDULER_ADMIN. Y finalmente, para crear y ejecutar procesos bajo tu propia identidad, se necesita tener el privilegio CREATE JOB. Por otro lado, aprovecho para mencionar que el paquete DBMS_JOB requería la inicialización de un parámetro del sistema, tras lo cual se lanzaba un proceso en background encargado de coordinar la ejecución de los distintos procesos programados, pero esto ya no es necesario si utilizamos Oracle 10g y el paquete DBMS_SCHEDULER.

jueves, 21 de diciembre de 2023

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.

Problemas con los triggers SQL

Problemas de mantenimiento

A largo plazo, la utilización de triggers suele causar grandes dolores de cabeza a la hora de pensar en el mantenimiento. Al ser piezas del código que sólo ocurren como consecuencia de que se ha realizado otra operación, es muy frecuente que la gente se olvide de que los triggers están allí, y revisar el código pensando en todas las piezas de la base de datos que pueden afectarle, se hace poco menos que imposible.

martes, 28 de noviembre de 2023

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.

Claves alternativas y naturales

Primero aclararé, para aquellos que no lo tengan claro, el concepto de clave sustitutiva o surrogate key. Una clave sustitutiva no es más que una clave interna, un identificador único, que no tiene significado para el negocio, y que identifica de forma única un registro de una tabla de la base de datos.

jueves, 9 de noviembre de 2023

Vistas materializadas y la funcionalidad "Query Rewrite"

Ya he escrito anteriormente un par de artículos sobre vistas materializadas (materialized views): uno sobre los aspectos generales de las vistas materializadas en SQL y PLSQL y otro sobre el refresco de las vistas materializadas en SQL y PL/SQL. En este artículo voy a tratar una de las funcionalidades soportadas por las vistas materializadas, funcionalidad conocida como QUERY REWRITE.

La funcionalidad de reescritura de consultas y las vistas materializadas

Funcionalidad de reescritura de una consulta

Esta claro que acceder a una vista materializada puede ser significativamente más rápido que acceder a todas las tablas base utilizadas al crear dicha vista materializada. Es por esta causa por la que, si así lo hemos indicado al crear la vista materializada, el optimizador Oracle, si la consulta o query lo permite, puede reescribir el plan de ejecución de dicha consulta para acceder a la vista en lugar de a las tablas base. Obviamente, la reescritura de la consulta es transparente a las aplicaciones que la estén utilizando. Así pues, de alguna manera, el uso del QUERY REWRITE es similar al uso de un índice.

martes, 24 de octubre de 2023

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.

Sintaxis de la cláusula COUNT

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.

martes, 3 de octubre de 2023

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:

Optimización de la base de datos Oracle mediante el uso de código compartido o los shared cursors

- 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:

martes, 5 de septiembre de 2023

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