de Martin LE TARNEC.

Obtener un plan de ejecución


Obtener un plan de ejecución desde DB2 LUW es un procedimiento en tres etapas de las cuales la primera es la configuración.

Crear las tablas requeridas (de inmediato)

La manera recomendada para crear las tablas requeridas se hace ejecutando el siguiente procedimiento:

CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', 
        CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))

Así se instalarán las tablas requeridas en el esquema SYSTOOLS (por ejemplo, SYSTOOLS.EXPLAIN_STREAM).

Dentro de la documentación se explica un procedimiento alterno de instalación y de personalización.

Explicar la sentencia

Prefija cualquier sentencia SQL con explain plan for para almacenarlos detalles del plan de ejecución dentro de las tablas.

Esta etapa aún no muestra el plan de ejecución, solo la almacena dentro de la base de datos.

EXPLAIN PLAN FOR SELECT 1 FROM sysibm.sysdummy1

Desplegar el plan de ejecución almacenado

IBM provee algunas herramientas para desplegar los datos almacenados dentro de las tablas. Sin embargo, el formato de la salida no es tan práctico como podría ser, así que uso mi propia sentencia SQL para desplegar la información como generalmente me interesa. Realmente, la documentación DB2 recomienda hacer lo siguiente.

Abajo, se puede encontrar la definición de la vista last_explained que devuelve un plan de ejecución de la última sentencia que ha ejecutado el usuario actual en esta base de datos. Observe que no se limita a la sesión actual. La vista puede utilizarse así de simple:

SELECT * FROM last_explained

El resultado podría verse así (la siguiente sección tiene los detalles para interpretarlos):

Explain Plan                                                                                        
----------------------------------------------
ID | Operation      |             Rows | Cost                                                       
 1 | RETURN         |                  |    0                                                       
 2 |  TBSCAN GENROW | 1 of 1 (100.00%) |    0                                                       
                                                                                                    
Predicate Information                                                                               
                                                                                                    
Explain plan by Markus Winand - NO WARRANTY                                                         
http://use-the-index-luke.com/s/last_explained 

Aviso

Esta vista es muy experimental; esto se provee sin ninguna garantía. Fue mejorada por Ember Crooks incluyendo “Actual Rows” si está disponible. Es recomendable leer su articulo para ver cómo recolectarlos.

Requerimiento mínimo: DB2 LUW 9.7 FixPack 4 (nota: Express-C está disponible solamente con el FixPack 2)

Sugerencia

Esta vista está disponible sobre GitHub (descarga directa ).

-- Copyright (c) 2014-2015, Markus Winand - NO WARRANTY
-- Modifications by Ember Crooks - NO WARRANTY
-- Info & license: http://use-the-index-luke.com/s/last_explained
--
--#SET TERMINATOR ;

