Interpretando un "Explain Plan" (Parte I)

¿Qué es un explain plan?

Un explain plan es una representación de la ruta de acceso que el optimizador de Oracle Database toma cuando es ejecutada una consulta SQL en la base de datos. El procesamiento de una consulta SQL se puede dividir en 7 fases:

1-  Sintáctica:   se verifica la sintaxis de la consulta

2-  Semántica: comprueba que todos los objetos existan  y sean accesibles

3-  View Merging: se reescribe la consulta como join en tablas base en lugar de usar vistas

4-  Transformación de sentencias: se reescribe la consulta transformando algunas construcciones complejas en otras más simples siempre que sea posible (por ejemplo, subconsulta unnesting, in/or transformation, etc.). Algunas transformaciones usan reglas mientras que otras tienen un costo basado en estadísticas.

5-  Optimización: determina la ruta de acceso óptima para la consulta. El optimizador basado en costos (CBO) usa estadísticas para analizar los costos relativos de acceso a los objetos.

6-  Query Evaluation Plan (QEP): Se evalúan las diferentes opciones de acceso y se  genera el plan.

7-  QEP execution: Se llevan adelante la acciones indicadas en el plan de ejecución determinado.

Los pasos del 1 al 6 a veces se agrupan y se denominan 'parsing', fase de 'parseo' o simplemente análisis.

El paso 7 es la ejecución misma de la sentencia.

El  "explain plan" es fundamentalmente una representación de la ruta de acceso generada en el paso 6.

Una vez que se ha determinado el “access path” o ruta de acceso, éste se almacena en memoria en la “library cache”  junto con la sentencia correspondiente. Las consultas se almacenan en la “library cache” en función de una representación en hash del literal de la sentencia, este hash luego lo utiliza Oracle para ubicarla en memoria. Antes de realizar las fases 1-6 antes mencionadas para un SQL determinado, Oracle busca la sentencia en la “library cache” para ver si ya fue "parseada" y utilizar el plan ya generado. Primero aplica un algoritmo hash al texto del SQL en cuestión y luego se busca este valor hash en  “library cache”.  Si el hash coincide con alguno de los almacenados, se utilizará ese “Access path” o método de acceso para resolver la consulta. Este será siempre utilizado (para la misma consulta) hasta  tanto la misma vuelva a parseada. Dependiendo de las circunstancias, el plan de ejecución tal vez varie de un parseo a otro. (En futuros posteos veremos que determina que una consulta vuelva a ser parseada). 

Terminología

Veamos un poco de terminología y conceptos relacionados a un plan de ejecución:

Row Source: una “row source” es una función de software que implementa operaciones específicas (como por ejemplo un “full table scan” o un “hash join”) y devuelve un conjunto de filas.

Predicados:  Son las cláusulas “where” de una consulta. Se utilizan para unir tablas (join) o para restringir las filas devueltas (filtros).

Tuplas. Se refiere a la filas (rows).

Driving table: esta es la “row source”  que se utiliza para iniciar el procesamiento de la consulta. Si devuelve muchas filas, puede tener un efecto negativo en todas las operaciones posteriores.

Probed table: este es el objeto en el que buscamos los datos después de haber recuperado los datos clave relevantes de la “driving table”.

¿Cómo accede Oracle a los datos?

A nivel físico, Oracle lee bloques de datos. La unidad mínima de lectura,  es de un bloque Oracle, la lectura más grande está limitada por los límites impuestos a nivel sistema operativo (y por el multi-block I/O). Lógicamente, Oracle accede los datos que deben ser leídos utilizando alguno (o varios) de los siguientes métodos:

• Full Table Scan (FTS)
• Index Look-up (unique & non-unique)
• Index Full Scan
• Index Fast Full Scan
• Rowid

Ejemplos de planes de ejecución (explain plan):

Vamos a ejemplificar un plan de ejecución y sus partes,  utilizando la salida de un “explain plan” generado con la opción autotrace de SQL * Plus. Más adelante (en otros posts) veremos diferentes maneras de generar planes de ejecución.

En nuestro ejemplo de hoy utilizamos el parámetro statistics_level en typical (default) para la generación del plan.

