Introducción

SQL es un lenguaje declarativo en el sentido de que sólo los datos de interés se describen en el programa (y no el algoritmo necesario para obtener dichos datos). Por lo tanto, suelen existir diversas formas de cumplir con una instrucción SQL. A estas diferentes formas de lograr esto se las conoce como rutas de acceso o planes de acceso. Para no complicar el tema, este artículo hace referencia a una forma en particular de cumplir con una instrucción SQL como una ruta de acceso. Aunque diferentes rutas de acceso para la misma instrucción SQL producen el mismo conjunto de resultados, lo más probable es que no realicen la tarea con el mismo nivel de rendimiento. El compilador SQL usa la optimización de consultas para seleccionar la ruta de acceso que tenga el mejor rendimiento y usarla con una instrucción SQL en particular (por supuesto que dentro de un plazo de tiempo razonable).

Si usted es desarrollador, administrador de bases de datos o especialista en puesta a punto de consultas, es fundamental que comprenda los aspectos básicos de las rutas de acceso para así poder poner a punto las consultas y las cargas de trabajo de manera precisa antes de que ocasionen problemas en su entorno de producción. Estos conocimientos básicos, en conjunto con los consejos de visualización y puesta a punto provistos por las soluciones de puesta a punto de consultas de IBM Optim, lo pueden ayudar a realizar esta tarea de manera más eficiente.

Luego de la introducción a la ejecución de consultas y a las rutas de acceso, este artículo describe los métodos de unión y los métodos de acceso más comunes soportados por DB2® para z/OS. Luego de esto, este artículo describe una ruta de acceso seleccionada por el optimizador de DB2 para z/OS y le presenta el gráfico de ruta de acceso para mostrarle toda la información crítica relacionada con la selección de la ruta de acceso. Este artículo concluye con un escenario de muestra que le enseña a usar la anotación de consultas, una función de Optim Query Tuner que lo ayuda con el análisis de consultas mediante la provisión de información estadística vital directamente en la instrucción SQL.

Si usted quiere tener la oportunidad de interactuar con los diagramas de ruta de acceso, se los incluye en un archivo de proyecto de muestra que podrá encontrar en la sección "Descargas" de este artículo. Usted puede importar el archivo de proyecto a Data Studio (un paquete independiente con el Fix Pack 1 o posterior) o a cualquiera de los productos de Optim Query Tuner. No es necesario que usted esté conectado con la base de datos para interactuar con los resultados del análisis, que se encuentran rotulados de manera tal que se correspondan con las cifras que figuran en este artículo.

Para importar el proyecto de muestra, haga lo siguiente:

1. Abra Data Perspective (Perspectiva de datos) en Data Studio o en el producto de Optim Query Tuner.
2. Seleccione File (Archivo) > Import... (Importar...).
3. En el Import Wizard (Asistente de importación), navegue a Query Tuner > Projects (Proyectos) y, luego de esto, haga clic en Next (Siguiente).
4. Haga clic en Browse... (Navegar) y seleccione el directorio que incluye el archivo zip descargado. Esto hace que se visualice una lista de proyectos en la ventana Projects (Proyectos).
5. Seleccione sampleaccesspathproject y haga clic en Finish (Finalizar).
6. El proyecto de muestra debería aparecer en su Project Explorer (Explorador de proyectos). Si no puede ver ninguna ventana de Project Explorer, asegúrese de encontrarse en Data Perspective y seleccione Window (Ventana) > Reset Perspective (Restablecer perspectiva). De manera alternativa, usted puede seleccionar Window > Show View (Mostrar vista) > Project Explorer.

Sobre las soluciones de puesta a punto de consultas Optim

Las soluciones de puesta a punto de consultas Optim ofrecen un entorno apropiado para identificar y poner a punto aquellas instrucciones SQL que tengan un bajo rendimiento por medio de asesores y herramientas que lo ayudarán a encontrar una solución adecuada. Las capacidades de puesta a punto de consultas se entregan como parte de los siguientes productos:

• Hay capacidades de formateo de consultas y puesta a punto de consultas únicas y básicas en Data Studio 2.2.0.1. Este producto está disponible de manera gratuita tanto para DB2 para z/OS como para DB2 para Linux®, UNIX® y Windows®. Tenga en cuenta que, si bien la información que figura en esta serie de artículos le explica cómo usar Data Studio para interpretar los gráficos de ruta de acceso, no todas las capacidades que se describen se encuentran disponibles en Data Studio.
• El formateo de consultas y la puesta a punto de consultas únicas, al igual que el conjunto más grande de asesores, están disponibles en Optim Query Tuner. Este producto está disponible tanto para DB2 para z/OS como para DB2 para Linux, UNIX y Windows.
• La puesta a punto de la carga de trabajo de consultas, la puesta a punto de consultas únicas y todo el conjunto de asesores están disponibles en Optim Query Workload Tuner. Este producto sólo está disponible para DB2 para z/OS (desde el momento en el que se redactó este artículo).

A los fines de la brevedad, esta serie de artículos usa el nombre Optim Query Tuner para referirse al conjunto de asesores y herramientas que le ofrecen las soluciones de puesta a punto de consultas Optim. Siempre que sea aplicable, se incluyen los nombres de los productos específicos cuando se describen las capacidades que pueden no llegar a estar disponibles en todos los productos que se mencionan con anterioridad.

