need help Tuning the qry!

Database
Enthusiast

need help Tuning the qry!

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!!
7 REPLIES
Enthusiast

Re: need help Tuning the qry!

Dieter, need your help please!!
Enthusiast

Re: need help Tuning the qry!

Have you looked at the DBQL data for this query? Which step seems to be taking the longest/using the most resources?

I'm not seeing anything that jumps out at me as very alarming. There are several high confidence steps. No product joins. No unnecessary redistributions.

How big are the underlying tables? Are they evenly distributed?
Enthusiast

Re: need help Tuning the qry!

YEs, the last 3 stepas, 18 ,19 and 10 take the longest. And these are ' all-AMPs SUM ' steps, how do I improve the perf of these steps? Only one of the tables is huge- src table. I ran this query and the skew factor of the tables varies between 1 to 31. So I am assuming data is not skewed.
select databasename,tablename,
cast((100 - (AVG(a.CurrentPerm)/MAX(a.CurrentPerm)*100)) as integer) AS SkewFactor
From dbc.tablesize a where databasename='database'
group by 1,2 order by 1;
Anything else that coul help?
Enthusiast

Re: need help Tuning the qry!

31 is pretty high, but if the table is small there's not much we can do about it. The main thing I can think of to improve an aggregation step is an Aggregate Join Index, but using a COUNT(DISTINCT ...) with an AJI typically negates the use of the AJI. One thing that does enable this is to create a AJI and then use a derived table that covers the AJI. Then do the COUNT(DISTINCT ...) outside of that derived table.

How many distinct instances of F. CNTCT are typical for the other grouping criteria? If that number is small, then an AJI will help. If there are many combinations, then an AJI may not prove very beneficial.
Enthusiast

Re: need help Tuning the qry!

there are 1290 sitinct instance considering the other grouping. Do u think and AJI will help?
And which columnd will the AJI be on?
Enthusiast

Re: need help Tuning the qry!

Thsi query also errored out with a spool space error. Could spool space be a problem?
Enthusiast

Re: need help Tuning the qry!

And how evenly are the rows spread across those 1290 instances?

An AJI might help, but it might not. I'd start by looking at your F derived table. First, select only the fields needed to satisfy the query (selected fields, joins, filters, etc.). Group by these fields. Make that your AJI definition. Change the query to use a select in the derived table that matches your AJI definition:

SELECT
Only, Columns, You, Need
FROM HIST_F
where
X_DEL_FLG IS NULL
GROUP BY Only, Columns, YOu, Need

Collect stats on the index and see if your explain plan improves. If not, drop the AJI. It won't help.

For the spool space error, try changing your query so you don't use derived tables for everything. Especially if you're just doing a SELECT * from the tables. This might reduce the spool used.