T2.2: Interprets Query Plans

Resumen

InterSystems IRIS proporciona múltiples métodos para ver y analizar planes de ejecución de consultas SQL.

• Tiempo: Los planes de consulta se generan cuando una operación de consulta se prepara (no necesita ejecutar la consulta)

Métodos Disponibles

Comando SQL EXPLAIN:

• Genera plan de consulta formato XML para consultas SELECT
• Puede incluir opcionalmente planes de consulta alternos y estadísticas SQL

Management Portal:

• Botón Show Plan en interfaz SQL del System Explorer
• SQL Performance Tools accesibles a través de System Explorer Tools

SQL Shell:

• SHOW PLAN: Muestra plan de ejecución para consulta ejecutada más recientemente
• SHOW PLANALT: Muestra planes de ejecución alternos

Acceso Programático:

• `$SYSTEM.SQL.Explain()`: Genera y muestra planes de consulta formato XML con planes alternos opcionales

Comportamiento Predeterminado

• Todas las herramientas muestran lo que InterSystems IRIS considera el plan de consulta óptimo por defecto
• La mayoría de herramientas también pueden generar planes de ejecución alternos para comparación
• Herramienta Alternate Show Plans: Habilita comparación de múltiples planes con estadísticas de runtime

Comportamiento SelectMode

• Show Plan por defecto retorna valores en modo Logical
• Cuando se invoca desde Management Portal o SQL Shell, usa modo Runtime

Resumen

El Plan de Consulta comienza ejecución con una de tres sentencias de tipo map que indican cómo se accederá a los datos.

Sentencias de Tipo Map

"Read master map" (Ineficiente):

• Indica un full table scan como primer elemento en módulo principal
• Master map contiene todos los datos en la tabla
• Lee datos en sí en lugar de un índice a los datos
• Casi siempre indica un plan de consulta ineficiente
• Recomendación: Definir un índice apropiado (a menos que la tabla sea relativamente pequeña)

"Read index map" (Eficiente):

• Indica que la consulta usará un índice disponible
• Generalmente mucho más eficiente que acceso master map

"Generate a stream of idkey values using the multi-index combination":

• Indica uso de múltiples índices (Multi Index)

Estructura de Almacenamiento de Tabla

Una tabla SQL se almacena como un conjunto de maps:

• Master map: Contiene todos los datos de tabla; campos RowID o IDKEY usados como subscripts de map
• Index maps: Otros campos usados como subscript(s) principal(es), con RowID/IDKEY como subscripts adicionales de nivel inferior
• Bitmaps: Estructuras de índice especializadas opcionales

Análisis de Bandera Roja

Al analizar planes de consulta, la presencia de "Read master map" debería ser una bandera roja provocando investigación de si puede definirse un índice apropiado para mejorar el rendimiento de consulta.

Resumen

Cuando un plan de consulta muestra "Read index map" como primer paso de ejecución, esto indica que el optimizador de consultas ha seleccionado una estrategia de acceso basada en índice en lugar de un full table scan.

Detalles de Plan de Índice

El plan proporciona información sobre:

• Qué índice se está usando
• Los subscripts (campos) que comprenden el índice
• Index maps usan campos de tabla como subscript(s) principal(es), con RowID/IDKEY como subscripts adicionales de nivel inferior

Tipos de Acceso a Valor Subscript

El plan indica qué valores subscript se accederán (la eficiencia disminuye en la lista):

• Valor único dado: Más eficiente
• Conjunto de valores dados: Menos eficiente
• Rango de valores: Menos eficiente
• Todos los valores presentes: Menos eficiente (escaneo completo de subscript)

Consideraciones de Índice Bitmap

• Capa bitmap adicional puede pensarse como un nivel subscript RowID extra
• Los bitmaps solo pueden usarse para RowIDs que sean enteros positivos

Patrones de Acceso a Map

El plan para una consulta puede acceder:

• Map único: Solo index o master map
• Dos maps: Index map seguido de master map
• El índice proporciona RowIDs de registros coincidentes
• Master map accedido para recuperar datos completos de registro
• Múltiples maps: Plan multi-índice usando varios maps

Validación de Optimización

Comprender el uso de índice en planes de consulta es esencial para:

• Identificar oportunidades de optimización
• Validar que los índices definidos se están utilizando efectivamente

Resumen

Al acceder datos de una tabla, a menudo es necesario examinar múltiples filas iterativamente, indicado por un loop en el plan de consulta.

Visualización de Loop

• Cuerpo de loop: Instrucciones ejecutadas para cada paso a través del loop
• Indicación visual: Indentado en la salida show plan
• Loops anidados: Cada nivel de loop indicado por mayor indentación
• Jerarquía: Crea jerarquía visual mostrando estructura de anidamiento

Cuándo se Usan Loops

Los loops se usan cuando la consulta necesita:

• Iterar a través de un conjunto de filas
• Escanear un rango de valores de índice
• Procesar múltiples registros coincidentes

Operaciones Lookup

En contraste, una operación lookup:

• Representa acceso directo a una fila específica o conjunto pequeño de filas
• Típicamente usa un índice para ir directamente a datos objetivo sin iteración
• Generalmente mucho más rápido que loops

Implicaciones de Rendimiento

• Lookups: Acceso rápido y directo
• Loops: Múltiples filas deben ser examinadas
• Loops profundamente anidados: Pueden indicar preocupaciones de rendimiento, especialmente cuando el loop externo procesa muchas filas

Lectura de Indentación de Plan

La estructura de indentación en el show plan hace fácil:

• Identificar niveles de anidamiento de loop
• Comprender el patrón de iteración de ejecución de consulta

Resumen

La construcción de archivos temporales y otras operaciones de procesamiento pueden delegarse a una unidad separada de trabajo llamada módulo en el plan de ejecución de consulta.

Nomenclatura de Módulo

• Nombrados alfabéticamente comenzando con B (Module:B, Module:C, etc.)
• Listados en orden de ejecución (no necesariamente orden alfabético)
• Cuando termina la ejecución de un módulo, el procesamiento se reanuda en la siguiente sentencia después de invocación de módulo

Frecuencia de Ejecución

Comprender si un módulo se ejecuta una vez o repetidamente es crítico para análisis de rendimiento:

• Ejecutado una vez: Configuración inicial o preparación de datos única
• Ejecutado repetidamente: Dentro de un loop, multiplica costo por número de iteraciones

Nomenclatura de Módulo de Subconsulta

Los módulos de subconsulta tienen convenciones de nomenclatura especiales:

• Nombrados alfabéticamente con secuencia de salto (saltando una o más letras antes de cada subconsulta nombrada)
• Cuando se alcanza el fin del alfabeto, subconsultas adicionales se numeran
• Secuencia ejemplo: F, I, L, O, R, U, X, 27, 30, 33 (salto cada-tercero)

Invocación de Módulo

• Si una subconsulta llama un módulo, el módulo se coloca en secuencia alfabética después de la subconsulta sin salto
• El lugar preciso desde donde se llama una sección de subconsulta no siempre se indica
• A menudo invocado como parte de procesamiento de condiciones o expresiones

Análisis de Rendimiento

Identificar frecuencia de ejecución de módulo ayuda:

• Predecir rendimiento general de consulta
• Señalar oportunidades de optimización

Resumen

Las estadísticas de tabla juegan un rol crucial en planificación de consulta ya que el compilador SQL usa información como tamaños de tabla e índices disponibles para generar el conjunto más eficiente de instrucciones.

Comando TUNE TABLE

El comando TUNE TABLE recolecta estadísticas de una tabla existente basadas en los datos actualmente en la tabla.

Estadísticas recolectadas:

• Tamaño de map
• Tamaño de extent
• Selectividad para cada campo

Requisito de datos: Deberían ser representativos de datos esperados cuando la tabla esté completamente poblada.

Comportamiento de Consulta en Caché