Volver arriba

¿Cómo se lee un gráfico de ruta de acceso?

Un gráfico de ruta de acceso no sólo describe los "detalles operacionales" de la ejecución de una consulta sino que también describe la forma en la que fluyen los datos. Un nodo hoja de un gráfico de ruta de acceso es un nodo tabla, un nodo archivo de trabajo o un nodo índice que representa una fuente de datos en el plan de ejecución de la consulta (la Figura 1 muestra ejemplos de cada uno de estos tipos de nodos). Los datos fluyen desde abajo hacia arriba (como se puede observar en el gráfico) y se los procesa por medio de nodos de operación en el gráfico de ruta de acceso. La Tabla 1 describe la entrada, la salida y la función de varios nodos de operación, que suelen aparecen en un gráfico de ruta de acceso de Optim Query Tuner.

Volver arriba

Rutas de acceso y ejecución de consultas

La forma más fácil de describir las rutas de acceso consiste en usar un ejemplo. La instrucción SQL que figura en el Listado 1 une tres tablas para producir un informe de ventas. El monto de venta se agrega basándose en el sexo y en el estado civil de los clientes.

        select c.gender_code, c.marital_status_code, sum(od.unit_cost * cust_quantity)
        from cust_customer c, cust_order_header oh, cust_order_detail od
        where c.cust_code = oh.cust_code
        and oh.cust_order_number = od.cust_order_number
        and c.cust_prov_state_code = 'CA'
        and od.product_number in (154110, 129170, 129150, 129110, 129140, 130130)
        group by c.gender_code, c.marital_status_code
            

De manera intuitiva, una ruta de acceso es una descripción del procedimiento de ejecución de consultas que incluye tres componentes:

• La secuencia de unión de las tablas
• El algoritmo por medio del que se escanea una tabla (método de acceso)
• El algoritmo por medio del que se realiza una operación de unión (método de unión)

La Figura 1 es el gráfico de ruta de acceso generado por Optim Query Tuner para la consulta que figura en el Listado 1.

(Vea una versión ampliada de la Figura 1.)

Cada una de las tres tablas a las que se hace referencia en la consulta figura en el gráfico como un nodo tabla con sus nombres de tabla correspondientes.

La tabla principal en la secuencia es CUST_ORDER_DETAIL, que se escanea por medio de un índice definido a través de la columna PRODUCT_NUMBER. Este índice está representado en el gráfico por medio del nodo índice rotulado con el siguiente nombre de índice: SQT01_CUST_ORDER_DETAIL.

Directamente sobre el nodo índice SQT01_CUST_ORDER_DETAIL, se encuentra el nodo IXSCAN. Esto significa que se atraviesa el índice para obtener los identificadores de registro de calificación (RIDs). Luego, el nodo FETCH toma los RIDs de calificación para capturar las páginas de datos y los registros correspondientes en el espacio de tabla y colocarlos en el grupo de buffers de DB2.

La segunda tabla en la secuencia de unión es CUST_ORDER_HEADER, que se escanea por medio de un índice en la columna de unión denominada CUST_ORDER_NUMBER. El método de acceso para CUST_ORDER_HEADER, escaneo del índice, se representa de la misma forma que la tabla CUST_ORDER_DETAIL.

La última tabla en la secuencia de unión, CUST_CUSTOMER, también se escanea por medio de un índice definido en la columna CUST_CODE.

Ahora que ya conoce las generalidades de las tablas y de cómo se accede a la información (por medio de índices en este caso), usted está listo para analizar los métodos de unión que se usan más en detalle. El nodo de unión en el gráfico le muestra que el optimizador de DB2 selecciona una unión de bucle anidado (NLJOIN) para la primera operación de unión entre CUST_ORDER_DETAIL y CUST_ORDER_HEADER. La unión toma el conjunto resultante de la primera tabla (CUST_ORDER_DETAIL) y el conjunto resultante de la segunda tabla (CUST_ORDER_HEADER) como datos de entrada. El resultado de dicha operación de unión se transforma en la primera entrada para la segunda operación de unión (también como una unión de bucle anidado). Se la une al conjunto resultante de la última tabla en la secuencia de unión denominada CUST_CUSTOMER.

Comprender el flujo de datos es importante porque es el factor más influyente en lo que se refiere al rendimiento de la consulta al momento de ejecutar una consulta. El resto de esta sección le presenta el gráfico de ruta de acceso una vez más a partir de la perspectiva del flujo de datos para que usted pueda comprender por completo todo lo relativo al rendimiento al momento de ejecutar consultas.

El número que figura en cada nodo es la estimación del optimizador correspondiente a la cardinalidad de la fuente de datos subyacente o del nodo de operación. Por ejemplo, el número 273 en el nodo índice para SQT01_CUST_ORDER_DETAIL indica que existen 273 claves de índice distintas. CUST_ORDER_HEADER_PK es un índice único. Por lo tanto, la cantidad de claves de índice se corresponde exactamente con la cantidad de registros (539.526) en la tabla correspondiente. Esto también es así en el caso del índice IDX_CUST_CUSTOMER y la tabla CUST_CUSTOMER (que tienen 31.284 claves de índice y registros respectivamente).

