Qry very sloww!

Database

Qry very sloww!

Hi!

This qry runs for more than an hour . I have simplified by replacing the column names with * . But there are all left outer joins and the union clause. the union cannot be changed to union all bcuz the dupes need to be removed. The collect stats are all multicolumn. Index wizard does not suggest a sec idx. Al the tables except Actvty_1 have upi as rowid. actvty_1 has the UPI as (SRC_NUM_ID ,ROWID ) and is partitioned by SRC_NUM_ID . POSN_X shows a skew of 25 and POSN_d shows a skew of 14. Can we reduce this skew further? So how can we impv the perf? Any help is appreciated.
SELECT *
FROM ACTVTY_1 A1
LEFT OUTER JOIN POSN_X PX
ON A1.POSN = PX.ROWID
LEFT OUTER JOIN PRSN_X PRDX
ON A1.OWNER = PRDX.ROWID
LEFT OUTER JOIN POSN_D P
ON A1.POSN = P.ROWID
LEFT OUTER JOIN PRSN_D PRD
ON A1.CNTCT = PRD.ROWID
LEFT OUTER JOIN PTY_X OX
ON A1.PTY=OX.ROWID
LEFT OUTER JOIN PTY_D O
ON A1.PTY=O.ROWID
WHERE A1.SRC_NUM_ID = 1
AND A1.REC_TYP='ABC'
UNION
SELECT *
FROM ACTVTY_1 A1
LEFT OUTER JOIN POSN_X PX
ON A1.POSN = PX.ROWID
LEFT OUTER JOIN PRSN_X PRDX
ON A1.OWNER = PRDX.ROWID
LEFT OUTER JOIN POSN_D P
ON A1.POSN = P.ROWID
LEFT OUTER JOIN PRSN_D PRD
ON A1.CNTCT = PRD.ROWID
WHERE A1.SRC_NUM_ID = 1
ORDER BY 16
1) First, we lock a distinct "pseudo table" for read on a
RowHash to prevent global deadlock for A1.
2) Next, we lock a distinct "pseudo table" for read on a
RowHash to prevent global deadlock for OX.
3) We lock a distinct "pseudo table" for read on a RowHash to
prevent global deadlock for O.
4) We lock a distinct "pseudo table" for read on a RowHash to
prevent global deadlock for PRDX.
5) We lock a distinct "pseudo table" for read on a RowHash to
prevent global deadlock for PX.
6) We lock a distinct "pseudo table" for read on a RowHash to
prevent global deadlock for PRD.
7) We lock a distinct "pseudo table" for read on a RowHash to
prevent global deadlock for P.
8) We lock A1 for read, we lock OX for read, we lock
O for read, we lock PRDX for read, we lock PX
for read, we lock PRD for read, and we lock P for
read.
9) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from PX by way of an
all-rows scan with no residual conditions into Spool 2
(all_amps) (compressed columns allowed), which is duplicated
on all AMPs. Then we do a SORT to order Spool 2 by row hash.
The size of Spool 2 is estimated with high confidence to be
1,759,050 rows. The estimated time for this step is 0.18
seconds.
2) We do an all-AMPs RETRIEVE step from a single partition of
A1 with a condition of ("A1.SRC_NUM_ID = 1")
with a residual condition of ("(A1.REC_TYP =
'ABC') AND (A1.SRC_NUM_ID = 1)") into Spool 3
(all_amps) (compressed columns allowed), which is built
locally on the AMPs. Then we do a SORT to order Spool 3 by
row hash. The size of Spool 3 is estimated with low
confidence to be 963,279 rows. The estimated time for this
step is 2.84 seconds.
10) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of
a RowHash match scan, which is joined to Spool 3 (Last Use) by
way of a RowHash match scan. Spool 2 and Spool 3 are
right outer joined using a merge join, with a join condition
of ("POSN = ROWID"). The result goes into Spool 4
(all_amps) (compressed columns allowed), which is built
locally on the 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 963,279 rows. The estimated time for this
step is 3.22 seconds.
2) We do an all-AMPs RETRIEVE step from O by way of an
all-rows scan with no residual conditions into Spool 7
(all_amps) (compressed columns allowed), which is duplicated
on all AMPs. Then we do a SORT to order Spool 7 by row hash.
The size of Spool 7 is estimated with high confidence to be
255,610,200 rows. The estimated time for this step is 30.02
seconds.
11) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of
a RowHash match scan, which is joined to Spool 7 (Last Use) by
way of a RowHash match scan. Spool 4 and Spool 7 are
left outer joined using a merge join, with a join condition of
("PTY = ROWID"). The result goes into Spool 8
(all_amps) (compressed columns allowed), which is built
locally on the AMPs. Then we do a SORT to order Spool 8 by
row hash. The size of Spool 8 is estimated with low
confidence to be 963,279 rows. The estimated time for this
step is 4.81 seconds.
2) We do an all-AMPs RETRIEVE step from OX by way of an
all-rows scan with no residual conditions into Spool 9
(all_amps) (compressed columns allowed), which is duplicated
on all AMPs. Then we do a SORT to order Spool 9 by row hash.
The size of Spool 9 is estimated with high confidence to be
255,610,200 rows. The estimated time for this step is 30.82
seconds.
12) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of
a RowHash match scan, which is joined to Spool 9 (Last Use) by
way of a RowHash match scan. Spool 8 and Spool 9 are
left outer joined using a merge join, with a join condition of
("PTY = ROWID"). The result goes into Spool 10
(all_amps) (compressed columns allowed), 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 963,279 rows. The estimated time
for this step is 3 minutes and 16 seconds.
2) We do an all-AMPs RETRIEVE step from P by way of an
all-rows scan with no residual conditions into Spool 11
(all_amps) (compressed columns allowed), which is duplicated
on all AMPs. Then we do a SORT to order Spool 11 by row hash.
The size of Spool 11 is estimated with high confidence to be
5,357,250 rows. The estimated time for this step is 0.60
seconds.
13) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of
a RowHash match scan, which is joined to PRD by way of
a RowHash match scan with no residual conditions. Spool 10
and PRD are left outer joined using a merge join, with
a join condition of ("CNTCT = PRD.ROWID"). The
input table PRD will not be cached in memory. The
result goes into Spool 12 (all_amps) (compressed columns
allowed), which is built locally on the AMPs. Then we do a
SORT to order Spool 12 by row hash. The size of Spool 12 is
estimated with low confidence to be 963,279 rows. The
estimated time for this step is 33.87 seconds.
2) We do an all-AMPs RETRIEVE step from PRDX by way of an
all-rows scan with no residual conditions into Spool 13
(all_amps) (compressed columns allowed), which is duplicated
on all AMPs. Then we do a SORT to order Spool 13 by row hash.
The result spool file will not be cached in memory. The size
of Spool 13 is estimated with high confidence to be
1,860,071,550 rows. The estimated time for this step is 6
minutes and 5 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 Spool 12 (Last Use) by way
of a RowHash match scan. Spool 11 and Spool 12 are right outer
joined using a merge join, with a join condition of (
"POSN = ROWID"). The result goes into Spool 14
(all_amps) (compressed columns allowed), which is built locally on
the AMPs. Then we do a SORT to order Spool 14 by row hash. The
size of Spool 14 is estimated with low confidence to be 963,279
rows. The estimated time for this step is 3.32 seconds.
15) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 13 (Last Use) by way of
a RowHash match scan, which is joined to Spool 14 (Last Use)
by way of a RowHash match scan. Spool 13 and Spool 14 are
right outer joined using a merge join, with a join condition
of ("OWNER = ROWID"). The result goes into Spool 1
(all_amps), which is redistributed by hash code to all AMPs.
The size of Spool 1 is estimated with low confidence to be
963,279 rows. The estimated time for this step is 26.21
seconds.
2) We do an all-AMPs RETRIEVE step from P by way of an
all-rows scan with no residual conditions into Spool 19
(all_amps) (compressed columns allowed), which is duplicated
on all AMPs. Then we do a SORT to order Spool 19 by row hash.
The size of Spool 19 is estimated with high confidence to be
5,357,250 rows. The estimated time for this step is 0.60
seconds.
3) We do an all-AMPs RETRIEVE step from a single partition of
A1 with a condition of ("A1.SRC_NUM_ID = 1")
with a residual condition of ("A1.SRC_NUM_ID = 1")
into Spool 20 (all_amps) (compressed columns allowed), which
is built locally on the AMPs. Then we do a SORT to order
Spool 20 by row hash. The result spool file will not be
cached in memory. The size of Spool 20 is estimated with low
confidence to be 84,410,905 rows. The estimated time for this
step is 7 minutes and 17 seconds.
4) We do an all-AMPs RETRIEVE step from PRD by way of an
all-rows scan with no residual conditions into Spool 21
(all_amps) (compressed columns allowed), which is duplicated
on all AMPs. Then we do a SORT to order Spool 21 by row hash.
The result spool file will not be cached in memory. The size
of Spool 21 is estimated with high confidence to be
1,875,777,750 rows. The estimated time for this step is 4
minutes and 55 seconds.
16) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 19 (Last Use) by way of
a RowHash match scan, which is joined to Spool 20 (Last Use)
by way of a RowHash match scan. Spool 19 and Spool 20 are
right outer joined using a merge join, with a join condition
of ("POSN = ROWID"). The result goes into Spool 22
(all_amps) (compressed columns allowed), which is built
locally on the AMPs. Then we do a SORT to order Spool 22 by
row hash. The result spool file will not be cached in memory.
The size of Spool 22 is estimated with low confidence to be
84,410,905 rows. The estimated time for this step is 8
minutes and 24 seconds.
2) We do an all-AMPs RETRIEVE step from PRDX by way of an
all-rows scan with no residual conditions into Spool 25
(all_amps) (compressed columns allowed), which is duplicated
on all AMPs. Then we do a SORT to order Spool 25 by row hash.
The result spool file will not be cached in memory. The size
of Spool 25 is estimated with high confidence to be
1,860,071,550 rows. The estimated time for this step is 6
minutes and 5 seconds.
17) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 21 (Last Use) by way of
a RowHash match scan, which is joined to Spool 22 (Last Use)
by way of a RowHash match scan. Spool 21 and Spool 22 are
right outer joined using a merge join, with a join condition
of ("CNTCT = ROWID"). The result goes into Spool 26
(all_amps) (compressed columns allowed), which is built
locally on the AMPs. Then we do a SORT to order Spool 26 by
row hash. The result spool file will not be cached in memory.
The size of Spool 26 is estimated with low confidence to be
84,410,905 rows. The estimated time for this step is 8
minutes and 5 seconds.
2) We do an all-AMPs RETRIEVE step from PX by way of an
all-rows scan with no residual conditions into Spool 29
(all_amps) (compressed columns allowed), which is duplicated
on all AMPs. Then we do a SORT to order Spool 29 by row hash.
The size of Spool 29 is estimated with high confidence to be
1,759,050 rows. The estimated time for this step is 0.18
seconds.
18) We do an all-AMPs JOIN step from Spool 25 (Last Use) by way of a
RowHash match scan, which is joined to Spool 26 (Last Use) by way
of a RowHash match scan. Spool 25 and Spool 26 are right outer
joined using a merge join, with a join condition of ("OWNER =
ROWID"). The result goes into Spool 30 (all_amps) (compressed
columns allowed), which is built locally on the AMPs. Then we do
a SORT to order Spool 30 by row hash. The result spool file will
not be cached in memory. The size of Spool 30 is estimated with
low confidence to be 84,410,905 rows. The estimated time for this
step is 8 minutes and 25 seconds.
19) We do an all-AMPs JOIN step from Spool 29 (Last Use) by way of a
RowHash match scan, which is joined to Spool 30 (Last Use) by way
of a RowHash match scan. Spool 29 and Spool 30 are right outer
joined using a merge join, with a join condition of (
"POSN = ROWID"). The result goes into Spool 1
(all_amps), which is redistributed by hash code to all AMPs. Then
we do a SORT to order Spool 1 by the sort key in spool field1
eliminating duplicate rows. The result spool file will not be
cached in memory. The size of Spool 1 is estimated with low
confidence to be 42,687,092 rows. The estimated time for this
step is 16 minutes and 31 seconds.
20) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 59 minutes and 10
seconds.
21) "COLLECT STATISTICS ACTVTY_F COLUMN (REC_TYP ,SRC_NUM_ID ,POSN)". (HighConf)
22) "COLLECT STATISTICS ACTVTY_F COLUMN (SRC_NUM_ID ,OWNER)". (HighConf)
23) "COLLECT STATISTICS ACTVTY_F COLUMN (SRC_NUM_ID ,POSN)". (HighConf)
24) "COLLECT STATISTICS ACTVTY_F COLUMN (REC_TYP ,SRC_NUM_ID ,PTY)". (HighConf)
25) "COLLECT STATISTICS ACTVTY_F COLUMN (REC_TYP ,SRC_NUM_ID ,CNTCT)". (HighConf)
26) "COLLECT STATISTICS ACTVTY_F COLUMN (REC_TYP ,SRC_NUM_ID ,OWNER)". (HighConf)

