Hi all!
I have to improvise the performance of this qry since it is running for 15 min. I tried filtering out at the table level instead of where condition.Stats have been collected.For all tables, upi is Rowid. Secondary index wizard does not suggest an index if I create a wrkload with this qry. I noticed that the last 3 steps which does an all-amps SUM step take a long time to run. What else can I do? PFB the qry and the xplain.Appreciate all help.
SELECT D1.c1 AS c1 , D1.c2 AS c2 , D1.c3 AS c3 , D1.c4 AS c4 , D1.c5 AS c5 , D1.c6 AS c6 , D1.c7 AS c7 , D1.c8 AS c8 , D1.c9 AS c9 , D1.c10 AS c10 , D1.c11 AS c11 , D1.c12 AS c12 , D1.c13 AS c13 , D1.c14 AS c14
FROM (
SELECT C. MKTPLN_NM AS c1 , C. PROG_NM AS c2 ,C. NM AS c3 , C. SRC_NUM AS c4 , C. STAT AS c5 ,C. STRT_DT AS c6 , C. END_DT AS c7 ,C. TYPE AS c8 , B. LNCD_TS AS c9 , G. OFFER_NM AS c10 ,G. MEDIA_TYPE AS c11 , G. OFFER_TYPE AS c12 , A. SGMT_NM AS c13 ,COUNT ( DISTINCT F. CNTCT ) AS c14 , A. INTGN_ID AS c15 ,G. INTGN_ID AS c16 , C. ID AS c17 ,C. PROG_ID AS c18 , C. MKTPLN_ID AS c19
FROM SGMT_D A ,
( sel * from LD_WAVE_D where LNCD_TS >= tImEsTaMp'2010-06-01 00:00:00' ) B ,
SRC_D C
LEFT OUTER JOIN
(sel * from SRC where X_SUB_TYPE IS NULL ) D
ON D. ROWID = C. ROWID ,
SRC_OFFR_H E ,
( sel * from HIST_F where X_DEL_FLG IS NULL ) F ,
OFFER_D G
WHERE ( A. ROWID = F. SGMT
AND B. ROWID = F. LD_WAVE
AND G. ROWID = E. OFFER
AND C. ROWID = E. SRC
AND C. ROWID = F. SRC
--AND B. LNCD_TS >= tImEsTaMp'2010-06-01 00:00:00'
--AND D. X_SUB_TYPE IS NULL
AND ( C. TYPE = 'Dir' OR C. TYPE = 'InDir' )
--AND F. X_DEL_FLG IS NULL
)
GROUP BY c15 , c13 , c16 ,c11 , c10 ,c12 , c9 , c6 , c7 , c17 , c3 , c4 , c5 , c8 , c18 ,c2 , C19 , C1
) D1
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 .SRC.
3) We lock a distinct ."pseudo table" for read on a RowHash to
prevent global deadlock for .C.
4) We lock a distinct ."pseudo table" for read on a RowHash to
prevent global deadlock for .A.
5) We lock a distinct ."pseudo table" for read on a RowHash to
prevent global deadlock for .G.
6) We lock a distinct ."pseudo table" for read on a RowHash to
prevent global deadlock for .LD_WAVE_D.
7) We lock a distinct ."pseudo table" for read on a RowHash to
prevent global deadlock for .HIST_F.
8) We lock .E for read, we lock .SRC for read, we
lock .C for read, we lock .A for read, we lock .G
for read, we lock .LD_WAVE_D for read, and we lock
.HIST_F for read.
9) We do an all-AMPs RETRIEVE step from .E by way of an all-rows
scan with no residual conditions 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
high confidence to be 5,809 rows. The estimated time for this
step is 0.02 seconds.
10) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from .C by way of a RowHash
match scan with a condition of ("(.C.TYPE =
'Dir') OR (.C.TYPE = 'InDir')"), which is
joined to Spool 4 (Last Use) by way of a RowHash match scan.
.C and Spool 4 are joined using a merge join, with a join
condition of (".C.ROWID = SRC"). The result
goes into Spool 5 (all_amps), which is built locally on the
AMPs. The size of Spool 5 is estimated with low confidence to
Be 1,545 rows. The estimated time for this step is 0.12
seconds.
2) We do an all-AMPs RETRIEVE step from .G by way of an
all-rows scan with no residual conditions into Spool 6
(all_amps), which is duplicated on all AMPs. The size of
Spool 6 is estimated with high confidence to be 237,300 rows.
The estimated time for this step is 0.07 seconds.
11) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an
all-rows scan, which is joined to Spool 6 (Last Use) by way of an
all-rows scan. Spool 5 and Spool 6 are joined using a single
partition hash join, with a join condition of ("ROWID =
OFFER"). The result goes into Spool 7 (all_amps), which is
built locally on the 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,545 rows. The estimated time for this step is 0.01 seconds.
12) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from .LD_WAVE_D by way
of an all-rows scan with a condition of (
".LD_WAVE_D.LNCD_TS >= TIMESTAMP '2010-06-01
00:00:00'") into Spool 8 (all_amps), which is duplicated on
all AMPs. The size of Spool 8 is estimated with high
confidence to be 5,400 rows. The estimated time for this step
is 0.01 seconds.
2) We do an all-AMPs RETRIEVE step from .HIST_F by
way of an all-rows scan with a condition of (
".HIST_F.X_DEL_FLG IS NULL") into Spool 9
(all_amps), which is built locally on the AMPs. The input
table will not be cached in memory, but it is eligible for
synchronized scanning. The size of Spool 9 is estimated with
high confidence to be 133,725,583 rows. The estimated time
For this step is 39.17 seconds.
13) 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 ("ROWID =
LD_WAVE"). The result goes into Spool 10 (all_amps), which is
built locally on the AMPs. The size of Spool 10 is estimated with
low confidence to be 641,798 rows. The estimated time for this
step is 1.81 seconds.
14) We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of a
RowHash match scan, which is joined to .SRC by way of
a RowHash match scan with a condition of (
".SRC.X_SUB_TYPE IS NULL"). Spool 7 and
.SRC are left outer joined using a merge join, with a
join condition of (".SRC.ROWID = ROWID"). The
Result goes into Spool 11 (all_amps), which is duplicated on all
AMPs. The size of Spool 11 is estimated with no confidence to be
231,750 rows. The estimated time for this step is 0.13 seconds.
15) We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of an
all-rows scan, which is joined to Spool 11 (Last Use) by way of an
all-rows scan. Spool 10 and Spool 11 are joined using a single
partition hash join, with a join condition of ("(SRC =
SRC) AND (ROWID = SRC)"). The result goes into
Spool 12 (all_amps), which is built locally on the AMPs. The size
of Spool 12 is estimated with no confidence to be 143,189 rows.
The estimated time for this step is 0.19 seconds.
16) We do an all-AMPs RETRIEVE step from .A by way of an all-rows
scan with no residual conditions into Spool 13 (all_amps), which
is duplicated on all AMPs. The size of Spool 13 is estimated with
high confidence to be 1,957,350 rows. The estimated time for this
step is 0.23 seconds.
17) We do an all-AMPs JOIN step from Spool 12 (Last Use) by way of an
all-rows scan, which is joined to Spool 13 (Last Use) by way of an
all-rows scan. Spool 12 and Spool 13 are joined using a single
partition hash join, with a join condition of ("ROWID =
SGMT"). The result goes into Spool 3 (all_amps), which is
built locally on the AMPs. The size of Spool 3 is estimated with
no confidence to be 143,189 rows. The estimated time for this
step is 0.08 seconds.
18) 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 15. The size of Spool 15 is estimated with no confidence
to be 107,392 rows. The estimated time for this step is 0.50
seconds.
19) We do an all-AMPs SUM step to aggregate from Spool 15 (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 17. The size of Spool 17 is estimated with no confidence
to be 80,544 rows. The estimated time for this step is 0.38
seconds.
20) We do an all-AMPs RETRIEVE step from Spool 17 (Last Use) by way of
an all-rows scan into Spool 1 (group_amps), which is built locally
on the AMPs. The size of Spool 1 is estimated with no confidence
to be 80,544 rows. The estimated time for this step is 0.03
seconds.
21) 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 42.66 seconds.
Thanks!!
I have to improvise the performance of this qry since it is running for 15 min. I tried filtering out at the table level instead of where condition.Stats have been collected.For all tables, upi is Rowid. Secondary index wizard does not suggest an index if I create a wrkload with this qry. I noticed that the last 3 steps which does an all-amps SUM step take a long time to run. What else can I do? PFB the qry and the xplain.Appreciate all help.
SELECT D1.c1 AS c1 , D1.c2 AS c2 , D1.c3 AS c3 , D1.c4 AS c4 , D1.c5 AS c5 , D1.c6 AS c6 , D1.c7 AS c7 , D1.c8 AS c8 , D1.c9 AS c9 , D1.c10 AS c10 , D1.c11 AS c11 , D1.c12 AS c12 , D1.c13 AS c13 , D1.c14 AS c14
FROM (
SELECT C. MKTPLN_NM AS c1 , C. PROG_NM AS c2 ,C. NM AS c3 , C. SRC_NUM AS c4 , C. STAT AS c5 ,C. STRT_DT AS c6 , C. END_DT AS c7 ,C. TYPE AS c8 , B. LNCD_TS AS c9 , G. OFFER_NM AS c10 ,G. MEDIA_TYPE AS c11 , G. OFFER_TYPE AS c12 , A. SGMT_NM AS c13 ,COUNT ( DISTINCT F. CNTCT ) AS c14 , A. INTGN_ID AS c15 ,G. INTGN_ID AS c16 , C. ID AS c17 ,C. PROG_ID AS c18 , C. MKTPLN_ID AS c19
FROM SGMT_D A ,
( sel * from LD_WAVE_D where LNCD_TS >= tImEsTaMp'2010-06-01 00:00:00' ) B ,
SRC_D C
LEFT OUTER JOIN
(sel * from SRC where X_SUB_TYPE IS NULL ) D
ON D. ROWID = C. ROWID ,
SRC_OFFR_H E ,
( sel * from HIST_F where X_DEL_FLG IS NULL ) F ,
OFFER_D G
WHERE ( A. ROWID = F. SGMT
AND B. ROWID = F. LD_WAVE
AND G. ROWID = E. OFFER
AND C. ROWID = E. SRC
AND C. ROWID = F. SRC
--AND B. LNCD_TS >= tImEsTaMp'2010-06-01 00:00:00'
--AND D. X_SUB_TYPE IS NULL
AND ( C. TYPE = 'Dir' OR C. TYPE = 'InDir' )
--AND F. X_DEL_FLG IS NULL
)
GROUP BY c15 , c13 , c16 ,c11 , c10 ,c12 , c9 , c6 , c7 , c17 , c3 , c4 , c5 , c8 , c18 ,c2 , C19 , C1
) D1
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 .SRC.
3) We lock a distinct ."pseudo table" for read on a RowHash to
prevent global deadlock for .C.
4) We lock a distinct ."pseudo table" for read on a RowHash to
prevent global deadlock for .A.
5) We lock a distinct ."pseudo table" for read on a RowHash to
prevent global deadlock for .G.
6) We lock a distinct ."pseudo table" for read on a RowHash to
prevent global deadlock for .LD_WAVE_D.
7) We lock a distinct ."pseudo table" for read on a RowHash to
prevent global deadlock for .HIST_F.
8) We lock .E for read, we lock .SRC for read, we
lock .C for read, we lock .A for read, we lock .G
for read, we lock .LD_WAVE_D for read, and we lock
.HIST_F for read.
9) We do an all-AMPs RETRIEVE step from .E by way of an all-rows
scan with no residual conditions 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
high confidence to be 5,809 rows. The estimated time for this
step is 0.02 seconds.
10) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from .C by way of a RowHash
match scan with a condition of ("(.C.TYPE =
'Dir') OR (.C.TYPE = 'InDir')"), which is
joined to Spool 4 (Last Use) by way of a RowHash match scan.
.C and Spool 4 are joined using a merge join, with a join
condition of (".C.ROWID = SRC"). The result
goes into Spool 5 (all_amps), which is built locally on the
AMPs. The size of Spool 5 is estimated with low confidence to
Be 1,545 rows. The estimated time for this step is 0.12
seconds.
2) We do an all-AMPs RETRIEVE step from .G by way of an
all-rows scan with no residual conditions into Spool 6
(all_amps), which is duplicated on all AMPs. The size of
Spool 6 is estimated with high confidence to be 237,300 rows.
The estimated time for this step is 0.07 seconds.
11) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an
all-rows scan, which is joined to Spool 6 (Last Use) by way of an
all-rows scan. Spool 5 and Spool 6 are joined using a single
partition hash join, with a join condition of ("ROWID =
OFFER"). The result goes into Spool 7 (all_amps), which is
built locally on the 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,545 rows. The estimated time for this step is 0.01 seconds.
12) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from .LD_WAVE_D by way
of an all-rows scan with a condition of (
".LD_WAVE_D.LNCD_TS >= TIMESTAMP '2010-06-01
00:00:00'") into Spool 8 (all_amps), which is duplicated on
all AMPs. The size of Spool 8 is estimated with high
confidence to be 5,400 rows. The estimated time for this step
is 0.01 seconds.
2) We do an all-AMPs RETRIEVE step from .HIST_F by
way of an all-rows scan with a condition of (
".HIST_F.X_DEL_FLG IS NULL") into Spool 9
(all_amps), which is built locally on the AMPs. The input
table will not be cached in memory, but it is eligible for
synchronized scanning. The size of Spool 9 is estimated with
high confidence to be 133,725,583 rows. The estimated time
For this step is 39.17 seconds.
13) 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 ("ROWID =
LD_WAVE"). The result goes into Spool 10 (all_amps), which is
built locally on the AMPs. The size of Spool 10 is estimated with
low confidence to be 641,798 rows. The estimated time for this
step is 1.81 seconds.
14) We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of a
RowHash match scan, which is joined to .SRC by way of
a RowHash match scan with a condition of (
".SRC.X_SUB_TYPE IS NULL"). Spool 7 and
.SRC are left outer joined using a merge join, with a
join condition of (".SRC.ROWID = ROWID"). The
Result goes into Spool 11 (all_amps), which is duplicated on all
AMPs. The size of Spool 11 is estimated with no confidence to be
231,750 rows. The estimated time for this step is 0.13 seconds.
15) We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of an
all-rows scan, which is joined to Spool 11 (Last Use) by way of an
all-rows scan. Spool 10 and Spool 11 are joined using a single
partition hash join, with a join condition of ("(SRC =
SRC) AND (ROWID = SRC)"). The result goes into
Spool 12 (all_amps), which is built locally on the AMPs. The size
of Spool 12 is estimated with no confidence to be 143,189 rows.
The estimated time for this step is 0.19 seconds.
16) We do an all-AMPs RETRIEVE step from .A by way of an all-rows
scan with no residual conditions into Spool 13 (all_amps), which
is duplicated on all AMPs. The size of Spool 13 is estimated with
high confidence to be 1,957,350 rows. The estimated time for this
step is 0.23 seconds.
17) We do an all-AMPs JOIN step from Spool 12 (Last Use) by way of an
all-rows scan, which is joined to Spool 13 (Last Use) by way of an
all-rows scan. Spool 12 and Spool 13 are joined using a single
partition hash join, with a join condition of ("ROWID =
SGMT"). The result goes into Spool 3 (all_amps), which is
built locally on the AMPs. The size of Spool 3 is estimated with
no confidence to be 143,189 rows. The estimated time for this
step is 0.08 seconds.
18) 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 15. The size of Spool 15 is estimated with no confidence
to be 107,392 rows. The estimated time for this step is 0.50
seconds.
19) We do an all-AMPs SUM step to aggregate from Spool 15 (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 17. The size of Spool 17 is estimated with no confidence
to be 80,544 rows. The estimated time for this step is 0.38
seconds.
20) We do an all-AMPs RETRIEVE step from Spool 17 (Last Use) by way of
an all-rows scan into Spool 1 (group_amps), which is built locally
on the AMPs. The size of Spool 1 is estimated with no confidence
to be 80,544 rows. The estimated time for this step is 0.03
seconds.
21) 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 42.66 seconds.
Thanks!!