En el caso de los índices que no son únicos, la cantidad de claves de índice es menor que la cantidad de registros. Además, la proporción entre la cantidad de registros y la cantidad de claves de índice indica, en promedio, la cantidad de RIDs asociadas con cada clave de índice. Los números que aparecen en el árbol Nodo tabla en el gráfico indican el tamaño de la tabla en lo que se refiere a la cantidad de registros.

Esto es lo que ocurre en el momento de ejecución para la primera parte de la unión de bucle anidado:

1. La operación de escaneo de índice (IXSCAN) relativa a BQT01_CUST_ORDER_DETAIL se lleva a cabo en primer lugar, ya que se trata de la primera operación para la primera tabla en la secuencia de unión. Basándose en la estimación del optimizador, existen ocho RIDs de calificación dentro del rango de clave de índice objetivo.
2. El nodo de búsqueda usa estos ocho RIDs de calificación para localizar la página y los registros correspondientes en el espacio de la tabla, que incluye 560.273 registros. Como no existen predicados adicionales que la operación de búsqueda deba aplicar, la cardinalidad de los datos de salida del nodo de búsqueda también es ocho. Esto significa que la operación de búsqueda producirá ocho registros de calificación.
3. Luego, se toman estos ocho registros de calificación como la primera entrada al nodo NLJOIN inmediatamente sobre el nodo FETCH. Debido a la forma en la que funcionan las uniones de bucle anidado, la segunda parte (existen cuatro nodos) se ejecuta por registro de tabla externo. Esto significa que la tabla interna de la operación de unión se escaneará ocho veces (una vez por cada uno de los ocho registros de calificación desde la entrada externa).

La segunda parte del nodo NLJOIN es similar a la de la primera tabla:

1. El optimizador selecciona un índice único (CUST_ORDER_HEADER_PK) para acceder a los registros que se encuentran en la tabla CUST_ORDER_HEADER.
2. Como lo indica la cantidad en el nodo IXSCAN y en el nodo FETCH, se encuentra un registro en CUST_ORDER_HEADER cada vez que se lleva a cabo un escaneo del índice. Por lo tanto, el nodo NLJOIN producirá una cantidad total de ocho registros.

De manera similar, la última parte del gráfico de ruta de acceso se evalúa ocho veces (una vez por cada registro producido por el primer nodo NLJOIN). Como se selecciona un solo índice para el nodo IXSCAN (IDX_CUST_CUSTOMER), se encontrará correspondencia con un solo registro en cada escaneo de la tabla CUST_CUSTOMER. Por lo tanto, se produce una cantidad total de ocho registros en el conjunto de resultados finales.

Desde un punto de vista del rendimiento, una ruta de acceso de buen rendimiento debería tocar la menor cantidad de datos con respecto al conjunto de resultados finales. En el ejemplo que figura con anterioridad, se minimiza la cantidad de registros capturados en cada tabla. En el caso de la tabla CUST_ORDER_DETAIL, sólo se capturan ocho registros en el espacio de la tabla usando el escaneo del índice. Además, cada sondeo en la entrada interna de ambas operaciones NLJOIN sólo se corresponde con un registro. Por lo tanto, si la estimación del optimizador es precisa, esta ruta de acceso debería resultar ser una muy eficiente ruta de acceso. (La sección de este artículo titulada "Un caso de estudio que usa la anotación de consulta" describe cómo usted puede determinar si la estimación es precisa.)

Volver arriba

Otros métodos de acceso y otros métodos de unión

En la sección anterior, usted aprendió sobre un tipo de método de acceso, el escaneo de índice (IXSCAN), y sobre un tipo de método de unión, la unión de bucle anidado (NLJOIN). Esta sección abarca otros métodos de acceso, métodos de unión y operaciones de ordenamiento. La descripción de cada operación incluye un gráfico de ruta de acceso de Query Tuner que explica su semántica operacional.

Escaneo de espacio de tabla (TBSCAN) con predicados stage-1 y stage-2

La instrucción SQL que figura en el Listado 2 crea el gráfico de ruta de acceso que aparece a la derecha de la captura de pantalla de Optim Query Tuner que se puede observar en la Figura 2.

select count(*) from cust_order_header where
                cust_total_quantity > 3 and cust_sales_tax > cust_total * 0.03

(Vea una versión ampliada de la Figura 2.)

La Figura 2 le muestra un ejemplo de cómo puede visualizar descripciones detalladas de dos nodos (la tabla CUST_ORDER_HEADER y la operación de escaneo de tabla (TBSCAN)) desde el gráfico de ruta de acceso. Usted puede obtener descripciones detalladas sobre el gráfico desde cualquier nodo simplemente haciendo clic derecho en el nodo y seleccionando Show Description (Mostrar descripción) en el menú de contexto. En la Figura 2, el descriptor para el nodo tabla y el descriptor para el nodo TBSCAN aparecen a la izquierda del gráfico de ruta de acceso. La instrucción SQL aparece en la parte inferior del diagrama.

La ruta de acceso le muestra que la operación TBSCAN se realiza en la tabla CUST_ORDER_HEADER. Esto significa que DB2 captura todas las páginas de datos en el espacio de la tabla de manera secuencial en el grupo de buffers. Luego de esto, se escanea cada registro en el grupo de buffers para aplicar el predicado stage-1. Los registros de calificación (sólo las columnas relevantes) se copian en un grupo de memoria privada para que se los filtre por medio de un predicado stage-2. La función de agregado, COUNT, se aplica a los registros que permanecen luego de efectuar el filtrado con el predicado stage-2.

