PL/SQL

Parte 4

Procedural Language extension to Structured

 Transacciones
 Triggers
 Optimización
 Transacciones
 Una transacción es un conjunto de operaciones que se
ejecutan en una base de datos, y que son tratadas como
una única unidad lógica por el SGBD. Es decir, una
transacción es una o varias sentencias SQL que se
ejecutan en una base de datos como una única operación,
confirmándose o deshaciéndose en grupo
 No todas las operaciones SQL son transaccionales. Sólo
son transaccionales las operaciones correspondiente al
lenguaje DML, es decir, sentencias SELECT, INSERT,
UPDATE y DELETE
 Para confirmar una transacción se utiliza la sentencia
COMMIT. Cuando realizamos COMMIT los cambios se
escriben en la base de datos
 Transacciones
 Para deshacer una transacción se utiliza la sentencia
ROLLBACK. Cuando realizamos ROLLBACK se
deshacen todas las modificaciones realizadas por la
transacción en la base de datos, quedando la base de
datos en el mismo estado que antes de iniciarse la
transacción
 En el servidor Oracle, las transacciones de DML
comienzan en el primer comando seguido de un COMMIT
o ROLLBACK y terminan en el siguiente COMMIT o
ROLLBACK ejecutado exitosamente
 Para marcar un punto intermedio en el tratamiento
transaccional, se utiliza SAVEPOINT
Transacciones
 Transacciones
 Un ejemplo clásico de transacción son las
transferencias bancarias. Para realizar una
transferencia de dinero entre dos cuentas bancarias
debemos descontar el dinero de una cuenta, realizar el
ingreso en la otra cuenta, grabar las operaciones y
movimientos necesarios, y actualizar los saldos .Si en
alguno de estos puntos se produce un fallo en el
sistema podríamos haber descontado el dinero de una
de las cuentas y no haberlo ingresado en la otra. Por lo
tanto, todas estas operaciones deben ser correctas o
fallar todas. En estos casos, al confirmar la transacción
(COMMIT) o al deshacerla (ROLLBACK) garantizamos
que todos los datos quedan en un estado consistente
 Transacciones
 En una transacción los datos modificados no son
visibles por el resto de usuarios hasta que se confirme
la transacción
 Si alguna de las tablas afectadas por la transacción
tiene triggers, las operaciones que realiza el trigger
están dentro del ámbito de la transacción, y son
confirmadas o deshechas conjuntamente con la
transacción
 ORACLE es completamente transaccional. Siempre
debemos especificar si que queremos deshacer o
confirmar la transacción
 Transacciones Autónomas
 En ocasiones es necesario que los datos escritos por
parte de una transacción sean persistentes a pesar de
que la transacción se deshaga con ROLLBACK
 PL/SQL permite marcar un bloque con PRAGMA
AUTONOMOUS_TRANSACTION. Con esta directiva
marcamos el subprograma para que se
comporte como transacción diferente a la del proceso
principal, llevando el control de COMMIT o
ROLLBACK independiente
 Transacciones Autónomas
CREATE OR REPLACE PROCEDURE Grabar_Log(descripcion
VARCHAR2)
DECLARE IS
producto PRECIOS%TYPE; PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN BEGIN
producto := '100599'; INSERT INTO LOG_APLICACION
INSERT INTO PRECIOS (CO_ERROR, DESCRIPICION, FX_ERROR)
(CO_PRODUCTO, PRECIO, FX_ALTA) VALUES
VALUES (SQ_ERROR.NEXTVAL, descripcion, SYSDATE);
(producto, 150, SYSDATE); COMMIT; -- Este commit solo afecta a la transaccion
COMMIT; autonoma
EXCEPTION END ;
WHEN OTHERS THEN
Grabar_Log(SQLERRM);
ROLLBACK;
/* Los datos grabados por "Grabar_Log" se escriben en
la base
de datos a pesar del ROLLBACK, ya que el
procedimiento está
marcado como transacción autonoma.
*/
END;

 Es muy común que, por ejemplo, en caso de que se produzca algún tipo de
error se quiera insertar un registro en una tabla de log con el error que se
ha producido y hacer ROLLBACK de la transacción. Pero si hacemos
ROLLBACK de la transacción también lo hacemos
de la inserción del log
 Triggers
 Son bloques nominados que se almacenan en
la base de datos. Su ejecución está
condicionada a cierta condición
 No pueden ser invocados directamente
 No reciben y retornan parámetros
 Se crean con la instrucción "create trigger"
