Oracle - pl/sql - rendimiento de bucles - loop performance

Carlos Garcia publica un post en el blog de Innova acerca de las diferencias de tiempo que obtiene al hacer el mismo número de iteraciones dentro de un bucle. Yo hice la prueba de rendimiento con diferentes formas de bucles, el mismo número de iteraciones y a poder ser el mismo código a ejecutar, que será básicamente un contador:

DECLARE
    i       NUMBER := 0;
    j       NUMBER := 0;
    b       BOOLEAN := FALSE;
    i_begin INTEGER;
    n_limit CONSTANT NUMBER := 100000000;
BEGIN
    i_begin := DBMS_UTILITY.GET_TIME;
    j := 0;
    FOR i IN 0..n_limit
    LOOP
        j := j + 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE (
           TO_CHAR((DBMS_UTILITY.GET_TIME - i_begin)/100)
        || '  FOR'
    );
    --
    i_begin := DBMS_UTILITY.GET_TIME;
    j := 0;
    LOOP
        j := j + 1;
        EXIT WHEN j = n_limit;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE (
           TO_CHAR((DBMS_UTILITY.GET_TIME - i_begin)/100)
        || '  LOOP EXIT WHEN'
    );
    --
    i_begin := DBMS_UTILITY.GET_TIME;
    j := 0;
    LOOP
        j := j + 1;
        IF j = n_limit
        THEN
            EXIT;
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE (
           TO_CHAR((DBMS_UTILITY.GET_TIME - i_begin)/100)
        || '  LOOP IF EXIT'
    );
    --
    i_begin := DBMS_UTILITY.GET_TIME;
    j := 0;
    WHILE TRUE
    LOOP
        j := j + 1;
        EXIT WHEN j = n_limit;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE (
           TO_CHAR((DBMS_UTILITY.GET_TIME - i_begin)/100)
        || '  WHILE TRUE EXIT WHEN'
    );
    --
    i_begin := DBMS_UTILITY.GET_TIME;
    j := 0;
    WHILE j < n_limit + 1
    LOOP
        j := j + 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE (
           TO_CHAR((DBMS_UTILITY.GET_TIME - i_begin)/100)
        || '  WHILE LOOP'
    );
    --
    i_begin := DBMS_UTILITY.GET_TIME;
    j := 0;
    WHILE b = FALSE
    LOOP
        j := j + 1;
        IF j = n_limit
        THEN
            b := TRUE;
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE (
           TO_CHAR((DBMS_UTILITY.GET_TIME - i_begin)/100)
        || '  WHILE LOOP IF THEN'
    );
END;

Se obtiene como resultado (en segundos):

10,48  FOR
12,58  LOOP EXIT WHEN
12,59  LOOP IF EXIT
12,61  WHILE TRUE EXIT WHEN
13,11  WHILE LOOP
16,61  WHILE LOOP IF THEN

el WHILE como se puede ver es el más lento de ejecutar, de todas maneras si el código que vas a ejecutar son sentencias DML yo hago lo que recomienda ORACLE y es utilizar el FORALL que es incluso más eficiente que el FOR.

11 Tuning PL/SQL Applications for Performance
http://www.stanford.edu/dept/itss/docs/oracle/10g/
appdev.101/b10807/12_tune.htm

Via:
http://www.balearsinnovacio.com/blog/?p=490

QEM - Quest Error Manager - maneja errores y excepciones en plsql

Quest Error Manager es un framework que te ayuda a estandarizar el manejo de los errores y excepciones en aplicaciones ORACLE PLSQL. Escritas por el bien conocido Steven Feuersteins, básicamente son 4 tablas (q$error_context, q$error, q$error_instance, q$log) y un package (q$error_manager), te descargas el archivo qem.zip descomprimes y solo tendras que ejecutar el script: qem$install.sql que te añadirá en tu esquema las tablas y el package, luego viene una demo y ejemplos para que puedas ver su funcionamiento.

Según Steven el QEM te ayudará en 2 areas:

1.- Definición y manejo de errores

2.- Tracear tu aplicación en tiempo de ejecución.

Si luego decides quitarlo de tu esquema Steven ya te creado el script qem$uninstall.sql

No dudo que cada uno ya ha creado y tiene montado un manejo de errores y excepciones en sus aplicaciones pero no estaria mal echarle un vistazo al código que siempre se puede aprender y más de Steven Feuersteins un Gurú del PLSQL.

Info:
http://www.toadworld.com/Knowledge/DatabaseKnowledge/
StevenFeuersteinsPLSQLObsession/MyPetProjectsandContributions/
QuestErrorManagerQEM/tabid/210/Default.aspx

Descargar:
http://www.oracleplsqlprogramming.com/downloads/qem.zip

Página de Steven Feuersteins:
http://www.stevenfeuerstein.com/

Tip - Trick - Oracle - PLSQL - mostrar el número de línea donde se produce el error