Los detalles relativos al flujo de datos se pueden encontrar en la sección de atributos del descriptor TBSCAN. Existen 539.526 registros escaneados por DB2 en el grupo de buffers (el valor Input Cardinality [Cardinalidad de entrada] en el descriptor TBSCAN). El optimizador de DB2 estima que 473.785,44 registros sobrevivirían al predicado stage-1 (vea el atributo Stage 1 Returned Rows [Filas devueltas – Etapa 1] en el descriptor) y 4.737,9688 registros sobrevivirían al predicado stage-2 (vea el atributo Stage 2 Returned Rows [Filas devueltas – Etapa 2] en el descriptor). Sólo se produce un registro, debido a la función COUNT. El atributo Prefetch (Captura previa) en el descriptor incluye el valor "S", que indica que la captura previa secuencia comenzará en el momento de ejecución para así mejorar el rendimiento de las E/S para capturar las páginas de datos desde el espacio de tabla.

Escaneo de la tabla (TBSCAN) con recorte de partición

La instrucción SQL que figura en el Listado 3 crea el gráfico de ruta de acceso que aparece en la Figura 3.

select count(*) from cust_order_header where
                cust_total_quantity > 3 and cust_sales_tax > cust_total * 0.03 and
                cust_order_number between 100000 and 580000

(Vea una versión ampliada de la Figura 3.)

Como se puede observar en el descriptor del Nodo tabla, existen 10 particiones en el espacio de tabla y la clave de limitación para la novena partición es 625.000. Como existe un predicado, cust_order_number between 100000 and 580000, en la columna de particionamiento, el optimizador puede limitar el TBSCAN a sólo las primeras nueve particiones. Esta optimización se describe en el descriptor TBSCAN. El atributo Page_Ranges incluye un rango, rango 1, que abarca de la partición 1 a la partición 9. En el momento de la ejecución, se saltea completamente la partición 10 para lograr un mejor rendimiento.

Escaneo de índice (IXSCAN) con predicados coincidentes

La instrucción SQL que figura en el Listado 4 crea el gráfico de ruta de acceso que aparece en la Figura 4.

        select crdt_method_code, cust_total_quantity, count(*)
        from cust_order_header 
        where cust_total_quantity - 2 > 1 
        and crdt_method_code > 20 
        and cust_order_date > '2007-01-01-01.24.58.017000' 
        group by crdt_method_code, cust_total_quantity
            

(Tenga en cuenta que, en el ejemplo que se incluye con anterioridad, la columna cust_order_date se define con un tipo de TIMESTAMP.)

(Vea una versión ampliada de la Figura 4.)

Generalmente, Query Tuner muestra un escaneo de índice como un grupo de cuatro nodos en el gráfico de ruta de acceso. Como se puede observar en la Figura 4, se accede a CUST_ORDER_HEADER a través del escaneo de índice coincidente con el índice BQT01_CUST_ORDER_HEADER, que se define en la columna CRDT_METHOD_CODE (usted puede observar esto expandiendo el descriptor IXSCAN un poco más de lo que se puede observar en la Figura 4). El descriptor IXSCAN detalla que existe un predicado coincidente con un factor de filtrado del 7,32%, lo que nos lleva a una estimación de 114 índices y 3,9471 RIDs, lo que nos da un total de 1.558 índices y 539.526 RIDs respectivamente.

El nodo FETCH usa estos RIDs para posicionar las páginas de datos correspondientes en el espacio de tabla. Luego de que se los captura en los grupos de buffers de DB2, los registros en estas páginas se escanean y se aplica el predicado stage-1. La descripción detallada del nodo FETCH le muestra que se estima que 18.515,602 registros sobreviven al predicado stage-1. Se aplica el predicado stage-2 luego de copiar las columnas relevantes de estos registros en buffers privados en DB2. Como resultado de esto, el nodo FETCH devuelve 6.172,508 registros.

Escaneo de índice no coincidente (IXSCAN)

La instrucción SQL que figura en el Listado 5 crea el gráfico de ruta de acceso que se puede observar en la Figura 5.

select count(*) as count from cust_order_header

(Vea una versión ampliada de la Figura 5.)

Un escaneo de índice puede ser no coincidente, lo que significa que no existen restricciones en el escaneo del índice subyacente. Esto quiere decir que DB2 escanea todas las páginas hoja. Generalmente, el optimizador elige un escaneo de índice no coincidente cuando el índice subyacente le ofrece beneficios (como, por ejemplo, el acceso sólo al índice, la evasión del orden o algún otro beneficio similar).

En el ejemplo que aparece en la Figura 5, el optimizador decide explotar el escaneo de índice no coincidente porque la función de agregado, count(*), se puede evaluar escaneando sólo el índice. Además, el índice suele ser más pequeño que la tabla y, por lo tanto, su escaneo es más eficiente. La descripción detallada del nodo IXSCAN le muestra que se escanean todas las páginas hoja (1.680) debido a la falta de un predicado coincidente. No aparece ningún nodo FETCH o tabla en el gráfico de ruta de acceso debido a que se trata de un escaneo sólo del índice.

