1. Documentación en Tahiti -> Oracle Database, 11g Release 2 (11.2) -> Masters Book List -> Performance Tuning Guide -> 11 The Query Optimizer -> Reading and Understanding Execution Plans

Documentación en Tahiti -> Oracle Database, 11g Release 2 (11.2) -> Masters Book List -> Performance Tuning Guide -> 12 Using EXPLAIN PLAN

2. Aunque ya hayamos visto como obtener un plan de ejecución de una consulta (con EXPLAIN PLAN o el paquete DBMS_XPLAN), merece la pena repasar los conocimientos adquiridos y profundizar en su interpretación. Un plan de ejecución es la salida que produce el optimizador para que el motor de ejecución de la BD sepa como proceder para acceder a los datos de nuestra BD. Se representa en forma de árbol, de donde se puede obtener el orden en el que se van a realizar las operaciones (que no es el orden en el que se representa el plan), los distintos tipos de acceso a los datos, tipos de JOIN y operaciones con los datos como ordenaciones, agregaciones, … Además, el plan de ejecución nos aporta información acerca del coste y cardinalidad de cada operación, acceso a particiones e información sobre la ejecución en paralelo (PX – Parallel Execution).

Podemos acceder a los planes de ejecución desde muchos sitios diferentes:

· PLAN_TABLE (desde SQL Developer o SQL*Plus)
· V$SQL_PLAN (almacenado en la Library Cache)
· V$SQL_PLAN_MONITOR (en versiones 11g)
· DBA_HIST_SQL_PLAN (planes almacenados en AWR)
· STATS$SQL_PLAN (Statspack)
· SQL Management Base (SQL Plan Baselines)
· SQL Tuning Set
· Ficheros de trazas generadors por DBMS_MONITOR
· Ficheros de trazas generados por el evento 10053
· Volcados de estado de procesos (a partir de 10gR2)

Para visualizar el plan de ejecución ya hemos el método DISPLAY del DBMS_XPLAN. Pero este paquete tiene más procedimientos para obtener el plan de otras ubicaciones:

· DISPLAY: Formatea y muestra los contenidos de la tabla PLAN_TABLE (por defecto)
· DISPLAY_AWR: Accede a los planes de ejecución contenidos en el repositorio AWR
· DISPLAY_CURSOR: Accede a los cursores almacenados en memoria
· DISPLAY_SQL_PLAN_BASELINE: Accede a los planes de la funcionalidad SQL Plan Baseline
· DISPLAY_SQLSET: Utiliza los planes almacenados en un set de SQL TUNING (STS)

Utilizaremos como primer ejercicio el procedimimiento DBMS_XPLAN.DISPLAY que ya hemos visto pero ampliado con varios argumentos que nunca hemos utilizado.

-- Generamos un plan de ejecución con el comando EXPLAIN PLAN con más parámetros de lo habitual
--   · Fijamos un identificador 'example' para poder acceder luego específicamente a dicho plan
--   · Con "INTO" podemos especificar la tabla destino para el plan ejecución (por defecto PLAN_TABLE)
EXPLAIN PLAN
SET STATEMENT_ID = 'example'
INTO PLAN_TABLE FOR
  SELECT E.LAST_NAME, D.DEPARTMENT_NAME
  FROM HR.EMPLOYEES E, HR.DEPARTMENTS D
  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- Mostramos el plan de ejecución que se ha introducido en la tabla PLAN_TABLE
--   · También podemos usar el script @?/rdbms/admin/utlxpls.sql para queries sin PX
--   · Para querys con PX debemos usar @?/rdbms/admin/utlxplp.sql
--   · El tercer argumento es el formato ("TYPICAL" es por defecto)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'example', 'TYPICAL'));

-- Volvemos a lanzar la consulta pero esta vez mostramos toda la información posible del plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'example', 'ALL'));

-- Tenemos la posibilidad de mostrar el conjunto de hints que reproduciría con exactitud nuestro plan
--   · Esto lo vemos en la sección "Outline Data"

