¿Cómo interpretas el plan de explicación de una consulta?

Al intentar comprender cómo se está ejecutando una instrucción SQL, a veces se recomienda mirar el plan de explicación. ¿Cuál es el process que uno debe seguir para interpretar (tener sentido) un plan de explicación? ¿Qué debería destacarse como, "Oh, esto está funcionando espléndidamente"? versus "Oh no, eso no está bien".

Me estremezco cada vez que veo comentarios de que las tablas completas son malas y el acceso al índice es bueno. Escaneos completos de tabla, escaneos de índices, exploraciones rápidas de índices completos, loops nesteds, combinación de fusión, combinaciones de hash, etc. son simplemente mecanismos de acceso que el analist debe entender y combinar con un conocimiento de la estructura de la database y el propósito de una consulta para llegar a una conclusión significativa.

Un escaneo completo es simplemente la forma más eficiente de leer una gran proporción de los bloques de un segmento de datos (una tabla o una tabla (sub) partición) y, aunque a menudo puede indicar un problema de performance, eso es solo en el context de si es un mecanismo eficiente para alcanzar los objectives de la consulta. Hablando como almacén de datos y tipo de BI, mi bandera de advertencia número uno para el performance es un método de acceso basado en índice y un ciclo nested.

Entonces, para el mecanismo de cómo leer un plan de explicación, la documentation de Oracle es una buena guía: http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/ex_plan.htm#PFGRF009

También lea bien la Guía de ajuste del performance.

También tenga un google para "retroalimentación de cardinalidad", una técnica en la que se puede usar un plan de explicación para comparar las estimaciones de cardinalidad en varias etapas de una consulta con las cardinalidades reales experimentadas durante la ejecución. Wolfgang Breitling es el autor del método, creo.

Entonces, línea de background: comprender los mecanismos de acceso. Comprende la database. Comprenda la intención de la consulta. Evite las reglas generales.

Este tema es demasiado grande para responder en una pregunta como esta. Debería tomarse un time para leer la Guía de ajuste de performance de Oracle

Los dos ejemplos a continuación muestran un escaneo COMPLETO y un escaneo RÁPIDO usando un ÍNDICE.

Lo mejor es concentrarte en tu Costo y Cardinalidad. En cuanto a los ejemplos, el uso del índice networkinguce el costo de ejecutar la consulta.

Es un poco más complicado (y no tengo un 100% de control), pero básicamente el Costo es una function del costo de CPU e IO, y la Cardinalidad es el número de filas que Oracle espera analizar. Reducir ambos es una buena cosa.

No olvide que el Costo de una consulta puede verse influenciado por su consulta y el model optimizador de Oracle (por ejemplo: COSTO, ELEGIR, etc.) y la frecuencia con la que ejecuta sus statistics.

Ejemplo 1:

ESCANEAR http://docs.google.com/a/shanghainetwork.org/File?id=dd8xj6nh_7fj3cr8dx_b

Ejemplo 2 usando índices:

INDICE http://docs.google.com/a/fukuoka-now.com/File?id=dd8xj6nh_9fhsqvxcp_b

Y como ya se sugirió, ten cuidado con TABLE SCAN. Generalmente puedes evitar esto.

Buscar cosas como escaneos secuenciales puede ser algo útil, pero la realidad está en los numbers … ¡excepto cuando los numbers son solo cálculos! Lo que generalmente es mucho más útil que mirar un plan de consulta es mirar la ejecución real. En Postgres, esta es la diferencia entre EXPLAIN y EXPLAIN ANALYZE. EXPLAIN ANALYZE en realidad ejecuta la consulta y obtiene información de time real para cada nodo. Eso le permite ver lo que está sucediendo realmente , en lugar de lo que el planificador cree que sucederá. Muchas veces encontrará que un escaneo secuencial no es un problema en absoluto, sino que es algo más en la consulta.