Esto ayuda a los que desarrollamos aplicaciones web con acceso a base de datos ORACLE. A partir de la versión de Oracle 10g, tenemos una función que devuelve una cadena de texto con el número de línea donde se ha producido un error, hacemos un ejemplo en la pestaña SQL Editor del TOAD con un bloque anónimo, en donde provocaremos un error comparando un valor númerico (NUMBER) con una cadena de texto:

El código de nuestro pequeño bloque anónimo es el siguiente:

DECLARE
   dummy NUMBER := 0;
BEGIN
    if dummy = 'alfa'
    then
        dbms_output.put_line('if');
    else
        dbms_output.put_line('else');
    end if;
EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line(sqlerrm);
      dbms_output.put_line(DBMS_UTILITY.format_error_backtrace);
END;

Lo que se muestra en el DBMS_OUTPUT es:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4

El SQLERRM como todos sabemos nos devuelve el mensaje de error, y DBMS_UTILITY.format_error_backtrace nos recupera el número de línea donde esta ocurriendo el error, si el código del EXCEPTION:

dbms_output.put_line(sqlerrm);
dbms_output.put_line(DBMS_UTILITY.format_error_backtrace);

estuviera en un paquete (package) el mensaje seria de la forma:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at “IDENTIFICADOR_PACKAGE.IDENTIFICADOR_PROCEDIMIENTO_FUNCION”, line 4

Esto es muy útil al momento de tracear errores en tiempo de ejecución y en el manejo de excepciones, ya que no solo sabemos la descripción del error sino que también sabemos el número de la línea de nuestro paquete donde sucedio.

Iron Man = Hardware de Marvel y Software de Oracle

Oracle da soporte a los superhéroes

Oracle co-promociona la película del conocido superhéroe Iron Man, con el estreno de la pelicula me entero que oracle tiene dedicado en su sitio web una sección de marvel, se puede ver el episodio 3 titulado Checkmate de la trilogia TWO WORLDS, en donde salen personajes de marvel y en la historia promocionan productos de oracle.

Episodio 3:
http://www.oracle.com/webapps/dialogue/dlgpage.jsp?
p_dlg_id=6179018&src=5634323&Act=188

Articulo de Oracle “Support for Superheroes” (en ingles):
http://www.oracle.com/technology/oramag/oracle/08-mar/o28marvel.html

Two Worlds: Unify to Lead (Building a Better Foundation for Business):
http://www.oracle.com/dm/08q4field/marvel_bk3_exec-guide.pdf

Web de la pelicula:
http://ironmanmovie.marvel.com/

Via:
An Expert’s Guide to Oracle Technology
http://blogs.ittoolbox.com/oracle/guide/archives/
oracle-11g-ironman-edition-24103

Polémica entre Techcrunch, Marvel y Oracle
http://lsnrctl.blogspot.com/2008/04/oracle-y-iron-man.html
http://www.techcrunch.com/2008/04/30/
goodness-prevails-iron-man-screening-is-on/

Tip - Trick - PLSQL - fecha con nombre del dia y del mes

Para recuperar el nombre del dia (es decir lunes, martes, miércoles, etc) usando la función to_char del plsql. recordemos un poco de teoria:

to_char( date, [ format_mask ], [ nls_language ] )

string: será la cadena de texto para convertirla a tipo fecha

format_mask: será la máscara que utilizaremos para convertir la cadena de texto

nls_language: es un parámetro opcional, es el lenguaje nls que utilizaremos para la conversión.

Supongamos que nuestro ejemplo toma como parámetro de entrada de fecha el SYSDATE

SELECT to_char(SYSDATE, 'dd-mm-yyyy'  FROM dual;

en el momento de escribir este post nos devuelve: 05-02-2008ahora agregamos el tercer parametro, nls_language es ‘NLS_DATE_LANGUAGE=spanish‘

para hacer la prueba podriamos hacer

SELECT to_char(SYSDATE, 'day dd-mm-yyyy', 'NLS_DATE_LANGUAGE=spanish'  FROM dual;

en el momento de escribir este post nos retorna: martes 05-02-2008Si haces:

SELECT to_char(SYSDATE, 'day dd-month-yyyy', 'NLS_DATE_LANGUAGE=spanish'  FROM dual;

nos retorna:martes 05-febrero -2008

Si deseas puedes cambiar el idioma:

SELECT to_char(SYSDATE, 'day dd-month-yyyy', 'NLS_DATE_LANGUAGE=english'  FROM dual;

SELECT to_char(SYSDATE, 'day dd-month-yyyy', 'NLS_DATE_LANGUAGE=french'  FROM dual;

SELECT to_char(SYSDATE, 'day dd-month-yyyy', 'NLS_DATE_LANGUAGE=german'  FROM dual;

Enlaces de referencia:
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96529/ch7.htm
http://www.techonthenet.com/oracle/functions/to_char.php