3. Una cuestión que todos debemos conocer es que el plan de ejecución que obtenemos con «EXPLAIN PLAN» puede ser distinto al verdadero plan que se ejecute cuando lanzamos la consulta. Y esto se debe a las famosas «Bind Variables». Las variables que se introducen en tiempo de ejecución pueden modificar el plan en función de lo valores que tengan y los histogramas que tengas las columnas del predicado que utilicemos. En este caso es recomendable obtener el plan de la vista V$SQL_PLAN a través de DBMS_XPLAN.DISPLAY_CURSOR. También es recomendable utilizar este método cuando el entorno en el que se están lanzando las sesiones es distinto al nuestro. Por ejemplo, cuando queremos optimizar una consulta que está lanzando una aplicación que utiliza unos parámetros distintos a los nuestros (Por ej. OPTIMIZER_MODE, …), entonces también es recomendable obtener los planes de ejecución de memoria.

-- Lanzamos una consulta con Bind Variables
-- Declaramos una variable de ejemplo 'bv'
VAR bv VARCHAR2(10);
EXEC :bv := 'Gerald';

-- Lanzamos la consulta utilizando dicha variable
SELECT COUNT(*) FROM HR.EMPLOYEES WHERE FIRST_NAME = :bv;

-- Obtenemos el plan de ejecución real que ha utilizado el motor con los valores de las Bind Variables
--   · Los valores aparecen en la sección "Peeked Binds" (Ej. 1 - :BV (VARCHAR2(30), CSID=873): 'Gerald')
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'BASIC +PEEKED_BINDS'));

4. Otra forma «rápida» de obtener el plan de ejecución de una consulta es el parámetro AUTOTRACE. Con este parámetro podemos obtener el plan y las estadísticas de ejecución de nuestra consulta. Si utilizamos el argumento TRACEONLY, la ejecución no devuelve ningún resultado, sólo el plan de ejecución y las estadísticas.

-- Vemos las distintas opciones que tenemos
-- Activamos AUTOTRACE
SET AUTOTRACE ON

-- Filtramos los resultados de la sentencia que ejecutamos
SET AUTOTRACE TRACEONLY

-- Mostramos sólo el plan de ejecución
SET AUTOTRACE TRACEONLY EXPLAIN

-- Mostramos sólo las estadísticas
SET AUTOTRACE TRACEONLY STATISTICS

-- Descactivamos AUTOTRACE
SET AUTOTRACE OFF

-- Generamos las estadísticas generadas de la consulta que utlizamos en el ejercicio anterior
-- Activamos sólo las estadísticas
SET AUTOTRACE TRACEONLY STATISTICS

-- Lanzamos la consulta
SELECT COUNT(*) FROM HR.EMPLOYEES WHERE FIRST_NAME = :bv;

5. El repositorio AWR es muy útil para diagnósticar problemas en el pasado. Podemos consultar las sentencias que producían mayor carga en el sistemas en función de ciertos criterios (tiempo de ejecución, tiempo de CPU, buffer gets, …).

-- Obtenemos todos los planes de ejecución del repositorio AWR que consulten la tabla SH.TEST_SALES
SELECT TF.* FROM DBA_HIST_SQLTEXT HT, TABLE(DBMS_XPLAN.DISPLAY_AWR(HT.SQL_ID, null, null, 'ALL')) TF
WHERE HT.SQL_TEXT LIKE '%SH.TEST_SALES%';

6. En la versión 11g de Oracle se ha introducido la posibilidad de monitorizar las sentencias SQL en tiempo real. Es necesario tener el parámetro STATISTICS_LEVEL a «TYPICAL» como mínimo, y también el parámetro CONTROL_MANAGEMENT_PACK_ACCESS debe ser «DIAGNOSTIC_TUNING» (por defecto en ambos casos). Todas las consultas tienen activada la monitorización por defecto, aunque podemos forzarlo con el hint MONITOR. Una vez lanzamos la monitorización se pueblan las vistas V$SQL_MONITOR y V$SQL_PLAN_MONITOR.

-- Primero ejecutamos las siguientes sentencias en otra sesión
--   · Este script de Tanel Poder permite cargar de trabajo las CPUs de la máquina
CREATE TABLE KILL_CPU(N PRIMARY KEY)
ORGANIZATION INDEX AS
  SELECT ROWNUM FROM ALL_OBJECTS WHERE ROWNUM <= 50;
ALTER SESSION SET "_old_connect_by_enabled"=true;
SELECT COUNT(*) X
  FROM KILL_CPU
  CONNECT BY N > PRIOR N
  START WITH N = 1;

