domingo, 19 de abril de 2009

PL/SQL: Performance: Paso de parámetros con NOCOPY

Los parámetros en los programas PL/SQL en modo IN pasan por referencia, en cambio, los parámetros en modo OUT o IN OUT pasan por "valor", es decir, se hace una copia completa de la(s) variable(s)al hacer la llamada (runtime del PL/SQL). Esto implica tiempo y recursos, sobre todo cuando un programa pasa una estructura "pesada" y es llamado muchas veces. En la mayoría de los casos esto se puede mejorar usando NOCOPY. Para mostrar esto, podemos usar una colección (arreglo en otros lenguajes) de registros y declarar una variable que será pasada como parámetro de entrada/salida (IN OUT) y tomar los tiempos de ejecución sin y con NOCOPY
   PROCEDURE parametros_x_valor ( 
      p_productos   IN OUT   productos_ct
   );

   PROCEDURE parametros_x_referencia (
      p_productos   IN OUT NOCOPY   productos_ct
   );
Ver ejemplo: nocopy.sql El resultado en tiempo es:
Carga de colección con:100000 regs:Tiempo:0 segundos.
Tiempo Parámetro x Valor:Tiempo:29 segundos.
Tiempo Parámetro x Referencia:Tiempo:6 segundos.
Observación: Si un programa recibe variables por referencia y termina con error por una excepción, los valores modificados persisten en el procedimiento que efectuó la llamada y esto puede producir problemas colaterales si el proceso continúa.

PL/SQL: Performance: Verificar existencia de un registro

Para verificar la existencia de un registro es mucho mejor usar una función ad-hoc existe, que verifique la existencia en vez de contar la cantidad de registros con un select count y preguntar si la cantidad de registros es mayor que cero. Esto es más dramático cuando usamos un where por un campo con valor no único o una clausula like. Por ejemplo, si deseamos saber si existe un registro con tipo de objeto = 'SYNONYM' , una alternativa (generalmente usada) es hacer:
SELECT COUNT (*)
FROM   objetos
WHERE  object_type = 'SYNONYM';
Una mejor alternativa es hacer una función:

FUNCTION existe (
   p_object_type   IN   objetos.object_type%TYPE
)
   RETURN BOOLEAN IS
   CURSOR c_existe IS
      SELECT 'x'
      FROM   objetos
      WHERE  object_type = p_object_type;

   v_dummy    VARCHAR2 (1);
   v_existe   BOOLEAN;
BEGIN
   OPEN c_existe;

   FETCH c_existe
   INTO  v_dummy;

   v_existe := c_existe%FOUND;

   CLOSE c_existe;

   RETURN v_existe;
END existe;
El primer caso recorre todos los registros, en el segundo caso, solo llega al primer registro y retorna la existencia. Si creamos un bloque anónimo con llamadas iterativas a estas dos instrucciones, tenemos que el tiempo de ejecución es mucho menor para el código ad-hoc eEvalua_Count_vs_Existencia.sql
  • La salida de este script es:
  • 
    Select vía Count:Tiempo en segundos:TF-TI:147 segundos.
    Función existe:Tiempo en segundos:TF-TI:2 segundos.
    
    Por lo tanto, el tiempo de respuesta y el uso de recursos son mucho mejores en el segundo caso. Además, la función de existencia es fácil de implementar programáticamente usando el diccionario de la base de datos.

    viernes, 3 de abril de 2009

    jueves, 2 de abril de 2009

    PL/SQL: Performance: Operaciones Bulk

    El lenguaje PL/SQL es muy poderoso pero creo que usamos un porcentaje muy bajo de las capacidades que tiene. Por ejemplo, el uso de bulk en sentencias SQL es muy poco aprovechado. Adjunto un ejemplo donde se nota la diferencia entre el uso de un cursor normal y el uso del bulk. Extrapolando, si el cursor se demora 2 minutos en ejecutar, el bulk puede demorarse hasta 5 segundos aproximadamente. Ver ejemplo forall_insert.txt La salida de la ejecución de este script es: forall_insert.out.txt El resultado de la ejecución es:
    ***************************
    Execution Time (secs)                                                   
    ---------------------                                                   
    Total regs:250.000                                                      
    FOR LOOP: 130
    FORALL: 5
    ***************************
    El tiempo total para insertar 250000 registros usando insert-cursor es de 130 segundos, en cambio, el bulk insert se demoró solo 5 segundos. Observación: El uso de colecciones consume memoria del servidor así que hay que jugar entre el uso de recursos y el tiempo de ejecución