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 (a ejecutar solamente una vez)
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 alternativo de instalación y de personalización.
Explicar la sentencia
Anteponer a cualquier sentencia SQL el comando explain plan
for para almacenar los detalles del plan de ejecución dentro de las tablas.
Esta etapa aún no muestra el plan de ejecución, sólo la almacena dentro de la base de datos.
EXPLAIN PLAN FOR SELECT 1 FROM sysibm.sysdummy1Mostrar el plan de ejecución almacenado
IBM provee algunas herramientas para mostrar 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 mostrar la información como generalmente me interesa. Realmente, esto es lo que recomienda la documentación de DB2.
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 fácilmente:
SELECT * FROM last_explainedEl 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 en 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
);