Unión de bucle anidado (NLJOIN)

La instrucción SQL que figura en el Listado 6 crea el gráfico de ruta de acceso que se puede observar en la Figura 6.

    select oh.cust_code, sum(od.cust_quantity * od.cust_unit_price)
    from cust_order_header oh, cust_order_detail od
    where oh.cust_order_number = od.cust_order_number
    and od.product_number in (154110, 129170, 129150, 129110, 129140, 130130)
    group by oh.cust_code
            

(Vea una versión ampliada de la Figura 6.)

Las uniones de bucle anidado (NLJOIN) toman dos fuentes de datos como entrada, unen los registros desde el lado externo (izquierdo) con los registros coincidentes en el lado interno (derecho) que califica los predicados unidos y, luego de esto, devuelve los registros unidos como datos de salida. Para mayor información sobre las uniones de bucle anidado, haga clic en el vínculo del centro de información adecuado en la sección Recursos para su plataforma DB2.

Como se puede observar en el gráfico de ruta de acceso de la consulta de muestra que aparece en la Figura 6, los registros de calificación de tabla externa (CUST_ORDER_DETAIL) se unen a los registros de calificación de tabla interna (CUST_ORDER_HEADER) usando NLJOIN y un predicado de unión EQUAL. En el momento de la ejecución, para cada uno de los registros que fluye desde el nodo FETCH en el lado externo de la unión, se escanea la tabla interna usando un escaneo de índice (IXSCAN) sobre el índice CUST_ORDER_HEADER_PK.

La información detallada que figura en el descriptor NLJOIN (que también aparece en la Figura 6) le muestra que, según la estimación del optimizador:

• Existen ocho registros de calificación luego de aplicar el predicado local (el valor de Outer Input Cardinality [Cardinalidad de entrada externa] es 8).
• Un registro de la tabla interna se corresponderá con cada registro de calificación de la tabla externa (el valor de Inner Input Cardinality [Cardinalidad de entrada interna] es 1).
• Finalmente, el nodo NLJOIN producirá ocho registros unidos como los datos de salida (el valor de Output Cardinality [Cardinalidad de salida] es 8).

Desde la perspectiva del rendimiento, a ésta se la considera como una operación de unión muy eficiente, ya que el costo de escanear la tabla interna es óptimo por medio del acceso único a través de un índice único.

Unión de bucle anidado (NLJOIN) con compuesto de orden

Tenga en cuenta que la instrucción SQL que figura en el Listado 7 es la misma que la que se puede observar en el Listado 6 de la sección anterior. Sin embargo, en el caso de este ejemplo, se selecciona una ruta de acceso diferente, como se puede observar en la Figura 7.

   select oh.cust_code, sum(od.cust_quantity * od.cust_unit_price)
   from cust_order_header oh, cust_order_detail od
   where oh.cust_order_number = od.cust_order_number
   and od.product_number in (154110, 129170, 129150, 129110, 129140, 130130)
   group by oh.cust_code
            

(Vea una versión ampliada de la Figura 7.)

La diferencia entre la Figura 6 y la Figura 7 es que, en la Figura 7, se agrega un nodo SORT en el lado externo de la unión. A este tipo de ruta de acceso se la denomina unión de bucle anidado (NLJOIN) con compuesto de orden.

A continuación, se explica por qué el optimizador agregaría un nodo SORT. Tenga en cuenta que existe una diferencia significativa con la cardinalidad que se puede observar en el nodo fetch en el lado externo de la unión en la Figura 7. El ejemplo que se puede observar en la Figura 6 sólo tenía ocho registros. Pero en el caso de la Figura 7, la estimación del optimizador con respecto a la cantidad de registros de calificación es 8.708,988. Cuanto mayor sea la cardinalidad, mayor será la cantidad de sondeos en la tabla interna: una por cada registro de calificación (externo). El índice explotado para la tabla interna resulta ser un índice agrupado en clústeres. Por lo tanto, el orden de los sondeos consecutivos en la tabla interna podría impactar sobre el rendimiento de las E/S de la tabla interna de manera significativa. Más precisamente, si los sondeos consecutivos de la tabla interna están en el orden CUST_ORDER_NUMBER, el índice interno se escaneará de manera secuencial a lo largo de diferentes sondeos que, ordenadamente, presentarán el escaneo de la tabla interna de manera secuencial. El beneficio que se obtiene gracias a contar con E/S más eficientes, supera el costo que tiene ordenar aproximadamente 8.708 registros. Por lo tanto, en la Figura 7, el optimizador decide ordenar el compuesto para la unión de bucle anidado.

En términos generales, una unión de bucle anidado con compuesto de orden saca provecho del optimizador si el índice interno se encuentra bien agrupado en clústeres y se espera que la tabla interna sea sondeada en varias ocasiones.

Unión de escaneo de fusión (MSJOIN) con orden externo e interno

La instrucción SQL que figura en el Listado 8 crea el gráfico de ruta de acceso que se puede observar en la Figura 8.

                select *
                from cust_order_header oh, cust_order_detail od
                where oh.cust_total_quantity = od.cust_quantity
                and oh.cust_total = od.cust_unit_price
                and oh.cust_order_date > '2009-01-16-01.00.00.000000'
                and od.product_number > 150000
            

