lunes, 22 de junio de 2009

SQL: Performance: Índice único NUMBER vs índice único VARCHAR2

¿Que es más eficiente en términos de velocidad de ejecución en consultas SQL? ¿Usar índices únicos del tipo number o usar varchar2? Hace unos días hice unas pruebas en una base de datos Oracle 10g R2 sobre CentOS 5 para ver que es más eficiente en términos de tiempos de búsqueda evaluando los tipos number versus varchar2. Al hacer búsquedas simples (usando solo una tabla) los planes de ejecución son equivalentes al usar índices únicos con una columna NUMBER(10) o si la columna indexada es VARCHAR2(10). Para preparar un ambiente de ejecución usé como base la tabla objetos (imagen de la tabla all_objects) creando dos columnas ID_OBJETO y COD_OBJETO e indexandolas separadamente con una clave única
  • Varios select usando la columna number
  • ALTER SESSION SET sql_trace = TRUE;
    SELECT id_objeto, cod_objeto, object_name, object_type
    FROM   mherrera.objetos
    WHERE  id_objeto = 22000;
    .....
    ALTER SESSION SET sql_trace=FALSE;
  • Varios select usando la columna varchar2
  • ALTER SESSION SET sql_trace = TRUE;
    SELECT id_objeto, cod_objeto, object_name, object_type
    FROM   mherrera.objetos
    WHERE  cod_objeto = '0000022000';
    .....
    ALTER SESSION SET sql_trace=FALSE;
  • Usando tkprof para evaluar la ejecución:
  • Select usando NUMBER
    SELECT id_objeto, cod_objeto, object_name, object_type
    FROM   mherrera.objetos
    WHERE  id_objeto = 22000
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        2      0.00       0.00          0          0          0           0
    Execute      2      0.00       0.00          0          0          0           0
    Fetch        4      0.00       0.00          0          6          0           2
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        8      0.00       0.00          0          6          0           2
    
    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 65  (MHERRERA)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          1  TABLE ACCESS BY INDEX ROWID OBJETOS (cr=3 pr=0 pw=0 time=62 us)
          1   INDEX UNIQUE SCAN UK01_OBJETOS (cr=2 pr=0 pw=0 time=33 us)(object id 68777)
    
    Rows     Execution Plan
    -------  ---------------------------------------------------
          0  SELECT STATEMENT   MODE: ALL_ROWS
          1   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'OBJETOS' (TABLE)
          1    INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'UK01_OBJETOS' (INDEX(UNIQUE))
  • Select usando VARCHAR2
  • SELECT id_objeto, cod_objeto, object_name, object_type
    FROM   mherrera.objetos
    WHERE  cod_objeto = '0000022000'
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        2      0.00       0.00          0          0          0           0
    Execute      2      0.00       0.00          0          0          0           0
    Fetch        4      0.00       0.00          0          6          0           2
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        8      0.00       0.00          0          6          0           2
    
    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 65  (MHERRERA)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          1  TABLE ACCESS BY INDEX ROWID OBJETOS (cr=3 pr=0 pw=0 time=46 us)
          1   INDEX UNIQUE SCAN UK02_OBJETOS (cr=2 pr=0 pw=0 time=25 us)(object id 68778)
    
    Rows     Execution Plan
    -------  ---------------------------------------------------
          0  SELECT STATEMENT   MODE: ALL_ROWS
          1   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'OBJETOS' (TABLE)
          1    INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'UK02_OBJETOS' (INDEX (UNIQUE))
    
  • Los planes son equivalentes y el uso de recursos y lecturas son los mismos, por lo tanto, la conclusión es que desde el punto de vista de velocidad de ejecución de una query, es lo mismo indexar por NUMBER o por VARCHAR2
  • Para ejecutar estas pruebas usé los siguientes scripts:
  • Crear escenario base
  • Varios select usando Number
  • Varios select usando Varchar2
  • Después, ejecute unas queries con un join entre dos tablas (Tipos_Objetos y Objetos)y los resultados son equivalentes. Para complementar el escenario anterior:
  • Complementa escenario con columnas Number
  • Select con Number
  • Complementa escenario con columnas Varchar2
  • Select con Varchar2
  • Los resultados generados por TRACE + TKPROF en este nuevo escenario:
  • Select con Number
  • Select con Varchar2
  • Contexto: Oracle 10g R2; SO: Centos 5.2
  • lunes, 8 de junio de 2009

    PL/SQL: Performance: Cuando usar BULK

    En varias oportunidades me he encontrado con alguien que me consulta cual es la mejor forma de utilizar las operaciones Bulk en PL/SQL. En general, la receta es: Si la operación se puede realizar en el motor SQL, hágalo allá; si necesita traer los registros al motor PL/SQL, use Bulk. Por ejemplo, si solo necesita hacer una copia de registros de una tabla a otra, usando algunas operaciones básicas, la operación INSERT/SELECT es mucho más eficiente que llevar los registros al motor PL/SQL y hacer un bulk insert:
  • Caso 1: Insert / Select
  • INSERT INTO resumen_creaciones
    SELECT   TRUNC (fecha_creacion, 'MONTH') AS mes,
        tipo_objeto,
        SUM (total_objetos) AS total_mensual
    FROM     objetos_creados
    GROUP BY TRUNC (fecha_creacion, 'MONTH'), tipo_objeto;
    
  • Caso 2: Bulk Select / Bulk Insert
  • .....
    SELECT   TRUNC (fecha_creacion, 'MONTH'), tipo_objeto, SUM (total_objetos)
    BULK COLLECT INTO v_mes, v_tipo_objeto, v_total_mensual
    FROM     objetos_creados
    GROUP BY TRUNC (fecha_creacion, 'MONTH'), tipo_objeto;
    
    FORALL i IN v_mes.FIRST .. v_mes.LAST
    INSERT INTO resumen_creaciones
           (mes, tipo_objeto, total_mensual
           )
    VALUES      (v_mes (i), v_tipo_objeto (i), v_total_mensual (i)
           );
    ....
    
  • Para el caso 1:
  • 
    OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        2      0.01       0.00          0          0          0           0
    Execute      2      0.00       0.00          3          8         25          38
    Fetch        1      0.00       0.00          0          3          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        5      0.01       0.00          3         11         25          39
    
  • Para el caso 2:
  • 
    OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        3      0.00       0.00          0          0          0           0
    Execute      3      0.00       0.01          3          5         25          38
    Fetch        2      0.00       0.00          0          6          0          39
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        8      0.00       0.01          3         11         25          77
    
    Beneficios de operaciones SQL:
    1. Generalmente son mucho más simples de implementar
    2. Producen menos parsing
    3. El motor SQL implementa estas operaciones con muchos menos pasos
    4. No hay intercambio de registros entre los motores PL/SQL y SQL
    5. Más fáciles de mantener
    Cuando usar PL/SQL+Colecciones+Procesos Bulk
    1. Si es necesario tener el control de errores por registro (p.e para ETL)
    2. Es muy grande el volumen de datos a procesar, mejor, dividir para conquistar. En este caso se puede usar BULK con LIMIT y procesar en grupos limitados de registros
    3. La query o DML es demasiado compleja para mantenerla como una sola instrucción
    4. Si la operación no se puede implementar solo con SQL ya que necesita instrucciones procedurales
    Los ejemplos completos se pueden ver en Contexto: Oracle 10g R2 en Centos 5.2

    lunes, 4 de mayo de 2009

    PL/SQL: Performance: SQL Nativo Dinámico vs DBMS_SQL

    Para ejecutar instrucciones SQL (DML y DDL) en forma dinámica se puede usar "SQL Nativo Dinámico" (Native Dynamic SQL) o el package DBMS_SQL. En general, el primero tiene mejor rendimiento que el DBMS_SQL, sin embargo, hay oportunidades en que el package DBMS_SQL es una mejor opción desde el punto de vista del rendimiento. Esto se debe a que usando el DBMS_SQL es necesario hacer el análisis o "parsing" de la instrucción solo una vez, en cambio, el "SQL Nativo Dinámico" hace este análisis cada vez que es invocada la instrucción. Una prueba ejecutada en una base de datos Oracle 9i sobre W2k3 nos muestra lo siguiente:
    SQL Nativo Dinámico:Tot.Ciclos:20.000:Tiempo total:13 segundos.
    DBMS_SQL:Tot.Ciclos:20.000:Tiempo total:6 segundos.
    
    En este caso, el tiempo de ejecución es de 2:1. Esto es válido para versiones 8i y 9i. En 10g hay una mejora con respecto a la ejecución de NDS.

    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