seguido del nombre del disparador. Si se
agrega "or replace" al momento de crearlo y ya
existe un trigger con el mismo nombre, tal
disparador será borrado y vuelto a crear
 Triggers
 CREATE [OR REPLACE] TRIGGER nombre
{BEFORE | AFTER | INSTEAD OF}
{INSERT | DELETE | UPDATE [OF ]} ON <tabla>
[FOR EACH ROW | STATEMENT]
[WHEN condición]
[DECLARE …]
BEGIN cuerpo del trigger
[EXCEPTION …]
END;
 Triggers
 Temporalidad del Evento

Before => Ejecutan la acción asociada antes de
que la sentencia sea ejecutada

After => Ejecutan la acción asociada después
de que se haya ejecutado la sentencia
 Nivel del evento

Fila => Ejecutan la acción asociada tantas
veces como filas se vean afectadas por la
sentencia que lo dispara

Sentencia => Ejecutan una única vez la acción
asociada, independientemente del número de
filas que se vean afectadas por la sentencia
(incluso si no hay filas afectadas)
 Triggers
 Orden de ejecución

Una sentencia SQL puede disparar varios TRIGGERS

La activación de un trigger puede disparar la activación
de otros triggers


1. Triggers Before (a nivel de sentencia)

2. Para cada fila:
• 1. Trigger Before (a nivel de fila)
• 2. Ejecuta la Sentencia
• 3. Triggers After (a nivel de fila)

3. Triggers After (a nivel de sentencia)
 Triggers
 Con :OLD.nombre_columna referenciamos

Al valor que tenía la columna antes del cambio
debido a una modificación (UPDATE)

Al valor de una columna antes de una operación de
borrado sobre la misma (DELETE)

Al valor NULL para operaciones de inserción
(INSERT)
 Con :NEW.nombre_columna referenciamos

Al valor de una nueva columna después de una
operación de inserción (INSERT)

Al valor de una columna después de modificarla
mediante una sentencia de modificación (UPDATE)

Al valor NULL para una operación de borrado
(DELETE)
 Triggers
 Disparados por sentencias DML (de varios
tipos)

INSERT or UPDATE or DELETE

CREATE OR REPLACE TRIGGER Ejemplo
BEFORE INSERT OR UPDATE OR DELETE ON tabla
BEGIN
IF DELETING THEN
Acciones asociadas al borrado
ELSIF INSERTING THEN
Acciones asociadas a la inserción
ELSIF UPDATING
Acciones asociadas a la modificación
END IF;
END Ejemplo;
 Optimización
 Una de las tareas más importantes de un desarrollador
de bases de datos es la de optimización, ajuste, puesta
a punto o tunning
 Hay que tener en cuenta que las sentencias SQL
pueden llegar a ser muy complejas y conforme el
esquema de base de datos va creciendo, las sentencias
son más complejas y confusas
 Es recomendable pasar por una etapa de tunning en la
que se revisan todas las sentencias SQL para poder
optimizarlas
 Tanto por cantidad como por complejidad, la mayoría de
las optimizaciones deben hacerse sobre sentencias
SELECT, ya que son (por regla general) las
responsables de la mayor pérdida de tiempos
 Optimización
 Normas básicas de optimización

Las condiciones (tanto de filtro como de join) deben
ir siempre en el orden en que esté definido el
índice. Si no hubiese índice por las columnas
utilizadas, se puede estudiar la posibilidad de
añadirlo, ya que tener índices extra sólo penaliza
los tiempos de inserción, actualización y borrado,
pero no de consulta

Al crear un restricción de tipo PRIMARY KEY o
UNIQUE, se crea automáticamente un índice sobre
esa columna

Para chequeos, siempre es mejor crear
restricciones (constraints) que disparadores
(triggers)
 Optimización
 Normas básicas de optimización

Hay que optimizar dos tipos de instrucciones
• Las que consumen mucho tiempo en ejecutarse
• Aquellas que no consumen mucho tiempo, pero que son
ejecutadas muchas veces

Generar un plan para todas las consultas de la
aplicación, poniendo especial cuidado en los planes
de las vistas, ya que estos serán incluidos en todas
las consultas que hagan referencia a la vista

Hay que tener especial cuidado de hacer joins
entre vistas
 Optimización
 Normas básicas de optimización

Si una aplicación que funcionaba rápido, se vuelve
lenta
• Hay que analizar los factores que han podido cambiar.
• Si el rendimiento se degrada con el tiempo, es posible
que sea un problema de volumen de datos, y sean
necesarios nuevos índices para acelerar las búsquedas.
• En otras ocasiones, añadir un índice equivocado puede
ralentizar ciertas búsquedas.
• Cuantos más índices tenga una tabla, más se tardará en
realizar inserciones y actualizaciones sobre la tabla,
aunque más rápidas serán las consultas.
• Hay que buscar un equilibrio entre el número de índices y
su efectividad, de tal modo que creemos el menos
número posible, pero sean utilizados el mayor número
de veces posible
 Optimización
 Normas básicas de optimización

