Neoview Query Guide (R2.2)

SELECT
SUM(CASE WHEN NBR < 1 THEN 1 ELSE 0 END) AS COST_L1,
SUM(CASE WHEN NBR BETWEEN 1 AND 5 THEN 1 ELSE 0 END) AS COST_B1_5,
SUM(CASE WHEN NBR BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS COST_B6_10,
SUM(CASE WHEN NBR BETWEEN 11 AND 20 THEN 1 ELSE 0 END) AS COST_B11_20,
SUM(CASE WHEN NBR BETWEEN 21 AND 50 THEN 1 ELSE 0 END) AS COST_B21_50,
SUM(CASE WHEN NBR > 50 THEN 1 ELSE 0 END) AS COST_G50
FROM (
SELECT
STATEMENT_NAME,
TOTAL_COST AS NBR
FROM EXPLAIN_TAB
WHERE OPERATOR = 'ROOT'
FOR READ UNCOMMITTED ACCESS
) AS T;
To show the count of queries using ESP processes:
SELECT COUNT(DISTINCT STATEMENT_NAME)
FROM EXPLAIN_TAB
WHERE OPERATOR = 'ESP_EXCHANGE'
FOR READ UNCOMMITTED ACCESS;
To show the count of queries using sorts:
SELECT COUNT(DISTINCT STATEMENT_NAME)
FROM EXPLAIN_TAB
WHERE OPERATOR = 'SORT'
FOR READ UNCOMMITTED ACCESS;
To show the queries that use MDAM:
SELECT DESCRIPTION
FROM EXPLAIN_TAB
WHERE DESCRIPTION LIKE '%mdam%'
FOR READ UNCOMMITTED ACCESS;
Capturing Query Plans for Review 17