-- De vuelta a la primera sesión obtenemos el informe de monitorización de SQLs
-- Veremos el plan de ejecución de nuestra sentencia pero ademas con estadísticas reales de su ejecución
--   · La columna "Activity (%)" nos aporta mucho valor, ya que nos dice qué operación está más activa
--   · En la sección de "Global Stats" vemos como todo el tiempo se está yendo en CPU y hay muy pocas esperas
SET LONG 10000000
SET LONGCHUNKSIZE 10000000
SET PAGES 512 LINES 512
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR FROM DUAL;

-- Este informe se puede generar en HTML y abrirlo con un navegador de la siguiente forma
-- Una vez generado el informe, hay que borrar la cabecera del archivo generado
SET TRIMSPOOL ON
SET TRIM ON
SET PAGES 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SPOOL /tmp/test_sql_monitoring.sql
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(TYPE=>'ACTIVE') FROM DUAL;
SPOOL OFF

-- Cuando hayamos terminado de revisar el ejemplo se puede eliminar la tabla
DROP TABLE KILL_CPU;

7. Después de haber visto varios métodos para generar un plan de ejecución nos metemos de lleno en la interpretación del mismo. La siguiente imagen está sacada del curso «Oracle Database 11g: SQL Tuning Workshop» en el que se profundiza no sólo en los planes de ejecución, sino también en practicamente todas las posibles operaciones que pueden salir en el propio plan.

La consulta que se ve en la imagen utiliza el hint RULE que obliga al optimizador a utilizar el sistema antiguo de reglas en vez del recomendado y más actual basado en costes. La ventaja que tiene el sistema basado en reglas es que la generación de los planes de ejecución es mucho más determinista. Esto quiere decir que es mucho más previsible que el sistema basado en costes. Esto puede ser ventajoso en alguna ocasión, pero se puede decir que el CBO (optimizador basado en costes) es lo habitual y recomendable.

Si nos fijamos en el plan, el orden de ejecución no es el mismo que se ve en la tabla (ordenado por la columna Id), sino que se realiza el orden visto en el árbol de la derecha. La primera operación que se realiza es la operación «hoja» (Id=3). Si el padre tienes más hijos, entonces la siguiente operación que se realiza es el siguiente hijo que cuelga del padre. En este caso nos vamos hasta la operación 5. Si el padre de éste no tiene más hijos se ejecuta la operación del padre.

Aplicando este algoritmo de recorrido, tenemos que el orden de ejcución de las operaciones es 3-5-4-2-6-1.

-- Veamos un plan de ejecución para explicar la información que contiene
-- Activamos las estadísticas a "ALL"
ALTER SESSION SET STATISTICS_LEVEL=ALL;

-- Lanzamos una consulta de ejemplo pero forzamos que no se utilice Result Cache
SELECT /*+ RULE NO_RESULT_CACHE */ ENAME, JOB, SAL, DNAME
FROM SCOTT.EMP, SCOTT.DEPT
WHERE
  DEPT.DEPTNO=EMP.DEPTNO AND NOT EXISTS (SELECT * FROM SCOTT.PROJ WHERE EMP.EMPNO = PROJ.EMPNO);

-- Obtenemos el plan de ejecución que debería salir más o menos esto
--
--   ------------------------------------------------------------------------------
--   | Id  | Operation            | Name | Starts | A-Rows |   A-Time   | Buffers |
--   ------------------------------------------------------------------------------
--   |   0 | SELECT STATEMENT     |      |      1 |      3 |00:00:00.01 |      35 |
--   |*  1 |  FILTER              |      |      1 |      3 |00:00:00.01 |      35 |
--   |   2 |   MERGE JOIN         |      |      1 |      3 |00:00:00.01 |      14 |
--   |   3 |    SORT JOIN         |      |      1 |      7 |00:00:00.01 |       7 |
--   |   4 |     TABLE ACCESS FULL| DEPT |      1 |      7 |00:00:00.01 |       7 |
--   |*  5 |    SORT JOIN         |      |      7 |      3 |00:00:00.01 |       7 |
--   |   6 |     TABLE ACCESS FULL| EMP  |      1 |      7 |00:00:00.01 |       7 |
--   |*  7 |   TABLE ACCESS FULL  | PROJ |      3 |      0 |00:00:00.01 |      21 |
--   ------------------------------------------------------------------------------
--
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'TYPICAL IOSTATS LAST'));