(Tenga en cuenta que, en el ejemplo que figura con anterioridad, la columnacust_order_datese define con un tipo deTIMESTAMP.)

(Vea una versión ampliada de la Figura 8.)

A la unión de escaneo de fusión también se la suele conocer como una unión de fusión ordenada. La abreviación MSJOIN se usa en Optim Query Tuner para denotar este algoritmo de unión. Para mayor información sobre las uniones de escaneo de fusión, haga clic en el vínculo del centro de información apropiado en la sección Recursos para su plataforma DB2.

Toda unión de escaneo de fusión involucra uno o más predicados de unión Equal. La Figura 8 le muestra que existen dos predicados de unión Equal explotados por la misma unión de escaneo de fusión de la consulta de muestra. Para que la unión de escaneo de fusión rinda de manera adecuada, se espera que ambas entradas estén en el orden de las columnas unidas. Más precisamente, en el caso de la tabla externa de la unión (CUST_ORDER_DETAIL), se supone que los registros de calificación se encuentran en el orden de las columnas CUST_QUANTITY y CUST_UNIT_PRICE. De igual forma, se supone que los registros de calificación de la tabla interna están en el orden de CUST_TOTAL_QUANTITY y CUST_TOTAL. Como la tabla interna se escanea con TBSCAN, lo que no garantiza el orden esperado para la MSJOIN, se agrega un nodo SORT para forzar el orden.

Como se puede observar en el descriptor IXSCAN en la Figura 8, la columna principal del índice explotado por el optimizador es PRODUCT_NUMBER, lo que no resulta en el orden requerido para la unión de escaneo de fusión. Por lo tanto, también se agrega un nodo SORT a la parte externa de la unión para forzar el orden.

Como ya lo hemos mencionado, una entrada ordenada es esencial para una unión de escaneo de fusión, aunque esto no es necesario para las uniones de bucle anidado. Por lo tanto, cuando se realiza la comparación con una unión de bucle anidado, es posible que la unión de escaneo de fusión involucre el costo de ordenar las entradas. Sin embargo, el beneficio que le ofrece la unión de escaneo de fusión es que no existe la necesidad de escanear la tabla interna en repetidas ocasiones, como sí ocurren en el caso de las uniones de bucle anidado. Por lo tanto, al momento de decidir qué método de unión elegir, el optimizador sopesa el costo de ordenamiento y el beneficio de rendimiento para el escaneo de la tabla interna para poder tomar la mejor decisión. Una cosa muy llamativa en relación con la unión de escaneo de fusión es que el optimizador evitaría agresivamente el uso de SORT manteniendo un registro del "orden adecuado" de los datos. Por ejemplo, se podría evitar hacer el SORT de la tabla interna si se escanease la tabla interna por medio de un índice con CUST_TOTAL y CUST_TOTAL_QUANTITY como columnas principales. Generalmente, el optimizador elige la unión de escaneo de fusión cuando la cardinalidad de la entrada externa no es lo suficientemente pequeña y no existe ningún tipo de método de acceso eficiente para la tabla interna.

Volver arriba

Un caso de estudio usando anotación de consulta

Esta sección ilustra cómo el gráfico de ruta de acceso y la anotación de consulta de Query Tuner lo pueden ayudar a analizar las rutas de acceso y el rendimiento de SQL. El Listado 9 incluye la misma instrucción SQL de unión triple que se usó en el Listado 1 para presentar el concepto de las rutas de acceso y la ejecución de consultas.

   select c.gender_code, c.marital_status_code, sum(od.unit_cost * cust_quantity)
   from cust_customer c, cust_order_header oh, cust_order_detail od
   where c.cust_code = oh.cust_code
      and oh.cust_order_number = od.cust_order_number
      and c.cust_prov_state_code = 'CA'
      and od.product_number in (154110, 129170, 129150, 129110, 129140, 130130)
   group by c.gender_code, c.marital_status_code
            

La Figura 9 ilustra la función de anotación de consulta en Optim Query Tuner que formatea una instrucción SQL para que cada referencia a la tabla en la cláusula FROM y cada predicado en la cláusula WHERE ocupe una nueva línea. Los predicados se reordenan y reagrupan basándose en el tipo de predicado (local o unión) y en la referencia a la tabla.

(Vea una versión ampliada de la Figura 9.)

La Figura 9 le muestra cómo las funciones de formateo y anotación hacen que la instrucción subyacente sea más fácil de comprender. Las referencias a las tres tablas (CUST_ORDER_DETAIL, CUST_CUSTOMER y CUST_ORDER_HEADER) se visualizan en tres líneas diferentes. Cada uno de los cuatro predicados también se visualiza en una línea diferente (con los dos predicados locales antes de los dos predicados de unión).

Además del formato que resulta más fácil de leer, usted también tiene acceso a la anotación, que le ofrece información estadística crítica para facilitar su análisis del rendimiento de SQL. Hacia la derecha de cada referencia a la tabla, se encuentra la cardinalidad de la tabla (tanto en lo que se refiere a la cantidad de registros como a la cantidad de páginas) y la cantidad estimada de filas calificadas.

