Qry Optmzn

Database
Enthusiast

Qry Optmzn

Hi!

I have this qry which runs for hours but the explain shows time as only a few seconds, does not recommened any stats. Index wizard does not suggest a index. Can you plz help me imrving the perf ?
SELECT X1.c1 AS c1 , X1.c2 AS c2 , X1.c3 AS c3 , X1.c4 AS c4 ,
X1.c5 AS c5 , X1.c6 AS c6 , X1.c7 AS c7 , X1.c8 AS c8 , X1.c9 AS c9 ,
X1.c10 AS c10 , X1.c11 AS c11 , X1.c12 AS c12 , X1.c13 AS c13 ,
X1.c14 AS c14 , X1.c15 AS c15 , X1.c16 AS c16 , X1.c17 AS c17
FROM (
SELECT X1.c1 AS c1 , X1.c2 AS c2 , X1.c3 AS c3 , X1.c4 AS c4 ,
X1.c5 AS c5 , X1.c6 AS c6 , X1.c7 AS c7 , X1.c8 AS c8 , X1.c9 AS c9 ,
X1.c10 AS c10 , X1.c11 AS c11 , X1.c12 AS c12 , X1.c13 AS c13 ,
X1.c14 AS c14 , X1.c15 AS c15 , X1.c16 AS c16 , X1.c17 AS c17
FROM (
SELECT D.NAME AS c1 , D.P_NAME AS c2 ,
D.C_NAME AS c3 , D.C_NUM AS c4 , D.C_STATUS AS c5 ,
D.C_START_DT AS c6 , D.C_END_DT AS c7 ,
D.C_TYPE AS c8 , C.L__TS AS c9 , G.O_NAME AS c10 ,
G.M_TYP AS c11 , G.O_TYPE AS c12 , B.SGMT_NAME AS c13 ,
COUNT ( DISTINCT E.CONTACT ) AS c14 , A.O_NAME AS c15 ,
A.M_TYP AS c16 , A.INTGN_ID AS c17 ,
B.INTGN_ID AS c18 , G.INTGN_ID AS c19 ,
D.C_ID AS c20 , D.PROG_ID AS c21 , D.MKTPLN_ID AS c22
FROM O_D A , SEG_D B , LD_W_D C ,
SRC_D D LEFT OUTER JOIN SRC_DX E
ON E.ROW = D.ROW , SRC_OFFR_H H ,
C_HIST_F E , C_HIST_FX F , O_D G
WHERE ( B.ROW = E.SEG
AND C.ROW = E.LD_W
AND D.ROW = H.SRC
AND G.ROW = H.O
AND D.ROW = E.SRC
AND E.ROW = F.ROW
AND F.DCP = A.ROW
AND C.L__TS >= tImEsTaMp'2011-01-01 00:00:00'
AND E.X_TYP IS NULL
AND ( D.C_TYPE = 'D'
OR D.C_TYPE = 'I' )
AND E.X_DEL_FLG IS NULL )
GROUP BY B.INTGN_ID , B.SGMT_NAME , G.INTGN_ID ,
G.M_TYP , G.O_NAME , G.O_TYPE ,
C.L__TS , D.C_START_DT , D.C_END_DT ,
D.C_ID , D.C_NAME , D.C_NUM ,
D.C_STATUS , D.C_TYPE , D.PROG_ID ,
D.P_NAME , D.MKTPLN_ID , D.NAME ,
A.INTGN_ID , A.M_TYP , A.O_NAME ) X1 ) X1