Utilizar siempre que sea posible las mismas consultas. La
segunda vez que se ejecuta una consulta, se ahorrará
mucho tiempo de parsing y optimización, así que se debe
intentar utilizar las mismas consultas repetidas veces

Las consultas más utilizadas deben encapsularse en
procedimientos almacenados. Esto es debido a que el
procedimiento almacenado se compila y analiza una sola
vez, mientras que una consulta (o bloque PL/SQL)
lanzado a la base de datos debe ser analizado,
optimizado y compilado cada vez que se lanza

Los filtros de las consultas deben ser lo más específicos y
concretos posibles. Es decir: es mucho más específico
poner WHERE campo = 'a' que WHERE campo LIKE '%a
%'. Es muy recomendable utilizar siempre consultas que
filtren por la clave primaria u otros campos
indexados
 Optimización
 Normas básicas de optimización

Hay que tener cuidado con lanzar demasiadas consultas
de forma repetida, como por ejemplo dentro de un bucle,
cambiando una de las condiciones de filtrado. Siempre
que sea posible, se debe consultar a la base de datos una
sola vez, almacenar los resultados en la memoria del
cliente, y procesar estos resultados después. También se
pueden evitar estas situaciones con procedimientos
almacenados, o con consultas con parámetros acoplados
(bind)

Evitar la condiciones IN ( SELECT…) sustituyéndolas por
joins, cuando se utiliza un conjunto de valores en la
clausula IN, se traduce por una condición compuesta con
el operador OR. Esto es lento, ya que por cada fila debe
comprobar cada una de las condiciones simples
 Optimización
 Normas básicas de optimización

Cuando se hace una consulta multi-tabla con joins, el
orden en que se ponen las tablas en el FROM influye en
el plan de ejecución. Aquellas tablas que retornan más
filas deben ir en las primeras posiciones, mientras que las
tablas con pocas filas deben situarse al final de la lista de
tablas

Si en la cláusula WHERE se utilizan campos indexados
como argumentos de funciones, el índice quedará
desactivado. Ejemplo, si tenemos un índice por el campo
IMPORTE, y utilizamos una condición como WHERE
ROUND(IMPORTE) > 0, entonces el índice quedará
desactivado y no se utilizará para la consulta

Siempre que sea posible se deben evitar las funciones de
conversión de tipos de datos e intentar hacer siempre
comparaciones con campos del mismo tipo
 Optimización
 Normas básicas de optimización

Una condición negada con el operador NOT desactiva los
índices

Una consulta cualificada con la cláusula DISTINCT debe ser
ordenada por el servidor aunque no se incluya la cláusula
ORDER BY

Para comprobar si existen registros para cierta condición, no
se debe hacer un SELECT COUNT(*) FROM X WHERE
xxx, sino que se hace un SELECT DISTINCT 1 FROM X
WHERE xxx. De este modo evitamos al servidor que cuente
los registros

Si vamos a realizar una operación de inserción, borrado o
actualización masiva, es conveniente desactivar los índices,
ya que por cada operación individual se actualizarán los
datos de cada uno de los índices. Una vez terminada la
operación, volvemos a activar los índices para que se
regeneren
 Optimización
 Toda consulta SELECT se ejecuta dentro del
servidor en varios pasos. Para la misma
consulta, pueden existir distintas formas para
conseguir el mismo resultados, por lo que el
servidor es el responsable de decidir qué
camino seguir para conseguir el mejor tiempo
de respuesta. La parte de la base de datos que
se encarga de estas decisiones se llama
Optimizador. El camino seguido por el servidor
para la ejecución de una consulta se denomina
“Plan de ejecución”
 Optimización
 Optimizador basado en reglas (RULE)

Se basa en ciertas reglas para realizar las
consultas. Por ejemplo, si se filtra por un campo
indexado, se utilizará el índice, si la consulta
contiene un ORDER BY, la ordenación se hará
al final, etc.

No tiene en cuenta el estado actual de la base
de datos, ni el número de usuarios conectados,
ni la carga de datos de los objetos, etc.

Es un sistema de optimización estático, no varía
de un momento a otro
 Optimización
 Optimizador basado en costes (CHOOSE)