La cantidad asociada con QUALIFIED_ROWS representa la cantidad estimada de registros que calificaría a todos los predicados locales de la referencia subyacente a la tabla. Por ejemplo, se estima que la tabla CUST_ORDER_DETAIL devuelva 8,000001 de 560.273 registros luego de aplicar los predicados locales. De igual forma, sólo 729,99976 de 31.284 registros calificarían a los predicados locales en el caso de la tabla CUST_CUSTOMER. Como no existen predicados locales para CUST_ORDER_HEADER, todas las 539.526 filas sobrevivirán. Esta información le puede llegar a permitir comprender cómo se elige una tabla principal en una secuencia de unión: — A menor cantidad de filas calificadas, más probable será que el optimizador seleccione la tabla como la tabla principal en la secuencia de unión.

Como se puede observar en la Figura 1, el optimizador selecciona la tabla CUST_ORDER_DETAIL como la tabla principal de la secuencia de unión porque se supone que sólo va a producir ocho registros luego de aplicar los predicados locales. En la ruta de acceso, se accede a la segunda referencia de la tabla (CUST_ORDER_HEADER) y a la tercera referencia de la tabla (CUST_CUSTOMER) en la secuencia de unión por medio de un índice único completamente coincidente. Esto quiere decir que cada sondeo de la segunda referencia de la tabla y de la tercera referencia de la tabla eficientemente localizaría el único registro coincidente con la condición de unión. Por lo tanto, esta ruta de acceso podría ser la de mejor rendimiento siempre que la estimación de QUALIFIED_ROWS de la primera referencia de la tabla (CUST_ORDER_DETAIL) sea precisa.

Para tener una idea de cómo el optimizador calcula QUALIFIED_ROWS y qué tan preciso es, vuelva a la Figura 9. Tenga en cuenta que sólo hay un predicado local para CUST_ORDER_DETAIL, que es un predicado de lista IN en la columna PRODUCT_NUMBER, con seis elementos en la lista (od.product_number en (154110, 129170, 129150, 129110, 129140, 130130)).

La anotación de consulta para este predicado le muestra que el factor de filtrado (FF) del predicado es 0,000014 ó 0,0014%. Esto quiere decir que el porcentaje estimado de filas que calificarían con este predicado sería 0,0014%. La estimación de QUALIFIED_ROWS se obtiene mediante la multiplicación de la cardinalidad de la tabla (560.273 registros) por el factor de filtrado del predicado (0,0014%).

Para comprender cómo el optimizador llega al 0,0014% como factor de filtrado, primero observemos los seis elementos en el predicado de la lista IN. La anotación de consulta para el predicado le muestra que existen 273 valores distintos en la columna PRODUCT_NUMBER. Asumiendo una distribución uniforme, el optimizador debería llegar a la conclusión normal de que el 2,197% (6/273 = 0,02197) de los registros calificaría a este predicado. En cambio, usa el 0,0014%. ¡Esto sugiere una mucho mejor selectividad de predicados de lo que parece ser!

Para comprender esto, observe MAXFREQ en la anotación de consulta para este predicado. Luego de reunir todas las estadísticas necesarias y hacer que estén disponibles, MAXFREQ le muestra la frecuencia de los valores que aparecen con mayor frecuencia en la columna. En este ejemplo, es 3,32%, lo que resulta mucho más alto que el porcentaje promedio por valor (1/273 = 0,366%). Esto sugiere que algunos de los 273 valores se podrían hacer disminuir. Las estadísticas disponibles en esta columna lo pueden ayudar a revelar lo que en realidad está ocurriendo. Usted puede obtener las estadísticas desde el descriptor de tabla del Nodo tabla en el gráfico de ruta de acceso subyacente.

Para ver el descriptor de tabla, haga clic derecho en el Nodo tabla en el gráfico de ruta de acceso. La Figura 10 le muestra el descriptor de tabla para CUST_ORDER_DETAIL.

(Vea una versión ampliada de la Figura 10.)

Dirigiéndose hacia las estadísticas de la columna en el descriptor de tabla, usted puede descubrir que los seis elementos en el predicado de la lista IN resultan ser los seis últimos valores en las estadísticas de frecuencia. Al sumar estos valores de frecuencia, usted obtiene exactamente el mismo factor de filtrado (estimado), 0,0014%, para el predicado de la lista IN. Por lo tanto, esto explica cómo el optimizador determinó que el factor de filtrado es 0,0014%.

Además, tenga en cuenta que la marca de tiempo correspondiente a la colección de estadísticas que se visualiza en la parte inferior del descriptor de tabla le muestra una fecha muy reciente relativa al momento en el que se explicó esta consulta. La estimación del optimizador podría llegar a estar muy cerca del valor real, siempre y cuando la distribución de datos no se haya modificado desde el último RUNSTATS. Asumiendo que esto es así, se considera que la ruta de acceso subyacente es óptima y muy confiable.

Volver arriba

Conclusión

Este artículo describió los conceptos básicos de las rutas de acceso y cómo leer un gráfico de ruta de acceso. Las capacidades de gráfico de ruta de acceso y anotación de consulta de Optim Query Tuner se usaron para revisar consultas reales y explicar el razonamiento relativo al cómo y al por qué el optimizador de DB2 eligió ciertas rutas de acceso en particular. Esta información le debería ofrecer todos los bloques de construcción necesarios para comenzar a usar las consultas de puesta a punto. Los artículos futuros de esta serie le ofrecerán más información sobre las metodologías que usted puede usar para la puesta a punto de consultas.