Explicamos que quiere decir cada columna:

· A-Rows son las filas producidas por cada origen de filas (operación que produce filas)
· Buffers se corresponde con las lecturas consistentes realzadas en cada operación
· Starts especifica cuantas veces se ha procesado la operación

8. Los planes de ejecución también pueden mostrar información sobre particiones. La funcionalidad «Partition Pruning» descarta aquellas particiones a las que no es necesario acceder para minimizar la cantidad de operaciones de lectura en la BD.

-- Lanzamos una consulta sobre una tabla particionada por fecha (una partición por trimestre)
-- Configuramos AUTOTRACE para mostrar únicamente el plan de ejecución
SET AUTOTRACE TRACEONLY EXPLAIN

-- En el plan de ejecución podemos ver las columnas "Pstart" y "Pstop"
-- Estas columnas nos muestran las particiones a las que se va a acceder
--   · Se corresponde con la columna PARTITION_POSITION de la vista DBA_TAB_PARTITIONS
-- Más información en http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_warehouse.htm#i1007993
SELECT SUM(AMOUNT_SOLD)
FROM SH.SALES
WHERE TIME_ID BETWEEN TO_DATE('01-JAN-2001', 'DD-MON-YYYY') and TO_DATE('01-APR-2001', 'DD-MON-YYYY');

9. En la documentación inicial se pueden consultar las posibles operaciones que pueden aparecer en el plan de ejecución con una explicación de cada una. A continuación adjunto un documento de Oracle muy completo para entender planes de ejecución que incluyan Parallel Execution -> http://www.oracle.com/technetwork/articles/datawarehouse/twp-parallel-execution-fundamentals-133639.pdf

10. En versiones 11gR2 podemos ver la siguiente frase en los planes de ejecución «cardinality feedback used for this statement». Es una funcionalidad que ajusta la cardinalidad de las operaciones en función de los estadísticas que se obtienen al ejecutar sentencias sobre la BD. Adjunto una página donde se explica con un ejemplo muy descriptivo -> https://blogs.oracle.com/optimizer/entry/cardinality_feedback.

-- Vamos a coger el ejemplo de la página que he adjuntado para que veáis como funciona "Cardinality Feedback"
-- Primero limpiamos la Shared Pool
ALTER SYSTEM FLUSH SHARED_POOL;

-- Lanzamos la consulta de ejemplo
SELECT PRODUCT_NAME
FROM OE.ORDER_ITEMS O, OE.PRODUCT_INFORMATION P
WHERE O.UNIT_PRICE = 15 AND QUANTITY > 1
AND P.PRODUCT_ID = O.PRODUCT_ID;

-- Obtenemos el plan de ejecución
-- Las operaciones más destacadaos son TABLES ACCESS FULL y INDEX UNIQUE SCAN
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

-- Volvemos a lanzar la consulta para demostrar esta funcionalidad
SELECT PRODUCT_NAME
FROM OE.ORDER_ITEMS O, OE.PRODUCT_INFORMATION P
WHERE O.UNIT_PRICE = 15 AND QUANTITY > 1
AND P.PRODUCT_ID = O.PRODUCT_ID;

-- Si sacamos ahora el plan de ejecución utilizado comprobaremos que es diferente
--   · Las operaciones destacadas son dos TABLE ACCESS FULL a las tablas ORDER_ITEMS y PRODUCT_INFORMATION
--   · El optimizado tras ejecutar la consulta ha comprobado que las estadísticas no son correctas
--   · En la segunda ejecución ha utilizado las estimaciones observadas para generar un plan mejor
--   · Además podemos comprobar el siguinte mensaje "cardinality feedback used for this statement"
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

-- Se pueden obtener los cursores que han utilizado Cardinality Feedback a través de la vista V$SQL_SHARED_CURSOR
-- La siguiente consulta muestra que hay dos cursores para la consulta que hemos lanzado (SQL_ID='g8mza363us2mg')
-- Pero uno de ellos utiliza Cardinality Feedback (USE_FEEDBACK_STATS='Y')
SELECT SQL_ID, CHILD_NUMBER, USE_FEEDBACK_STATS FROM V$SQL_SHARED_CURSOR WHERE SQL_ID='g8mza363us2mg';