La otra key es identificar cuál es el paso realmente costoso. Muchas herramientas gráficas usarán flechas de diferentes tamaños para indicar cuánto cuestan las diferentes partes del plan. En ese caso, solo busque los pasos que tienen flechas delgadas entrando y dejando una flecha gruesa. Si no está usando una GUI, necesitará mirar los numbers y search dónde de repente se hacen mucho más grandes. Con un poco de práctica, es bastante fácil distinguir las áreas problemáticas.

Realmente para cuestiones como estas, lo mejor que puedes hacer es PREGUNTAR . En particular, su respuesta a esa pregunta contiene enlaces al documento en línea de Oracle, donde se explican muchas de esas reglas.

Una cosa a tener en count, es que explicar los planes son realmente las mejores suposiciones.

Sería una buena idea aprender a usar sqlplus y experimentar con el command AUTOTRACE. Con algunos numbers duros, generalmente puede tomar mejores decisiones.

Pero deberías PREGUNTAR. Él lo sabe todo 🙂

El resultado de la explicación le dice cuánto time ha tardado cada paso. Lo primero es encontrar los pasos que han llevado mucho time y entender lo que significan. Cosas como un escaneo secuencial le dicen que necesita mejores índices; es principalmente una cuestión de investigación sobre su database y experiencia particular.

Uno "Oh no, eso no está bien" a menudo es en forma de escaneo de tabla . Los escaneos de tabla no utilizan ningún índice especial y pueden contribuir a purgar todos los elementos útiles en las cachings de memory. En postgreSQL, por ejemplo, encontrará que se ve así.

Seq Scan on my_table (cost=0.00..15558.92 rows=620092 width=78) 

A veces, los escaneos de tabla son ideales, por ejemplo, usando un índice para consultar las filas. Sin embargo, este es uno de esos patrones de bandera roja que pareces estar buscando.

Básicamente, eche un vistazo a cada operación y vea si las operaciones "tienen sentido" dado su conocimiento de cómo debería poder funcionar.

Por ejemplo, si une dos tablas, A y B en sus respectivas columnas C y D (CA = BD), y su plan muestra un análisis de índice agrupado (término de SQL Server – no estoy seguro del término del oracle) en la tabla A, luego un bucle nested se une a una serie de búsquedas de índices agrupados en la tabla B, podría pensar que hubo un problema. En ese escenario, puede esperar que el motor haga un par de escaneos de índice (sobre los índices en las columnas unidas) seguido de una combinación de fusión. Investigaciones adicionales podrían revelar malas statistics que hagan que el optimizador elija ese patrón de unión, o un índice que realmente no existe.

mira el porcentaje de time que pasaste en cada subsección del plan, y considera lo que está haciendo el motor. por ejemplo, si está escaneando una tabla, considere poner un índice en el (los) campo (s) que está escaneando para

Principalmente busco escaneos de índices o tablas. Esto generalmente me dice que me falta un índice en una columna importante que está en la instrucción where o join.

De http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx :

Si ve alguno de los siguientes en un plan de ejecución, debe considerarlos como signos de advertencia e investigarlos para detectar posibles problemas de performance. Cada uno de ellos es less que ideal desde una perspectiva de performance.

 * Index or table scans: May indicate a need for better or additional indexes. * Bookmark Lookups: Consider changing the current clustenetworking index, consider using a covering index, limit the number of columns in the SELECT statement. * Filter: Remove any functions in the WHERE clause, don't include wiews in your Transact-SQL code, may need additional indexes. * Sort: Does the data really need to be sorted? Can an index be used to avoid sorting? Can sorting be done at the client more efficiently? 

No siempre es posible evitarlos, pero cuanto más pueda evitarlos, más rápido será el performance de la consulta.

Reglas de juego

(Probablemente también desee leer los detalles:

  • Documentos de Oracle
  • PREGUNTAR
  • Documentos de SQL Server
    )

Malo

Tabla de escaneos de varias tablas grandes

Bueno

Usando un índice único
El índice incluye todos los campos obligatorios

La victoria más común

En aproximadamente el 90% de los problemas de performance que he visto, la ganancia más fácil es dividir una consulta con lotes (4 o más) de tablas en 2 consultas más pequeñas y una tabla temporal.