Volver arriba

Descargar

Información sobre métodos de descarga

Recursos

Aprender

• Mire la demostración titulada "Optim Performance Management solution" [Solución Optim de gestión de rendimiento] (developerWorks; abril de 2010) para observar cómo una compañía ficticia usa las soluciones Optim para resolver problemas antes de que afecten al negocio, lo que incluye el uso de Optim Query Tuner para DB2 para Linux, UNIX y Windows.
  
  
• Ingrese a la página web del producto Optim Query Workload Tuner para DB2 para z/OS en la página web del producto Optim Query Tuner para DB2 para Linux, UNIX y Windows para obtener mayor información al respecto, incluso sobre cómo adquirir el producto en cuestión.
  
  
• Lea la información del producto DB2 para Linux, UNIX y Windows sobre uniones en el Centro de Información de DB2.
  
  
• Lea la información del producto DB2 para Linux, UNIX y Windows sobre los tipos de predicado en el Centro de Información de DB2.
  
  
• Lea la información del producto DB2 para z/OS sobre las uniones de bucle anidado en el Centro de Información de DB2 para z/OS.
  
  
• Lea la información del producto DB2 para z/OS sobre las uniones de escaneo de fusión en el Centro de Información de DB2 para z/OS.
  
  
• Lea la información del producto DB2 para z/OS sobre los predicados stage 1 y stage 2 en el Centro de Información de DB2 para z/OS.
  
  
• " Best practices when using Data Studio and Optim Development Studio with DB2 for z/OS" [Mejores prácticas al momento de usar Data Studio y Optim Development Studio con DB2 para z/OS] (developerWorks; junio de 2010) incluye información que lo ayudará a configurar Data Studio para la puesta a punto de consultas en DB2 para z/OS.
  
  
• Explore la página de la familia de productos Optim de developerWorks y obtenga más información sobre las soluciones Optim. Encuentre documentación técnica, artículos instructivos, material educativo, archivos para descargar de Internet, información sobre productos y mucho más.
  
  
• Obtenga más información sobre Gestión de información en la zona de Gestión de información de developerWorks. Encuentre documentación técnica, artículos instructivos, material educativo, archivos para descargar de Internet, información sobre productos y mucho más.
  
  
• Manténgase actualizado sobre las transmisiones por Internet y los eventos técnicos de developerWorks. Ingrese a esta página para conocer un programa de sesiones informativas técnicas virtuales relativas a la cartera de productos Optim para la gestión integrada de datos.
  
  

Obtener los productos y tecnologías

• Descargue el software gratuito de Data Studio.
  
  

Comentar

• Participar en el foro de debate.
  
  
• Ingrese a los blogs de expertos en Gestión integrada de datos) e involúcrese en la comunidad de Integrated Data Management, que incluye una lista integral de recursos y archivos para descargar de Internet.
  
  
• Ingrese a los blogs de developerWorks e involúcrese en la comunidad de developerWorks.
  
  

Sobre los autores

Gene Fuh trabaja en tecnologías de bases de datos para IBM desde 1994. Ingresó en la organización de DB2 para z/OS en el año 2000, luego de haber trabajado en la organización de DB2 LUW durante seis años y medio. En 2004, creó un equipo para el desarrollo de DB2 Optimization Expert and Optimization Service Center (DB2 OE/OSC). Fue el arquitecto principal del proyecto y se encargó de su supervisión hasta 2007, cuando el producto pasó a estar disponible de manera simultánea con DB2 9 para z/OS. En 2008, comenzó a realizar la transición de la tecnología DB2 OE/OSC a las soluciones de puesta a punto Optim, conocidas como Optim Query Tuner y Optim Query Workload Tuner. Durante los 17 años que lleva trabajando para IBM, Gene solicitó el registro de 48 patentes y publicó más de 20 documentos técnicos tanto en conferencias académicas como de IBM.

Kendrick Ren es Technical Lead de los productos IBM Optim Query Tuner y Optim Query Workload Tuner en el laboratorio de IBM en Toronto. Kendrick ya trabajaba en las versiones anteriores de estos productos, conocidos como DB2 Optimization Expert y Optimization Service Center, desde que se creó el equipo en 2004. Además, trabaja en estrecha colaboración con los clientes y los socios de negocios que usan estos productos y los ayuda en el área de optimización de consultas. Antes de formar parte del equipo de Optimization Expert, Kendrick trabajó en el producto IBM WebSphere Commerce Server durante dos años.

Kathy Zeidenstein es parte de IBM desde hace muchísimos años. En la actualidad, trabaja para el equipo de activación técnica de IBM Optim Solutions y es responsable del desarrollo de la comunidad y las comunicaciones. Antes de comenzar a desempeñar esta tarea, era gerente de marketing de productos en lo relativo a las tecnologías de análisis y de búsqueda de textos.

Califique este artículo

Comentario

Volver arriba

Tabla de contenidos

• Introducción
• Sobre las soluciones de puesta a punto de consultas Optim
• ¿Cómo se lee un gráfico de ruta de acceso?
• Rutas de acceso y ejecución de consultas
• Otros métodos de acceso y otros métodos de unión
• Un caso de estudio usando anotación de consulta
• Conclusión
• Descargar
• Recursos
• Sobre los autores
• Comentarios