1) First, we lock a distinct pseudo table for read on a
RowHash to prevent global deadlock for E.
2) Next, we lock a distinct pseudo table for read on a
RowHash to prevent global deadlock for H.
3) We lock a distinct pseudo table for read on a RowHash to
prevent global deadlock for A.
4) We lock a distinct pseudo table for read on a RowHash to
prevent global deadlock for E.
5) We lock a distinct pseudo table for read on a RowHash to
prevent global deadlock for F.
6) We lock E for read, we lock H for read, we
lock A for read, we lock E for read, and
we lock F for read.
7) We do an all-AMPs JOIN step from E by way of a RowHash
match scan with a condition of (E.X_DEL_FLG IS
NULL), which is joined to F by way of a RowHash match
scan with no residual conditions. E and F
are joined using a merge join, with a join condition of (
E.ROW = F.ROW). The input table
E will not be cached in memory, but it is eligible for
synchronized scanning. The result goes into Spool 4 (all_amps),
which is redistributed by hash code to all AMPs. Then we do a
SORT to order Spool 4 by row hash. The size of Spool 4 is
estimated with low confidence to be 1 row. The estimated time for
this step is 0.01 seconds.
8) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of a
RowHash match scan, which is joined to A by way of a
RowHash match scan with no residual conditions. Spool 4 and
A are joined using a merge join, with a join condition
of (DCP = A.ROW). The result goes into
Spool 5 (group_amps), which is redistributed by hash code to all
AMPs. Then we do a SORT to order Spool 5 by row hash. The size
of Spool 5 is estimated with low confidence to be 1 row. The
estimated time for this step is 0.01 seconds.
9) We do a group-AMPs JOIN step from C by way of a
RowHash match scan with a condition of (
C.L__TS >= TIMESTAMP '2011-01-01 00:00:00'),
which is joined to Spool 5 (Last Use) by way of a RowHash match
scan. C and Spool 5 are joined using a merge join,
with a join condition of (C.ROW = LD_W).
The result goes into Spool 6 (group_amps), which is redistributed
by hash code to all AMPs. Then we do a SORT to order Spool 6 by
row hash. The size of Spool 6 is estimated with low confidence to
be 1 row. The estimated time for this step is 0.00 seconds.
10) We do a group-AMPs JOIN step from D by way of a
RowHash match scan with a condition of ((D.C_TYPE
= 'D') OR (D.C_TYPE = 'I')), which is
joined to Spool 6 (Last Use) by way of a RowHash match scan.
D and Spool 6 are joined using a merge join, with a
join condition of (D.ROW = SRC). The
result goes into Spool 7 (group_amps), which is redistributed by
hash code to all AMPs. Then we do a SORT to order Spool 7 by row
hash. The size of Spool 7 is estimated with low confidence to be
1 row. The estimated time for this step is 0.01 seconds.
11) We execute the following steps in parallel.
1) We do a group-AMPs JOIN step from B by way of a
RowHash match scan with no residual conditions, which is
joined to Spool 7 (Last Use) by way of a RowHash match scan.
B and Spool 7 are joined using a merge join, with
a join condition of (B.ROW = SEG).
The result goes into Spool 8 (all_amps), which is duplicated
on all AMPs. The size of Spool 8 is estimated with low
confidence to be 150 rows. The estimated time for this step
is 0.01 seconds.
2) We do an all-AMPs RETRIEVE step from H by way of
an all-rows scan with no residual conditions into Spool 9
(all_amps), which is built locally on the AMPs. The size of
Spool 9 is estimated with high confidence to be 5,809 rows.
The estimated time for this step is 0.00 seconds.
12) We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of an
all-rows scan, which is joined to Spool 9 (Last Use) by way of an
all-rows scan. Spool 8 and Spool 9 are joined using a single
partition hash join, with a join condition of ((ROW =
SRC) AND (src=SRC)). The result goes into
Spool 10 (all_amps), which is redistributed by hash code to all
AMPs. Then we do a SORT to order Spool 10 by row hash. The size
of Spool 10 is estimated with low confidence to be 1 row. The
estimated time for this step is 0.01 seconds.
13) We do an all-AMPs JOIN step from G by way of a RowHash
match scan with no residual conditions, which is joined to Spool
10 (Last Use) by way of a RowHash match scan. G and
Spool 10 are joined using a merge join, with a join condition of (
G.ROW = O). The result goes into Spool
11 (all_amps), which is redistributed by hash code to all AMPs.
Then we do a SORT to order Spool 11 by row hash. The size of
Spool 11 is estimated with low confidence to be 1 row. The
estimated time for this step is 0.01 seconds.
14) We do an all-AMPs JOIN step from Spool 11 (Last Use) by way of a
RowHash match scan, which is joined to E by way of a
RowHash match scan with no residual conditions. Spool 11 and
E are left outer joined using a merge join, with a
join condition of (E.ROW = ROW). The result
goes into Spool 12 (all_amps), which is built locally on the AMPs.
The size of Spool 12 is estimated with low confidence to be 1 row.
The estimated time for this step is 0.01 seconds.
15) We do an all-AMPs RETRIEVE step from Spool 12 (Last Use) by way of
an all-rows scan with a condition of (X_TYP IS NULL) into
Spool 3 (all_amps), which is built locally on the AMPs. The size
of Spool 3 is estimated with low confidence to be 1 row. The
estimated time for this step is 0.01 seconds.
16) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by
way of an all-rows scan, and the grouping identifier in field 1.
Aggregate Intermediate Results are computed globally, then placed
in Spool 14. The size of Spool 14 is estimated with no confidence
to be 1 row. The estimated time for this step is 0.01 seconds.
17) We do an all-AMPs SUM step to aggregate from Spool 14 (Last Use)
by way of an all-rows scan, and the grouping identifier in field 1.
Aggregate Intermediate Results are computed globally, then placed
in Spool 16. The size of Spool 16 is estimated with no confidence
to be 1 row. The estimated time for this step is 0.01 seconds.
18) We do an all-AMPs RETRIEVE step from Spool 16 (Last Use) by way of
an all-rows scan into Spool 1 (all_amps), which is built locally
on the AMPs. The size of Spool 1 is estimated with no confidence
to be 1 row. The estimated time for this step is 0.01 seconds.
19) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of
an all-rows scan into Spool 18 (group_amps), which is built
locally on the AMPs. The size of Spool 18 is estimated with no
confidence to be 1 row. The estimated time for this step is 0.01
seconds.
20) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 18 are sent back to the user as the result
of statement 1. The total estimated time is 0.10 seconds.

Thanks!