Thanks!

3 REPLIES

Re: Qry very sloww!

Dieter, I need your inputs please!

Re: Qry very sloww!

Dieter, can you help me pleasE?
N/A

Re: Qry very sloww!

Here you are doing a select * on whole query output. Since only first table it being left outer joined with all other tables, after the first left outer join it will actually try to do a cartesian product of the result set with the third table. This is continued till the last query.

Try selecting only those fields which you require. Otherwise, you can also try something like:

SEL A7.* FROM

(SEL A6.* FROM

(SEL A5.* FROM

(SEL A4.* FROM

(SEL A3.* FROM

(SEL A2.* FROM

(SELECT A1.*

FROM ACTVTY_1 A1

LEFT OUTER JOIN POSN_X PX

ON A1.POSN = PX.ROWID

)A2

LEFT OUTER JOIN PRSN_X PRDX

ON A2.OWNER = PRDX.ROWID

)A3

LEFT OUTER JOIN POSN_D P

ON A1.POSN = P.ROWID

)A4

LEFT OUTER JOIN PRSN_D PRD

ON A1.CNTCT = PRD.ROWID

)A5

LEFT OUTER JOIN PTY_X OX

ON A1.PTY=OX.ROWID

)A6

LEFT OUTER JOIN PTY_D O

ON A1.PTY=O.ROWID

)A7

WHERE A7.SRC_NUM_ID = 1

AND A7.REC_TYP='ABC'