CREATE OR REPLACE VIEW last_explained AS
WITH tree(operator_ID, level, path, explain_time, cycle)
AS
(
SELECT 1 operator_id 
     , 0 level
     , CAST('001' AS VARCHAR(1000)) path
     , max(explain_time) explain_time
     , 0
  FROM SYSTOOLS.EXPLAIN_OPERATOR O
 WHERE O.EXPLAIN_REQUESTER = SESSION_USER

UNION ALL

SELECT s.source_id
     , level + 1
     , tree.path || '/' || LPAD(CAST(s.source_id AS VARCHAR(3)), 3, '0')  path
     , tree.explain_time
     , CASE WHEN (POSITION(path IN '%/' || LPAD(CAST(s.source_id AS VARCHAR(3)), 3, '0')  || '/%' USING OCTETS) >0)
            THEN 1
            ELSE 0
       END
  FROM tree
     , SYSTOOLS.EXPLAIN_STREAM S
 WHERE s.target_id    = tree.operator_id
   AND s.explain_time = tree.explain_time
   AND S.Object_Name IS NULL
   AND S.explain_requester = SESSION_USER
   AND tree.cycle = 0
   AND level < 100
)
SELECT * 
  FROM (
SELECT "Explain Plan"
  FROM (
SELECT CAST(   LPAD(id,        MAX(LENGTH(id))        OVER(), ' ')
            || ' | ' 
            || RPAD(operation, MAX(LENGTH(operation)) OVER(), ' ')
            || ' | ' 
            || LPAD(rows,      MAX(LENGTH(rows))      OVER(), ' ')
            || ' | ' 
            -- Don't show ActualRows columns if there are no actuals available at all 
            || CASE WHEN COUNT(ActualRows) OVER () > 1 -- the heading 'ActualRows' is always present, so "1" means no OTHER values
                    THEN LPAD(ActualRows, MAX(LENGTH(ActualRows)) OVER(), ' ') || ' | ' 
                    ELSE ''
               END
            || LPAD(cost,      MAX(LENGTH(cost))      OVER(), ' ')
         AS VARCHAR(100)) "Explain Plan"
     , path
  FROM (
SELECT 'ID' ID
     , 'Operation' Operation
     , 'Rows' Rows
     , 'ActualRows' ActualRows
     , 'Cost' Cost
     , '0' Path
  FROM SYSIBM.SYSDUMMY1
-- TODO: UNION ALL yields duplicate. where do they come from?
UNION
SELECT CAST(tree.operator_id as VARCHAR(254)) ID
     , CAST(LPAD(' ', tree.level, ' ')
       || CASE WHEN tree.cycle = 1
               THEN '(cycle) '
               ELSE ''
          END     
       || COALESCE (
             TRIM(O.Operator_Type)
          || COALESCE(' (' || argument || ')', '') 
          || ' '
          || COALESCE(S.Object_Name,'')
          , ''
          )
       AS VARCHAR(254)) AS OPERATION
     , COALESCE(CAST(rows AS VARCHAR(254)), '') Rows
     , CAST(ActualRows as VARCHAR(254)) ActualRows -- note: no coalesce
     , COALESCE(CAST(CAST(O.Total_Cost AS BIGINT) AS VARCHAR(254)), '') Cost
     , path
  FROM tree
  LEFT JOIN ( SELECT i.source_id
              , i.target_id
              , CAST(CAST(ROUND(o.stream_count) AS INTEGER) AS VARCHAR(12))
                || ' of '
                || CAST (total_rows AS VARCHAR(12))
                || CASE WHEN total_rows > 0
                         AND ROUND(o.stream_count) <= total_rows THEN
                   ' ('
                   || LPAD(CAST (ROUND(ROUND(o.stream_count)/total_rows*100,2)
                          AS NUMERIC(5,2)), 6, ' ')
                   || '%)'
                   ELSE ''
                   END rows
              , CASE WHEN act.actual_value is not null then
                CAST(CAST(ROUND(act.actual_value) AS INTEGER) AS VARCHAR(12))
                || ' of '
                || CAST (total_rows AS VARCHAR(12))
                || CASE WHEN total_rows > 0 THEN
                   ' ('
                   || LPAD(CAST (ROUND(ROUND(act.actual_value)/total_rows*100,2)
                          AS NUMERIC(5,2)), 6, ' ')
                   || '%)'
                   ELSE NULL
                   END END ActualRows
              , i.object_name
              , i.explain_time
         FROM (SELECT MAX(source_id) source_id
                    , target_id
                    , MIN(CAST(ROUND(stream_count,0) AS INTEGER)) total_rows
                    , CAST(LISTAGG(object_name) AS VARCHAR(50)) object_name
                    , explain_time
                 FROM SYSTOOLS.EXPLAIN_STREAM
                WHERE explain_time = (SELECT MAX(explain_time)
                                        FROM SYSTOOLS.EXPLAIN_OPERATOR
                                       WHERE EXPLAIN_REQUESTER = SESSION_USER
                                     )
                GROUP BY target_id, explain_time
              ) I
         LEFT JOIN SYSTOOLS.EXPLAIN_STREAM O
           ON (    I.target_id=o.source_id
               AND I.explain_time = o.explain_time
               AND O.EXPLAIN_REQUESTER = SESSION_USER
              )
         LEFT JOIN SYSTOOLS.EXPLAIN_ACTUALS act
           ON (    act.operator_id  = i.target_id
               AND act.explain_time = i.explain_time
               AND act.explain_requester = SESSION_USER
               AND act.ACTUAL_TYPE  like 'CARDINALITY%'
              )
       ) s
    ON (    s.target_id    = tree.operator_id
        AND s.explain_time = tree.explain_time
       )
  LEFT JOIN SYSTOOLS.EXPLAIN_OPERATOR O
    ON (    o.operator_id  = tree.operator_id
        AND o.explain_time = tree.explain_time
        AND o.explain_requester = SESSION_USER
       ) 
  LEFT JOIN (SELECT LISTAGG (CASE argument_type
                             WHEN 'UNIQUE' THEN
                                  CASE WHEN argument_value = 'TRUE'
                                       THEN 'UNIQUE'
                                  ELSE NULL
                                  END
                             WHEN 'TRUNCSRT' THEN
                                  CASE WHEN argument_value = 'TRUE'
                                       THEN 'TOP-N'
                                  ELSE NULL
                                  END   
                             WHEN 'SCANDIR' THEN
                                  CASE WHEN argument_value != 'FORWARD'
                                       THEN argument_value
                                  ELSE NULL
                                  END                     
                             ELSE argument_value     
                             END
                           , ' ') argument
                  , operator_id
                  , explain_time
               FROM SYSTOOLS.EXPLAIN_ARGUMENT EA
              WHERE argument_type IN ('AGGMODE'   -- GRPBY
                                     , 'UNIQUE', 'TRUNCSRT' -- SORT
                                     , 'SCANDIR' -- IXSCAN, TBSCAN
                                     , 'OUTERJN' -- JOINs
                                     )
                AND explain_requester = SESSION_USER
              GROUP BY explain_time, operator_id

            ) A
    ON (    a.operator_id  = tree.operator_id
        AND a.explain_time = tree.explain_time
       )
     ) O
UNION ALL
SELECT 'Explain plan (c) 2014-2015
 by Markus Winand - NO WARRANTY - V20150116'
     , 'Z0' FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 'Modifications by Ember Crooks - NO WARRANTY'
     , 'Z1' FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 'http://use-the-index-luke.com/s/last_explained'
     , 'Z2' FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '', 'A' FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '', 'Y' FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 'Predicate Information', 'AA' FROM SYSIBM.SYSDUMMY1
UNION
 ALL
SELECT CAST (LPAD(CASE WHEN operator_id = LAG  (operator_id)
                                          OVER (PARTITION BY operator_id
                                                    ORDER BY pred_order
                                               )
                       THEN ''
                       ELSE operator_id || ' - '
                  END
                , MAX(LENGTH(operator_id )+4) OVER()
                , ' ')
             || how_applied
             || ' ' 
             || predicate_text
          AS VARCHAR(100)) "Predicate Information"
     , 'P' || LPAD(id_order, 5, '0') || pred_order path
  FROM (SELECT CAST(operator_id AS VARCHAR(254)) operator_id
             , LPAD(trim(how_applied)
                  ,  MAX (LENGTH(TRIM(how_applied)))
                    OVER (PARTITION BY operator_id)
                  , ' '
               ) how_applied
               -- next: capped to length 80 to avoid
               -- SQL0445W  Value "..." has been truncated.  SQLSTATE=01004
               -- error when long literal values may appear (space padded!)
             , CAST(substr(predicate_text, 1, 80) AS VARCHAR(80)) predicate_text
             , CASE how_applied WHEN 'START' THEN '1'
                                WHEN 'STOP'  THEN '2'
                                WHEN 'SARG'  THEN '3'
                                ELSE '9'
               END pred_order
             , operator_id id_order
          FROM systools.explain_predicate p
         WHERE explain_time = (SELECT MAX(explain_time)
                                 FROM systools.explain_operator)
       )
)
ORDER BY path
);

Acerca del autor

Foto de Markus Winand

Markus Winand enseña eficientemente SQL, en casa y online. Minimiza el tiempo de desarrollo utilizando moderno SQL y optimiza el tiempo de ejecución con indexación inteligente. Para ello también ha publicado el libro SQL Performance Explained.

“Use The Index, Luke” de Markus Winand se halla bajo licencia Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Aspectos legales | Contacto | SIN GARANTÍA | Marcas | Privacy | CC-BY-NC-ND 3.0 licencia