• Cuando los valores cambian: TUNE TABLE recompila todas las consultas en caché que usan la tabla
• Cuando los valores no cambian: Las consultas en caché no se purgan, definición de clase no se marca para recompilación

Advertencias de Plan de Consulta

Cuando el sistema prepara una consulta SQL y genera un plan, las advertencias se reportan como parte de Show Plan:

• "Table are not tuned": Se genera cuando una o más tablas referenciadas no han sido tuneadas
  Resolución: Tunear las tablas especificadas usando TUNE TABLE

  Beneficios del Optimizador

  Las estadísticas permiten al optimizador tomar decisiones informadas sobre:

  • Rutas de acceso
  • Órdenes de join
  • Otras estrategias de ejecución

  Sin Estadísticas Actuales

  • El optimizador debe confiar en suposiciones predeterminadas
  • Puede no coincidir con distribución real de datos
  • Potencialmente lleva a planes de consulta subóptimos

  Referencias de Documentación

  • TUNE TABLE
  • Table Statistics for Query Optimizer

  7. Soluciona problemas usando query hints Report Issue

  Puntos Clave

  • %ALLINDEX: Forzar uso de todos los índices aplicables en optimización de consulta
  • %FIRSTTABLE: Especificar qué tabla debería accederse primero en join
  • %FULL: Forzar full table scan en lugar de usar índice
  • %IGNOREINDEX: Prevenir que índice específico sea considerado
  • %NOLOCK: No realizar bloqueo en tablas (modo READ UNCOMMITTED)
  • %NOFPLAN: Ignorar plan congelado y generar nuevo plan de consulta
  • %PARALLEL: Habilitar procesamiento de consulta paralelo para consultas elegibles

  Notas Detalladas

  Resumen

  Los query hints (también llamados optimization hints) proporcionan un mecanismo para influir o sobrescribir el comportamiento predeterminado del optimizador de consultas cuando la optimización estándar produce resultados subóptimos.

  • Colocación: Especificados en cláusula FROM o como opciones %keyword después de SELECT

  Query Hints Comunes

  Control de Índice:

  • %ALLINDEX: Forzar uso de todos los índices aplicables en optimización de consulta
  • %IGNOREINDEX: Prevenir que índice específico sea considerado

  Acceso a Tabla:

  • %FULL: Forzar full table scan (beneficioso para tablas pequeñas o cuando la mayoría de filas serán seleccionadas)
  • %FIRSTTABLE / %STARTTABLE: Especificar qué tabla debería accederse primero en join

  Bloqueo y Planes:

  • %NOLOCK: No realizar bloqueo en tablas (modo READ UNCOMMITTED)
  • %NOFPLAN: Ignorar plan congelado y generar nuevo plan de consulta

  Control de Procesamiento:

  • %PARALLEL: Habilitar procesamiento de consulta paralelo para consultas elegibles
  • %INORDER: Procesar joins en orden de cláusula FROM
  • %NOFLATTEN: Prevenir aplanamiento de subconsulta
  • %NOMERGE: Prevenir fusión de vista
  • %NOREDUCE: Deshabilitar reducción de expresión
  • %NOSVSO: Deshabilitar optimización de secuencia sort-vector
  • %NOTOPOPT: Deshabilitar optimización TOP
  • %NOUNIONOROPT: Deshabilitar optimización UNION/OR

  Mejores Prácticas

  • Usar juiciosamente y solo después de análisis cuidadoso
  • Los hints sobrescriben la lógica del optimizador basada en estadísticas y algoritmos completos
  • Particularmente valioso cuando tiene conocimiento específico sobre distribución de datos o patrones de acceso que el optimizador no puede inferir solo de estadísticas

  Referencias de Documentación

  • Specify Optimization Hints in Queries
  • SELECT %keyword options

  8. Identifica oportunidades de índice basadas en planes de consulta Report Issue

  Puntos Clave

  • Indicador Read master map: Señal primaria de que creación de índice podría mejorar rendimiento
  • Análisis de cláusula WHERE: Condiciones que filtran datos son candidatas para columnas de índice
  • Columnas de condición JOIN: Campos usados en predicados join benefician de indexación
  • Columnas ORDER BY: Campos de ordenamiento pueden beneficiar de índice para evitar ordenamiento temp-file
  • Mensajes de advertencia: Advertencias "Index cannot be used" identifican problemas de collation o selectability

  Notas Detalladas

  Resumen

  Los planes de consulta proporcionan pistas valiosas para identificar oportunidades de crear nuevos índices o modificar existentes para mejorar rendimiento de consulta.

  Indicador Primario: "Read master map"

  El indicador más obvio es "Read master map" como primer elemento en módulo principal:

  • Señala que no existe índice adecuado
  • Se está realizando un full table scan

  Identificación de Candidato de Índice

  Columnas de Cláusula WHERE:

  • Examinar condiciones para identificar qué campos filtran datos
  • Candidatos principales para creación de índice

  Columnas de Condición JOIN:

  • Campos usados en condiciones JOIN entre tablas
  • Índices en columnas join mejoran dramáticamente rendimiento de join
  • Habilitan lookups eficientes en lugar de loops anidados

  Columnas ORDER BY:

  • Campos especificados en cláusulas ORDER BY pueden beneficiar de indexación
  • Índice apropiado proporciona datos en orden deseado
  • Evita operación de ordenamiento separada y creación de archivo temporal

  Mensajes de Advertencia

  La sección Warnings puede identificar oportunidades de índice:

  Problemas de Collation:

  • " cannot be used... because there is no index subscript collation"
  • " cannot be used... because the index subscript collation is "
  • Resolución: Definir un índice con collation apropiado

  Problemas de Selectability:

  • " are not selectable"
  • Indica índices que mejorarían rendimiento pero necesitan ser reconstruidos

  Indicadores de Temp File

  Cuando los planes de consulta agregan nodos vacíos a un temp file:

  • Sugiere que la consulta anticipa necesitar resultados temporales pero no tiene ninguno para almacenar
  • Agregar un índice apropiado puede eludir creación de temp files no usados

  Mejor Práctica

  La revisión sistemática de planes de consulta para consultas frecuentemente ejecutadas o críticas de rendimiento es una parte esencial del tuning de base de datos.

  Referencias de Documentación

  • Structure of the Plan - Warnings
  • Accessing Maps

  9. Interpreta valores de costo relativo en planes de consulta Report Issue

  Puntos Clave

  • Definición de costo: Entero abstracto calculado de múltiples factores para comparar planes de ejecución
  • Factores de costo: Complejidad de consulta, presencia de índices, y tamaño(s) de tabla
  • Comparación misma-consulta: Útil solo para comparar diferentes planes de ejecución de misma consulta
  • No comparable entre consultas: No puede comparar valores de costo entre dos consultas diferentes
  • Orden de planes alternos: Listados en orden ascendente por costo (costo menor primero)
  • Costo no disponible: Ciertas consultas agregadas como COUNT(*) sin cláusula WHERE

  Notas Detalladas

  Resumen

  El costo relativo es un valor entero mostrado en planes de consulta que proporciona una medida abstracta para comparar la eficiencia de diferentes planes de ejecución para la misma consulta.

  Factores de Cálculo de Costo

  El cálculo toma en cuenta:

  • Complejidad de la consulta
  • Presencia de índices
  • Tamaño de la(s) tabla(s) siendo accedida(s)

  Limitaciones Importantes

  • No comparable entre consultas: El costo relativo NO es útil para comparar dos consultas diferentes
  • Solo misma-consulta: Solo significativo al comparar planes de ejecución alternativos para la misma consulta
  • Medida abstracta: No corresponde directamente a tiempo de ejecución, consumo de recursos, o métrica de rendimiento específica
  • Representa: Estimación del optimizador del trabajo total requerido para ejecutar el plan de consulta

  Visualización de Planes Alternos

  Al usar herramienta Alternate Show Plans o $SYSTEM.SQL.Explain() con calificador "all":

  • Múltiples planes de ejecución mostrados para una sola consulta
  • Listados en orden ascendente por costo (costo menor primero)
  • Plan de costo menor es lo que InterSystems IRIS considera óptimo

  Precisión de Estimación de Costo

  • Basada en estadísticas y heurísticas
  • Planes con costo estimado ligeramente mayor podrían realmente funcionar mejor
  • El optimizador no puede dar cuenta de todos los factores

  Casos Especiales

  Para ciertas consultas agregadas (COUNT(*) o MAX(%ID) sin cláusula WHERE):

  • Show plan retorna "Relative cost not available"
  • Estas consultas usan optimizaciones especiales evitando análisis normal de costo

  Comparación Práctica

  • Enfocarse en magnitud de diferencias de costo
  • Pequeña diferencia de costo puede no traducirse en variación de rendimiento significativa
  • Diferencias grandes de costo típicamente indican estrategias de ejecución sustancialmente diferentes
  • Característica Stats: Proporciona estadísticas reales de runtime (Time, Global Refs, Commands, Read Latency) para complementar estimaciones de costo

  Referencias de Documentación

  • Structure of the Plan - Query Plan
  • Alternate Show Plans - Stats

  Resumen de Preparación para el Examen

  Conceptos Críticos a Dominar:

  1. Herramientas de Plan de Consulta: Conocer comando EXPLAIN, Show Plan del Management Portal, comandos SQL Shell, y $SYSTEM.SQL.Explain()
  2. Reconocimiento de Table Scan: Identificar "Read master map" como indicador de full table scan ineficiente
  3. Uso de Índice: Reconocer "Read index map" y comprender patrones de acceso index vs. master map
  4. Loops vs. Lookups: Distinguir loops iterativos de lookups directos, comprender implicaciones de loop anidado
  5. Ejecución de Módulo: Identificar si los módulos se ejecutan una vez o repetidamente, comprender impacto de rendimiento
  6. Estadísticas de Tabla: Comprender rol de TUNE TABLE e impacto en optimización de consulta
  7. Query Hints: Saber cuándo y cómo usar hints %FULL, %IGNOREINDEX, %FIRSTTABLE, %NOLOCK
  8. Oportunidades de Índice: Identificar desde "Read master map", cláusulas WHERE, condiciones JOIN, y advertencias
  9. Costo Relativo: Usar solo para comparar planes alternos de misma consulta, comprender componentes de factor de costo

  Escenarios Comunes de Examen:

  • Analizar plan de consulta para identificar full table scan vs. uso de índice
  • Recomendar creación de índice basado en plan de consulta mostrando "Read master map"
  • Interpretar loops anidados y estimar frecuencia de ejecución
  • Identificar qué herramienta de plan de consulta usar para escenario específico de solución de problemas
  • Comprender cuándo las estadísticas de tabla necesitan actualización (TUNE TABLE)
  • Seleccionar query hints apropiados para resolver problemas de rendimiento
  • Comparar costos relativos de planes de consulta alternos
  • Reconocer patrones de ejecución de módulo y sus implicaciones de rendimiento
  • Identificar oportunidades de índice desde advertencias de plan de consulta

  Recomendaciones de Práctica Práctica:

  • Generar planes de consulta usando múltiples métodos (EXPLAIN, Portal, SQL Shell)
  • Comparar planes antes y después de crear índices
  • Ejecutar TUNE TABLE y observar impacto en plan de consulta y consultas en caché
  • Usar Alternate Show Plans para comparar múltiples estrategias de ejecución
  • Practicar lectura de estructuras de loop indentadas en planes de consulta
  • Experimentar con query hints y observar cambios de plan
  • Analizar advertencias de plan de consulta y resolver problemas de índice
  • Identificar oportunidades de optimización en planes de consulta reales de base de datos Sample

  Report an Issue

  Issue Type Confusing Content Incorrect Information Outdated Content Formatting Issue Typo Other Description

  Notify me by email when resolved

  Cancel Submit