Como comentaba, si bien existen diferentes métodos para mostrar los planes de ejecución y a veces la información puede parecer levemente diferente, la información básica de la que se genera el plan es la misma. Con los diferentes mecanismos vamos a poder obtener información más detallada ante determinadas situaciones y bajo condiciones específicas.

Secciones de un Explain Plan

Cuando genera un explain plan, se muestran diferentes secciones dependiendo de la consulta sobre la cual se haya generado el plan y las características u opciones  que utilicemos para obtener el plan de ejecución. Todos los planes de ejecución tendrán una tabla principal del plan, que muestra las operaciones que son llevadas a cabo para ejecutar la consulta y posiblemente incluya otro tipo de información como "plan hash value", cantidad de filas estimadas, estimación de costo para cada uno de los  pasos, etc.

Ejemplo:

SQL> show parameter statistics_level

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

statistics_level                     string      TYPICAL

SQL> set autotrace on

SQL> select * from dual;

D

-

X

Execution Plan

----------------------------------------------------------

Plan hash value: 272002086

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          2  consistent gets

          0  physical reads

          0  redo size

        522  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> set autotrace traceonly

SQL> select * from t1;

38224 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3617692013

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 37155 |  2866K|    51   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T1   | 37155 |  2866K|    51   (0)| 00:00:01 |

--------------------------------------------------------------------------

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       2727  consistent gets

          0  physical reads

          0  redo size

    1796910  bytes sent via SQL*Net to client

      28552  bytes received via SQL*Net from client

       2550  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      38224  rows processed

Si la consulta tiene una cláusula where, se generarán los predicados:

SQL> set autotrace traceonly

SQL> select * from t1 where object_id = 45673;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 3617692013

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     2 |   158 |    51   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T1   |     2 |   158 |    51   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OBJECT_ID"=45673)

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

          5  recursive calls

          0  db block gets

        253  consistent gets

          0  physical reads

          0  redo size

        410  bytes sent via SQL*Net to client

        513  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          0  rows processed

La sección “Predicate Information” contiene información de predicados de acceso (aquellos que son utilizados para acceder a los datos en objetos subyacentes) o predicados de filtro (que se utilizan para restringir las filas devueltas desde un paso en particular y refinar los resultados). Debemos tener  en cuenta que el optimizador realiza transformaciones, y estas pueden  mostrar predicados en una forma algo diferente a los presentados en la consulta original.

Otro ejemplo de sección de Predicado:

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("DEPT"."DNAME"='ACCOUNTING' OR "DEPT"."DNAME"='OPERATIONS' OR

              "DEPT"."DNAME"='RESEARCH' OR "DEPT"."DNAME"='SALES')

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

En ocasiones, se generará una sección de "Nota" que incluye información útil sobre el plan. La sección puede indicar (entre otros) si la base de datos usó un “SQL Plan Baseline”, outline  o un “SQL Profile” para esa consulta, si se utilizó dynamic sampling (indicando el nivel), si el optimizador usó “statistics feedback” para ajustar sus estimaciones de costos para aplicar a la segunda ejecución de la consulta, si es un “adaptive plan” o si la consulta es tatalmente ejecutada  remota. Por ejemplo:

Por ejemplo:

Note
-----
   - SQL plan baseline SYS_SQL_PLAN_fbc180b13jjdg479e637 used for this statement

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Note
-----
   -  statistics feedback used for this statement

Note
-----
   - this is an adaptive plan

Note
-----
   - fully remote statement

Si una consulta es parcialmente remota, puede existir una sección que muestre la consulta que se envió al sitio remoto:

Por ejemplo:

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - select “id”, “name” FROM “emp” (accessing 'LOOP_LINK' )

Conclusión:
En este artículo vimos a grandes rasgos,  de que se trata y que estructura tiene un “explain plan”, el cual es la herramienta que Oracle nos provee para visualizar el plan de ejecución que utiliza el motor, para obtener los datos requeridos por determinada consulta. Queda a las claras  la importancia de poder obtener la información de este plan de ejecución e interpretar/entender las operaciones descriptas en el "explain plan",

ya que es la mejor herramienta que tenemos para escribir SQL de manera eficiente y además poder ajustar aquellas sentencias que no se estén ejecutando de forma optima.