Se basa en las reglas básicas, pero teniendo en cuenta el
estado actual de la base de datos: cantidad de memoria
disponible, entradas/salidas, estado de la red, etc. Por
ejemplo, si se hace una consulta utilizando un campo
indexado, mirará primero el número de registros y si es
suficientemente grande, entonces merecerá la pena acceder
por el índice, si no, accederá directamente a la tabla.

Para averiguar el estado actual de la base de datos se basa
en los datos del catálogo público, por lo que es
recomendable que esté lo más actualizado posible (a través
de la sentencia ANALYZE), ya que de no ser así, se pueden
tomar decisiones a partir de datos desfasados (la tabla tenía
10 registros hace un mes pero ahora tiene 10.000)
 ALTER SESSION SET OPTIMIZER_GOAL =
[RULE|CHOOSE];
 Optimización
 Sugerencias o hints

Un hint es un comentario dentro de una consulta
SELECT que informa a Oracle del modo en que
tiene que trazar el plan de ejecución. Los hint
deben ir detrás de la palabra SELECT:
• SELECT /*+ HINT */ . . . A
 Optimización
 Sugerencias o hints
• /*+ CHOOSE */ Pone la consulta a costes.
• /*+ RULE */ Pone la consulta a reglas.
• /*+ ALL_ROWS */

Pone la consulta a costes y la optimiza para que devuelva todas
las filas en el menor tiempo posible. Es la opción por defecto del
optimizador basado en costes. Esto es apropiado para procesos
en masa, en los que son necesarias todas las filas para empezar
a trabajar con ellas
• /*+ FIRST_ROWS */

Pone la consulta a costes y la optimiza para conseguir que
devuelva la primera fila en el menor tiempo posible. Esto es
idóneo para procesos online, en los que podemos ir trabajando
con las primeras filas mientras se recupera el resto de
resultados. Este hint se desactivará si se utilizan funciones de
grupo como SUM, AVG, etc
 Optimización
 Sugerencias o hints
• SELECT /*+ HINT */ . . . A
• /*+ INDEX( tabla índice ) */

Fuerza la utilización del índice indicado para la tabla
indicada. Se puede indicar el nombre de un índice (se
utilizará ese índice), de varios índices (el optimizador
elegirá uno entre todos ellos) o de una tabla (se
utilizará cualquier índice de la tabla)
• /*+ ORDERED */

Hace que las combinaciones de las tablas se hagan
en el mismo orden en que aparecen en el join
 Optimización
 Calcular el coste de una consulta
• Para calcular el coste de una consulta, el
optimizador se basa en las estadísticas
almacenadas en el catálogo de Oracle, a través
de la instrucción:

ANALYZE [TABLE,INDEX] [COMPUTE,
ESTIMATE] STATISTICS;
 Optimización
 Calcular el coste de una consulta
• Si no existen datos estadísticos para un objeto (por ejemplo,
porque se acaba de crear), se utilizarán valores por defecto.
Además, si los datos estadísticos está anticuados, se corre el
riesgo de calcular costes basados en estadísticas incorrectas,
pudiendo ejecutarse planes de ejecución que a priori pueden
parecer mejores. Por esto, si se utiliza el optimizador basado
en costes, es muy importante analizar los objetos
periódicamente (como parte del mantenimiento de la base de
datos). Como las estadísticas van evolucionando en el tiempo
(ya que los objetos crecen o decrecen), el plan de ejecución
se va modificando para optimizarlo mejor a la situación actual
de la base de datos. El optimizador basado en reglas hacía lo
contrario: ejecutar siempre el mismo plan, independientemente
del tamaño de los objetos involucrados en la consulta. Dentro
de la optimización por costes, existen dos modos de
optimización, configurables desde el parámetro
OPTIMIZER_MODE
 Optimización
 Calcular el coste de una consulta
• FIRST_ROWS: utiliza sólo un número determinado
de filas para calcular los planes de ejecución. Este
método es más rápido pero puede dar resultados
imprecisos
• ALL_ROWS: utiliza todas las filas de la tabla a la
hora de calcular los posibles planes de ejecución.
Este método es más lento, pero asegura un plan de
ejecución muy preciso. Si no se indica lo contrario,
este es el método por defecto
 Optimización
 Plan de ejecución
• Aunque en la mayoría de los casos no hace falta
saber cómo ejecuta Oracle las consultas, existe una
sentencia especial que nos permite ver esta
información. El plan de ejecución nos proporciona
muchos datos que pueden ser útiles para averiguar
qué está ocurriendo al ejecutar una consulta, pero
principalmente, de lo que nos informa es del tipo de
optimizador utilizado, y el orden y modo de unir las
distintas tablas si la instrucción utiliza algún join
FIN