Takes up cpu!

Database
Enthusiast

Takes up cpu!

Hi!

I have this qry whic takes up too much of CPU. Most of the stats are multi coln so I do not want to collect those.
Can we chg the qry to prform better?Any help is appreciated. I tried removing the double precision for one coln, and it just took more time to run which I could not find the reason for.
SELECT DISTINCT D1.c55 AS c1 , D1.c56 AS c2 , D1.c54 AS c3 , D1.c57 AS c4 ,
D1.c58 AS c5 , D1.c59 AS c6 , D1.c22 AS c7 , D1.c11 AS c8 , D1.c34 AS c9 ,
D1.c60 AS c10 , D1.c61 AS c11 , D1.c33 AS c12 , D1.c62 AS c13 ,
( COALESCE( CAST( CAST( D1.c40 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c41 AS DOUBLE PRECISION ) + CAST( D1.c40 AS DOUBLE PRECISION ) ,0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,
, D1.c63 AS c15 , D1.c64 AS c16 ,
D1.c65 AS c17 , D1.c66 AS c18 , D1.c67 AS c19 , D1.c68 AS c20 ,
D1.c69 AS c21 , D1.c70 AS c22 , D1.c71 AS c23 , D1.c72 AS c24 ,
D1.c53 AS c25 , D1.c42 AS c26 , D1.c43 AS c27 , D1.c44 AS c28 ,
D1.c45 AS c29 , D1.c46 AS c30 , D1.c47 AS c31 , D1.c48 AS c32 ,
1 AS c33 , D1.c39 AS c34 , D1.c37 AS c35 , D1.c35 AS c36 , D1.c38 AS c37 ,
D1.c36 AS c38 , D1.c39 + D1.c38 + D1.c37 + D1.c36 + D1.c35 AS c39 ,
D1.c46 AS c40 , D1.c47 AS c41 , D1.c41 AS c42 , D1.c49 + D1.c50 AS c43 ,
D1.c51 AS c44 , D1.c40 AS c45 , ( COALESCE( CAST( CAST( D1.c52 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c40 AS DOUBLE PRECISION ) ,
0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,
( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c46 , ( COALESCE( CAST( CAST( D1.c46 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c48 AS DOUBLE PRECISION ) ,
0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,
( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c47 , D1.c16 + D1.c15 + D1.c14 + D1.c13 + D1.c12 AS c48 ,
( COALESCE( CAST( CAST( D1.c17 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c18 AS DOUBLE PRECISION ) + CAST( D1.c17 AS DOUBLE PRECISION ) ,
0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,
( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c49 , ( COALESCE( CAST( CAST( D1.c19 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c17 AS DOUBLE PRECISION ) ,
0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,
( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c50 , ( COALESCE( CAST( CAST( D1.c20 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c21 AS DOUBLE PRECISION ) ,
0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,
( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c51 , D1.c5 + D1.c4 + D1.c3 + D1.c2 + D1.c1 AS c52 ,
( COALESCE( CAST( CAST( D1.c6 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c7 AS DOUBLE PRECISION ) + CAST( D1.c6 AS DOUBLE PRECISION ) ,
0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,
( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c53 , ( COALESCE( CAST( CAST( D1.c8 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c6 AS DOUBLE PRECISION ) ,
0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,
( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c54 , ( COALESCE( CAST( CAST( D1.c9 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c10 AS DOUBLE PRECISION ) ,
0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,
( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c55 , D1.c27 + D1.c26 + D1.c25 + D1.c24 + D1.c23 AS c56 ,
( COALESCE( CAST( CAST( D1.c28 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c29 AS DOUBLE PRECISION ) + CAST( D1.c28 AS DOUBLE PRECISION ) ,
0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,
( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c57 , ( COALESCE( CAST( CAST( D1.c30 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c28 AS DOUBLE PRECISION ) ,
0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,
( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c58 , ( COALESCE( CAST( CAST( D1.c31 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c32 AS DOUBLE PRECISION ) ,
0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,
( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c59
FROM (
SELECT SUM ( D1.c1 ) OVER ( partition BY D1.c22 , D1.c11 ) AS c1 ,
SUM ( D1.c2 ) OVER ( partition BY D1.c22 , D1.c11 ) AS c2 , SUM ( D1.c3 ) OVER ( partition BY D1.c22 ,
D1.c11 ) AS c3 , SUM ( D1.c4 ) OVER ( partition BY D1.c22 , D1.c11 ) AS c4 ,
SUM ( D1.c5 ) OVER ( partition BY D1.c22 , D1.c11 ) AS c5 , SUM ( D1.c6 ) OVER ( partition BY D1.c22 ,
D1.c11 ) AS c6 , SUM ( D1.c7 ) OVER ( partition BY D1.c22 , D1.c11 ) AS c7 ,
SUM ( D1.c8 ) OVER ( partition BY D1.c22 , D1.c11 ) AS c8 , SUM ( D1.c9 ) OVER ( partition BY D1.c22 ,
D1.c11 ) AS c9 , SUM ( D1.c10 ) OVER ( partition BY D1.c22 ,
D1.c11 ) AS c10 , D1.c11 AS c11 , SUM ( D1.c1 ) OVER ( partition BY D1.c22 ) AS c12 ,
SUM ( D1.c2 ) OVER ( partition BY D1.c22 ) AS c13 , SUM ( D1.c3 ) OVER ( partition BY D1.c22 ) AS c14 ,
SUM ( D1.c4 ) OVER ( partition BY D1.c22 ) AS c15 , SUM ( D1.c5 ) OVER ( partition BY D1.c22 ) AS c16 ,
SUM ( D1.c6 ) OVER ( partition BY D1.c22 ) AS c17 , SUM ( D1.c7 ) OVER ( partition BY D1.c22 ) AS c18 ,
SUM ( D1.c8 ) OVER ( partition BY D1.c22 ) AS c19 , SUM ( D1.c9 ) OVER ( partition BY D1.c22 ) AS c20 ,
SUM ( D1.c10 ) OVER ( partition BY D1.c22 ) AS c21 , D1.c22 AS c22 ,
SUM ( D1.c1 ) OVER ( partition BY D1.c33 , D1.c34 , D1.c22 ,
D1.c11 ) AS c23 , SUM ( D1.c2 ) OVER ( partition BY D1.c33 ,
D1.c34 , D1.c22 , D1.c11 ) AS c24 , SUM ( D1.c3 ) OVER ( partition BY D1.c33 ,
D1.c34 , D1.c22 , D1.c11 ) AS c25 , SUM ( D1.c4 ) OVER ( partition BY D1.c33 ,
D1.c34 , D1.c22 , D1.c11 ) AS c26 , SUM ( D1.c5 ) OVER ( partition BY D1.c33 ,
D1.c34 , D1.c22 , D1.c11 ) AS c27 , SUM ( D1.c6 ) OVER ( partition BY D1.c33 ,
D1.c34 , D1.c22 , D1.c11 ) AS c28 , SUM ( D1.c7 ) OVER ( partition BY D1.c33 ,
D1.c34 , D1.c22 , D1.c11 ) AS c29 , SUM ( D1.c8 ) OVER ( partition BY D1.c33 ,
D1.c34 , D1.c22 , D1.c11 ) AS c30 , SUM ( D1.c9 ) OVER ( partition BY D1.c33 ,
D1.c34 , D1.c22 , D1.c11 ) AS c31 , SUM ( D1.c10 ) OVER ( partition BY D1.c33 ,
D1.c34 , D1.c22 , D1.c11 ) AS c32 , D1.c33 AS c33 , D1.c34 AS c34 ,
SUM ( D1.c1 ) OVER ( partition BY D1.c33 , D1.c54 , D1.c53 ,
D1.c55 , D1.c66 , D1.c62 , D1.c63 , D1.c67 , D1.c68 , D1.c34 ,
D1.c22 , D1.c59 , D1.c58 , D1.c61 , D1.c60 , D1.c72 , D1.c70 ,
D1.c69 , D1.c71 , D1.c11 , D1.c57 ) AS c35 , SUM ( D1.c2 ) OVER ( partition BY D1.c33 ,
D1.c54 , D1.c53 , D1.c55 , D1.c66 , D1.c62 , D1.c63 , D1.c67 ,
D1.c68 , D1.c34 , D1.c22 , D1.c59 , D1.c58 , D1.c61 , D1.c60 ,
D1.c72 , D1.c70 , D1.c69 , D1.c71 , D1.c11 , D1.c57 ) AS c36 ,
SUM ( D1.c3 ) OVER ( partition BY D1.c33 , D1.c54 , D1.c53 ,
D1.c55 , D1.c66 , D1.c62 , D1.c63 , D1.c67 , D1.c68 , D1.c34 ,
D1.c22 , D1.c59 , D1.c58 , D1.c61 , D1.c60 , D1.c72 , D1.c70 ,
D1.c69 , D1.c71 , D1.c11 , D1.c57 ) AS c37 , SUM ( D1.c4 ) OVER ( partition BY D1.c33 ,
D1.c54 , D1.c53 , D1.c55 , D1.c66 , D1.c62 , D1.c63 , D1.c67 ,
D1.c68 , D1.c34 , D1.c22 , D1.c59 , D1.c58 , D1.c61 , D1.c60 ,
D1.c72 , D1.c70 , D1.c69 , D1.c71 , D1.c11 , D1.c57 ) AS c38 ,
SUM ( D1.c5 ) OVER ( partition BY D1.c33 , D1.c54 , D1.c53 ,
D1.c55 , D1.c66 , D1.c62 , D1.c63 , D1.c67 , D1.c68 , D1.c34 ,
D1.c22 , D1.c59 , D1.c58 , D1.c61 , D1.c60 , D1.c72 , D1.c70 ,
D1.c69 , D1.c71 , D1.c11 , D1.c57 ) AS c39 , D1.c40 AS c40 ,
D1.c41 AS c41 , D1.c42 AS c42 , D1.c43 AS c43 , D1.c44 AS c44 ,
D1.c45 AS c45 , D1.c46 AS c46 , D1.c47 AS c47 , D1.c48 AS c48 ,
D1.c49 AS c49 , D1.c50 AS c50 , D1.c51 AS c51 , D1.c52 AS c52 ,
D1.c53 AS c53 , D1.c54 AS c54 , D1.c55 AS c55 , D1.c56 AS c56 ,
D1.c57 AS c57 , D1.c58 AS c58 , D1.c59 AS c59 , D1.c60 AS c60 ,
D1.c61 AS c61 , D1.c62 AS c62 , D1.c63 AS c63 , D1.c64 AS c64 ,
D1.c65 AS c65 , D1.c66 AS c66 , D1.c67 AS c67 , D1.c68 AS c68 ,
D1.c69 AS c69 , D1.c70 AS c70 , D1.c71 AS c71 , D1.c72 AS c72
FROM (
SELECT COUNT ( CASE WHEN Q.ACTN_CD = '8' AND Q.STS_CD = 'P' THEN Q.ROW END ) AS c1
, COUNT ( CASE WHEN Q.ACTN_CD = '13' AND Q.STS_CD = 'P' THEN Q.ROW END ) AS c2
, COUNT ( CASE WHEN Q.ACTN_CD = '2' AND Q.STS_CD = 'R' THEN Q.ROW END ) AS c3
, COUNT ( CASE WHEN Q.ACTN_CD = '5' AND Q.STS_CD = 'P' THEN Q.ROW END ) AS c4
, COUNT ( CASE WHEN Q.ACTN_CD = '2' AND Q.STS_CD = 'P' THEN Q.ROW END ) AS c5
, SUM ( CASE WHEN Q.QC_P_FL IN ( 'A' , 'F' , 'O' , 'P' ,'S' ) THEN 1 ELSE 0 END ) AS c6
, SUM ( CASE WHEN R.QC_ELGBL = 'Y' AND Q.QC_P_FL IS NULL THEN 1 ELSE 0 END ) AS c7
, SUM ( CASE WHEN Q.QC_P_FL = 'P' THEN 1 ELSE 0 END ) AS c8
, SUM ( CASE WHEN Q.QC_P_FL IN ( 'O' , 'P' ) THEN 1 ELSE 0 END ) AS c9
, SUM ( CASE WHEN Q.QC_P_FL IN ( 'F' , 'O' , 'P' , 'S' ) THEN 1 ELSE 0 END ) AS c10 ,
CASE WHEN N.F_NM = ‘ABC’ THEN
CASE WHEN J.NM IN ( 'LS' , 'P AND LS' ) THEN 'T - L Prd'
WHEN J.NM IN ( 'CNTRLZD QC PO' ) THEN 'T - C P-Out'
WHEN J.NM IN ( 'CNTRLZD QC PI' ) THEN 'T - C P-In'
WHEN J.NM IN ( 'CNTRBTS PRCSNG ANLYST' ,'PEN PI' ) THEN 'T - Contributions'
WHEN J.NM IN ( 'ENRLMT' , 'ENRLMT SVC REP' ) THEN 'T - ENRLMTs'
WHEN J.NM IN ( 'ENRLMT SC AND MGRS' ) THEN 'T - ENRLMTs MGRS and SCs'
WHEN J.NM IN ( 'NULL' ) THEN 'T - Null'
WHEN J.NM IN ( 'PMT OPS' ) THEN 'T - PMT Ops'
WHEN J.NM IN ( 'SRVOR' , 'SRVOR NON SIP' , 'SRVOR PMTS T' ) THEN 'T - SRVOR'
END ELSE I.F_NM
END AS c11
, N.F_NM AS c22 , Q.OWNR_LGIN AS c33 ,L.F_NM AS c34
, SUM ( CASE WHEN Q.QC_P_FL IN ( 'A' , 'F' , 'O' , 'P' ,'S' ) THEN 1 ELSE 0 END ) AS c40
, SUM ( CASE WHEN R.QC_ELGBL = 'Y' AND Q.QC_P_FL IS NULL THEN 1 ELSE 0 END ) AS c41
, SUM ( CASE WHEN Q.QC_P_FL IN ( 'A' , 'O' , 'S' ) THEN 1 ELSE 0 END ) AS c42
, SUM ( CASE WHEN Q.QC_P_FL IN ( 'O' ) THEN 1 ELSE 0 END ) AS c43
, SUM ( CASE WHEN Q.QC_P_FL IN ( 'S' ) THEN 1 ELSE 0 END ) AS c44
, SUM ( CASE WHEN Q.QC_P_FL IN ( 'A' ) THEN 1 ELSE 0 END ) AS c45
, SUM ( CASE WHEN Q.QC_P_FL IN ( 'O' , 'P' ) THEN 1 ELSE 0 END ) AS c46
, SUM ( CASE WHEN Q.QC_P_FL IN ( 'F' , 'S' ) THEN 1 ELSE 0 END ) AS c47
, SUM ( CASE WHEN Q.QC_P_FL IN ( 'F' , 'O' , 'P' , 'S' ) THEN 1 ELSE 0 END ) AS c48
, SUM ( CASE WHEN Q.QC_P_FL = 'A' THEN 1 ELSE 0 END ) AS c49
, SUM ( CASE WHEN R.QC_ELGBL = 'N' AND Q.QC_P_FL IS NULL THEN 1 ELSE 0 END ) AS c50
, SUM ( CASE WHEN Q.QC_P_FL IS NULL THEN 1 ELSE 0 END ) AS c51
, SUM ( CASE WHEN Q.QC_P_FL = 'P' THEN 1 ELSE 0 END ) AS c52
, Q.QC_P_FL AS c53 , Q.TRDT AS c54
, O.DAYT AS c55 , O.PER_NM_MNTH AS c56 ,
CASE WHEN I.F_NM IN ( 'P, Jhn' , 'R, Elzbth' ) THEN 'Prmr/WO/NO'
WHEN N.F_NM = ‘ABC’ AND M.NM IN ( 'CNTRLZD QC PO' )
AND L.BDGT_CD IN ( 1283 , 80429 ) OR I.F_NM IN ( 'G, Dnl' , 'P, Mchl' ) THEN 'CQC'
ELSE ‘Prdn’
END AS c57
, H.F_NM AS c58 , K.F_NM AS c59 , L.BDGT_CD AS c60 , I.BDGT_CD AS c61
, P.LONGESC AS c62 , R.QC_ELGBL AS c63 , R.A_03 AS c64 , R.A_04 AS c65
, R.A_05 AS c66 , S.LONGESC AS c67 , W.L4_EMP_F_NM AS c68
, SUBSTR ( A.L4ANC_POSTN_ID ,4 ) AS c69 , A.L4_EMP_F_NM AS c70 , A.L5_EMP_F_NM AS c71
,D.NM AS c72
FROM PSTN A , PSTN B , EMP C ,
LOV D , EMP E , PSTN F ,
PSTN G , EMP H , EMP I ,
LOV J , EMP K , EMP L ,
LOV M , EMP N , DAY O ,
LOV P , PI_PERF_F Q , LOVX R ,
LOV S ,
LOVX T , EMP U ,
PSTN V , PSTN W
WHERE ( Q.QC_FAIL = D.ROW
AND Q.QC_OPRTR = C.ROW
AND E.INTGRTN_ID = G.INTGRTN_ID
AND E.ROW = Q.OPRTR
AND I.PRFL = J.ROW
AND L.PRFL = M.ROW
AND O.ROW = Q.PIT
AND P.ROW = Q.TSK_TYPE
AND P.ROW = R.ROW
AND S.NM = R.A_01
AND S.ROW = Q.DEPT
AND S.ROW = T.ROW
AND U.INTGRTN_ID = V.INTGRTN_ID
AND U.ROW = T.OWNER
AND V.ROW = W.ROW
AND P.TYPE = 'PI_TSK'
AND S.TYPE = 'PIPT'
AND H.F_NM = 'B, Brn'
AND K.DSRC_NUM_ID = 11
AND K.F_NM = 'B, Adrw'
AND A.ROW = B.ROW
AND F.L8ANC_POSTN_ID = H.INTGRTN_ID
AND F.L5ANC_POSTN_ID = I.INTGRTN_ID
AND F.L7ANC_POSTN_ID = K.INTGRTN_ID
AND F.L4ANC_POSTN_ID = L.INTGRTN_ID
AND F.L6ANC_POSTN_ID = N.INTGRTN_ID
AND F.ROW = G.ROW
AND B.INTGRTN_ID = C.INTGRTN_ID
AND ( Q.ACTN_CD = '2'
OR Q.STS_CD = 'P' )
AND ( Q.ACTN_CD = '13'
OR Q.ACTN_CD = '2'
OR P.LONGESC = 'UVW'
OR P.LONGESC = 'XYZ'
OR Q.ACTN_CD = '8' )
AND ( Q.STS_CD = 'P'
OR Q.STS_CD = 'R' )
AND ( Q.ACTN_CD = '13'
OR Q.ACTN_CD = '2'
OR Q.ACTN_CD = '5'
OR Q.ACTN_CD = '8' )
AND O.DAYT BETWEEN tImEsTaMp'2011-07-19 00:00:00'
AND tImEsTaMp'2011-07-25 00:00:00' )
GROUP BY P.LONGESC , S.LONGESC , O.DAYT ,
O.PER_NM_MNTH , H.F_NM , K.F_NM ,
N.F_NM , I.BDGT_CD , L.F_NM ,
L.BDGT_CD , Q.OWNR_LGIN , Q.TRDT ,
Q.QC_P_FL , R.A_03 , R.A_04 ,
R.A_05 , R.QC_ELGBL , W.L4_EMP_F_NM ,
D.NM , A.L4_EMP_F_NM , A.L5_EMP_F_NM ,
c11 , c57 , c69
) D1 ) D1

1) First, we lock LOVX for access, we lock
PI_PERF_F for access, we lock PSTN for
access, we lock EMP for access, we lock
DAY for access, we lock PSTN for access,
and we lock LOV for access.
2) Next, we execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from EMP by
way of an all-rows scan with a condition of (
(EMP.F_NM = 'B Adrw') AND
(EMP.DSRC_NUM_ID = 11)) into Spool 5
(all_amps) (compressed columns allowed), which is duplicated
on all AMPs. The size of Spool 5 is estimated with low
confidence to be 300 rows. The estimated time for this step
is 0.02 seconds.
2) We do an all-AMPs JOIN step from PSTN by way
of a RowHash match scan with no residual conditions, which is
joined to PSTN by way of a RowHash match scan
with no residual conditions. PSTN and
PSTN are joined using a merge join, with a join
condition of (PSTN.ROW =
PSTN.ROW). The result goes into Spool 6
(all_amps) (compressed columns allowed), which is built
locally on the AMPs. The size of Spool 6 is estimated with
low confidence to be 35,698 rows. The estimated time for
this step is 0.03 seconds.
3) 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 (L7ANC_POSTN_ID =
INTGRTN_ID). The result goes into Spool 7 (all_amps)
(compressed columns allowed), which is duplicated on all AMPs.
The size of Spool 7 is estimated with low confidence to be 1,200
rows. The estimated time for this step is 0.01 seconds.
4) We do an all-AMPs RETRIEVE step from EMP by way of
an all-rows scan with a condition of (NOT
(EMP.PRFL IS NULL)) locking for access
into Spool 8 (all_amps) (compressed columns allowed), which is
built locally on the AMPs. The size of Spool 8 is estimated with
high confidence to be 28,512 rows. The estimated time for this
step is 0.02 seconds.
5) We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of an
all-rows scan, which is joined to Spool 8 (Last Use) by way of an
all-rows scan. Spool 7 and Spool 8 are joined using a single
partition hash join, with a join condition of (L4ANC_POSTN_ID =
INTGRTN_ID). The result goes into Spool 9 (all_amps)
(compressed columns allowed), which is built locally on the AMPs.
The size of Spool 9 is estimated with low confidence to be 8 rows.
The estimated time for this step is 0.01 seconds.
6) We do an all-AMPs RETRIEVE step from EMP by way of
an all-rows scan with a condition of (
EMP.F_NM = 'Bohaty, Brian') locking for
access into Spool 10 (all_amps) (compressed columns allowed),
which is duplicated on all AMPs. The size of Spool 10 is
estimated with high confidence to be 300 rows. The estimated time
for this step is 0.02 seconds.
7) We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of an
all-rows scan, which is joined to Spool 10 (Last Use) by way of an
all-rows scan. Spool 9 and Spool 10 are joined using a single
partition hash join, with a join condition of (L8ANC_POSTN_ID =
INTGRTN_ID). The result goes into Spool 11 (all_amps)
(compressed columns allowed), which is duplicated on all AMPs.
The size of Spool 11 is estimated with low confidence to be 300
rows. The estimated time for this step is 0.01 seconds.
8) We do an all-AMPs RETRIEVE step from EMP by way of
an all-rows scan with a condition of (NOT
(EMP.PRFL IS NULL)) locking for access
into Spool 12 (all_amps) (compressed columns allowed), which is
built locally on the AMPs. The size of Spool 12 is estimated with
high confidence to be 28,512 rows. The estimated time for this
step is 0.02 seconds.
9) We do an all-AMPs JOIN step from Spool 11 (Last Use) by way of an
all-rows scan, which is joined to Spool 12 (Last Use) by way of an
all-rows scan. Spool 11 and Spool 12 are joined using a single
partition hash join, with a join condition of (L5ANC_POSTN_ID =
INTGRTN_ID). The result goes 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 size of
Spool 13 is estimated with low confidence to be 300 rows. The
estimated time for this step is 0.01 seconds.
10) We do an all-AMPs JOIN step from Spool 13 (Last Use) by way of an
all-rows scan, which is joined to LOV by way of a
traversal of index # 4 without accessing the base table extracting
row ids only. Spool 13 and LOV are joined using a
nested join, with a join condition of (PRFL =
LOV.ROW). The result goes into Spool 14 (all_amps),
which is built locally on the AMPs. Then we do a SORT to order
Spool 14 by field Id 1. The size of Spool 14 is estimated with
low confidence to be 3 rows. The estimated time for this step is
0.02 seconds.
11) We do an all-AMPs JOIN step from Spool 14 (Last Use) by way of an
all-rows scan, which is joined to LOV by way of an
all-rows scan with no residual conditions. Spool 14 and
LOV are joined using a row id join, with a join
condition of (PRFL = LOV.ROW). The
result goes into Spool 15 (all_amps) (compressed columns allowed),
which is duplicated on all AMPs. Then we do a SORT to order Spool
15 by row hash. The size of Spool 15 is estimated with low
confidence to be 450 rows. The estimated time for this step is
0.02 seconds.
12) We do an all-AMPs JOIN step from Spool 15 (Last Use) by way of an
all-rows scan, which is joined to LOV by way of a
traversal of index # 4 without accessing the base table extracting
row ids only locking LOV for access. Spool 15 and
LOV are joined using a nested join, with a join
condition of (PRFL = LOV.ROW). The
result goes into Spool 16 (all_amps), which is built locally on
the AMPs. Then we do a SORT to order Spool 16 by field Id 1. The
size of Spool 16 is estimated with low confidence to be 4 rows.
The estimated time for this step is 0.02 seconds.
13) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 16 (Last Use) by way of
an all-rows scan, which is joined to LOV by way of
an all-rows scan with no residual conditions locking
LOV for access. Spool 16 and LOV are
joined using a row id join, with a join condition of (
PRFL = LOV.ROW). The result goes
into Spool 17 (all_amps) (compressed columns allowed), which
is duplicated on all AMPs. The size of Spool 17 is estimated
with low confidence to be 600 rows. The estimated time for
this step is 0.02 seconds.
2) We do an all-AMPs RETRIEVE step from EMP by way
of an all-rows scan with no residual conditions locking for
access into Spool 18 (all_amps) (compressed columns allowed),
which is built locally on the AMPs. The size of Spool 18 is
estimated with high confidence to be 34,691 rows. The
estimated time for this step is 0.02 seconds.
14) We do an all-AMPs JOIN step from Spool 17 (Last Use) by way of an
all-rows scan, which is joined to Spool 18 (Last Use) by way of an
all-rows scan. Spool 17 and Spool 18 are joined using a single
partition hash join, with a join condition of (L6ANC_POSTN_ID =
INTGRTN_ID). The result goes into Spool 19 (all_amps)
(compressed columns allowed), which is duplicated on all AMPs.
The size of Spool 19 is estimated with low confidence to be 750
rows. The estimated time for this step is 0.01 seconds.
15) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from EMP by way
of an all-rows scan with no residual conditions locking for
access into Spool 20 (all_amps) (compressed columns allowed),
which is built locally on the AMPs. The size of Spool 20 is
estimated with high confidence to be 34,691 rows. The
estimated time for this step is 0.02 seconds.
2) We do an all-AMPs RETRIEVE step from LOVX by way
of an all-rows scan with a condition of ((NOT
(LOVX.A_01 IS NULL )) AND (NOT
(LOVX.ROW IS NULL ))) into Spool 21
(all_amps) (compressed columns allowed), which is built
locally on the AMPs. The size of Spool 21 is estimated with
high confidence to be 15,037 rows. The estimated time for
this step is 0.00 seconds.
3) We do an all-AMPs RETRIEVE step from LOV by way of
index # 8 TYPE = 'PIPT' with a residual condition of (
LOV.TYPE = 'PIPT') locking for access into
Spool 22 (all_amps) (compressed columns allowed), which is
duplicated on all AMPs. The size of Spool 22 is estimated
with high confidence to be 32,700 rows. The estimated time
for this step is 0.04 seconds.
4) We do an all-AMPs RETRIEVE step from LOV by way of
an all-rows scan with a condition of (LOV.TYPE =
'PI_TSK') locking for access into Spool 23 (all_amps)
(compressed columns allowed), which is redistributed by hash
code to all AMPs. The size of Spool 23 is estimated with high
confidence to be 2,437 rows. The estimated time for this step
is 0.02 seconds.
16) We do an all-AMPs JOIN step from Spool 21 (Last Use) by way of an
all-rows scan, which is joined to Spool 22 (Last Use) by way of an
all-rows scan. Spool 21 and Spool 22 are joined using a single
partition hash join, with a join condition of (NM = A_01).
The result goes into Spool 24 (all_amps) (compressed columns
allowed), which is redistributed by hash code to all AMPs. The
size of Spool 24 is estimated with low confidence to be 14,253
rows. The estimated time for this step is 0.05 seconds.
17) We do an all-AMPs RETRIEVE step from LOVX by way of
an all-rows scan with a condition of ((NOT
(LOVX.OWNER IS NULL )) AND (NOT
(LOVX.ROW IS NULL ))) locking for access into
Spool 25 (all_amps) (compressed columns allowed), which is
redistributed by hash code to all AMPs. The size of Spool 25 is
estimated with high confidence to be 15,037 rows. The estimated
time for this step is 0.05 seconds.
18) We do an all-AMPs JOIN step from Spool 23 (Last Use) by way of an
all-rows scan, which is joined to Spool 24 (Last Use) by way of an
all-rows scan. Spool 23 and Spool 24 are joined using a single
partition hash join, with a join condition of (ROW = ROW).
The result goes into Spool 26 (all_amps) (compressed columns
allowed), which is redistributed by hash code to all AMPs. The
size of Spool 26 is estimated with low confidence to be 14,253
rows. The estimated time for this step is 0.05 seconds.
19) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from EMP by way
of an all-rows scan with no residual conditions locking for
access into Spool 27 (all_amps) (compressed columns allowed),
which is redistributed by hash code to all AMPs. The size of
Spool 27 is estimated with high confidence to be 34,691 rows.
The estimated time for this step is 0.09 seconds.
2) We do an all-AMPs JOIN step from PSTN by way of
a RowHash match scan with no residual conditions, which is
joined to PSTN by way of a RowHash match scan
with no residual conditions locking PSTN for
access and PSTN for access. PSTN
and PSTN are joined using a merge join, with a
join condition of (PSTN.ROW =
PSTN.ROW). The result goes into Spool 28
(all_amps) (compressed columns allowed), which is
redistributed by hash code to all AMPs. The size of Spool 28
is estimated with low confidence to be 35,698 rows. The
estimated time for this step is 0.09 seconds.
3) We do an all-AMPs RETRIEVE step from EMP by way
of an all-rows scan with no residual conditions locking for
access into Spool 29 (all_amps) (compressed columns allowed),
which is redistributed by hash code to all AMPs. The size of
Spool 29 is estimated with high confidence to be 34,691 rows.
The estimated time for this step is 0.09 seconds.
4) We do an all-AMPs JOIN step from PSTN by way of
a RowHash match scan with no residual conditions, which is
joined to PSTN by way of a RowHash match scan
with no residual conditions locking PSTN for
access and PSTN for access. PSTN
and PSTN are joined using a merge join, with a
join condition of (PSTN.ROW =
PSTN.ROW). The result goes into Spool 30
(all_amps) (compressed columns allowed), which is
redistributed by hash code to all AMPs. The size of Spool 30
is estimated with low confidence to be 35,698 rows. The
estimated time for this step is 0.09 seconds.
20) We do an all-AMPs JOIN step from Spool 19 (Last Use) by way of an
all-rows scan, which is joined to Spool 20 (Last Use) by way of an
all-rows scan. Spool 19 and Spool 20 are joined using a single
partition hash join, with a join condition of (INTGRTN_ID =
INTGRTN_ID). The result goes into Spool 31 (all_amps)
(compressed columns allowed), which is duplicated on all AMPs.
The size of Spool 31 is estimated with low confidence to be 900
rows. The estimated time for this step is 0.01 seconds.
21) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from DAY by way of
an all-rows scan with a condition of ((DAY.DAYT
<= TIMESTAMP '2011-07-25 00:00:00') AND (DAY.DAYT
>= TIMESTAMP '2011-07-19 00:00:00')) into Spool 32 (all_amps)
(compressed columns allowed), which is duplicated on all AMPs.
The size of Spool 32 is estimated with high confidence to be
19,500 rows. The estimated time for this step is 0.03 seconds.
2) We do an all-AMPs JOIN step from Spool 31 (Last Use) by way of
an all-rows scan, which is joined to PI_PERF_F by
way of an all-rows scan with a condition of (
((PI_PERF_F.ACTN_CD = '13') OR
((PI_PERF_F.ACTN_CD = '2') OR
((PI_PERF_F.ACTN_CD = '5') OR
(PI_PERF_F.ACTN_CD = '8')))) AND
(((PI_PERF_F.STS_CD = 'P') OR
(PI_PERF_F.STS_CD = 'R')) AND
(((PI_PERF_F.ACTN_CD = '2') OR
(PI_PERF_F.STS_CD = 'P')) AND ((NOT
(PI_PERF_F.DEPT IS NULL )) AND ((NOT
(PI_PERF_F.TSK_TYPE IS NULL )) AND ((NOT
(PI_PERF_F.PIT IS NULL )) AND (NOT
(PI_PERF_F.QC_OPRTR IS NULL )))))))).
Spool 31 and PI_PERF_F are joined using a product
join, with a join condition of (ROW =
PI_PERF_F.OPRTR). The input table
PI_PERF_F will not be cached in memory, but it is
eligible for synchronized scanning. The result goes into
Spool 33 (all_amps) (compressed columns allowed), which is
built locally on the AMPs. The size of Spool 33 is estimated
with low confidence to be 13,627 rows. The estimated time for
this step is 1 minute and 1 second.
22) We do an all-AMPs JOIN step from Spool 25 (Last Use) by way of an
all-rows scan, which is joined to Spool 26 (Last Use) by way of an
all-rows scan. Spool 25 and Spool 26 are joined using a single
partition hash join, with a join condition of (ROW = ROW).
The result goes into Spool 34 (all_amps) (compressed columns
allowed), which is built locally on the AMPs. The size of Spool
34 is estimated with low confidence to be 156,554 rows. The
estimated time for this step is 0.02 seconds.
23) We do an all-AMPs JOIN step from Spool 32 (Last Use) by way of an
all-rows scan, which is joined to Spool 33 (Last Use) by way of an
all-rows scan. Spool 32 and Spool 33 are joined using a single
partition hash join, with a join condition of (ROW =
PIT). The result goes into Spool 35 (all_amps) (compressed
columns allowed), which is duplicated on all AMPs. The size of
Spool 35 is estimated with low confidence to be 121,350 rows. The
estimated time for this step is 0.05 seconds.
24) We do an all-AMPs JOIN step from Spool 27 (Last Use) by way of an
all-rows scan, which is joined to Spool 28 (Last Use) by way of an
all-rows scan. Spool 27 and Spool 28 are joined using a single
partition hash join, with a join condition of (INTGRTN_ID =
INTGRTN_ID). The result goes into Spool 36 (all_amps)
(compressed columns allowed), which is built locally on the AMPs.
The size of Spool 36 is estimated with low confidence to be 34,693
rows. The estimated time for this step is 0.01 seconds.
25) We do an all-AMPs JOIN step from Spool 34 (Last Use) by way of an
all-rows scan, which is joined to Spool 35 (Last Use) by way of an
all-rows scan. Spool 34 and Spool 35 are joined using a single
partition hash join, with a join condition of ((((LONGESC =
'UVW') OR (LONGESC = 'XYZ')) OR ((ACTN_CD =
'13') OR ((ACTN_CD = '2') OR (ACTN_CD = '8')))) AND
((ROW = TSK_TYPE) AND ((ROW = TSK_TYPE) AND
((ROW = DEPT) AND (ROW= DEPT ))))). The result
goes into Spool 37 (all_amps) (compressed columns allowed), which
is duplicated on all AMPs. The size of Spool 37 is estimated with
low confidence to be 91,050 rows. The estimated time for this
step is 0.05 seconds.
26) We do an all-AMPs RETRIEVE step from LOV by way of an
all-rows scan with no residual conditions locking for access into
Spool 38 (all_amps) (compressed columns allowed), which is built
locally on the AMPs. The size of Spool 38 is estimated with high
confidence to be 100,301 rows. The estimated time for this step
is 0.02 seconds.
27) We do an all-AMPs JOIN step from Spool 36 (Last Use) by way of an
all-rows scan, which is joined to Spool 37 (Last Use) by way of an
all-rows scan. Spool 36 and Spool 37 are joined using a single
partition hash join, with a join condition of (ROW =
OWNER). The result goes into Spool 39 (all_amps) (compressed
columns allowed), which is duplicated on all AMPs. The size of
Spool 39 is estimated with low confidence to be 91,200 rows. The
estimated time for this step is 0.05 seconds.
28) We do an all-AMPs JOIN step from Spool 29 (Last Use) by way of an
all-rows scan, which is joined to Spool 30 (Last Use) by way of an
all-rows scan. Spool 29 and Spool 30 are joined using a single
partition hash join, with a join condition of (INTGRTN_ID =
INTGRTN_ID). The result goes into Spool 40 (all_amps)
(compressed columns allowed), which is built locally on the AMPs.
The size of Spool 40 is estimated with low confidence to be 34,693
rows. The estimated time for this step is 0.01 seconds.
29) We do an all-AMPs JOIN step from Spool 38 (Last Use) by way of an
all-rows scan, which is joined to Spool 39 (Last Use) by way of an
all-rows scan. Spool 38 and Spool 39 are joined using a single
partition hash join, with a join condition of (QC_FAIL =
ROW). The result goes into Spool 41 (all_amps) (compressed
columns allowed), which is duplicated on all AMPs. The size of
Spool 41 is estimated with index join confidence to be 91,350 rows.
The estimated time for this step is 0.05 seconds.
30) We do an all-AMPs JOIN step from Spool 40 (Last Use) by way of an
all-rows scan, which is joined to Spool 41 (Last Use) by way of an
all-rows scan. Spool 40 and Spool 41 are joined using a single
partition hash join, with a join condition of (QC_OPRTR
= ROW). The result goes into Spool 4 (all_amps) (compressed
columns allowed), which is built locally on the AMPs. The size of
Spool 4 is estimated with index join confidence to be 610 rows.
The estimated time for this step is 0.02 seconds.
31) We do an all-AMPs SUM step to aggregate from Spool 4 (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 42. The size of Spool 42 is estimated with index join
confidence to be 610 rows. The estimated time for this step is
0.02 seconds.
32) We do an all-AMPs RETRIEVE step from Spool 42 (Last Use) by way of
an all-rows scan into Spool 1 (all_amps) (compressed columns
allowed), which is built locally on the AMPs. The size of Spool 1
is estimated with index join confidence to be 610 rows. The
estimated time for this step is 0.01 seconds.
33) We do an all-AMPs STAT FUNCTION step from Spool 1 (Last Use) by
way of an all-rows scan into Spool 47 (Last Use), which is assumed
to be redistributed by value to all AMPs. The result rows are put
into Spool 45 (all_amps) (compressed columns allowed), which is
built locally on the AMPs.
34) We do an all-AMPs STAT FUNCTION step from Spool 45 (Last Use) by
way of an all-rows scan into Spool 50 (Last Use), which is assumed
to be redistributed by value to all AMPs. The result rows are put
into Spool 49 (all_amps) (compressed columns allowed), which is
built locally on the AMPs.
35) We do an all-AMPs STAT FUNCTION step from Spool 49 (Last Use) by
way of an all-rows scan into Spool 53 (Last Use), which is assumed
to be redistributed by value to all AMPs. The result rows are put
into Spool 52 (all_amps) (compressed columns allowed), which is
built locally on the AMPs.
36) We do an all-AMPs STAT FUNCTION step from Spool 52 (Last Use) by
way of an all-rows scan into Spool 56 (Last Use), which is assumed
to be redistributed by value to all AMPs. The result rows are put
into Spool 55 (all_amps) (compressed columns allowed), which is
built locally on the AMPs.
37) We do an all-AMPs RETRIEVE step from Spool 55 (Last Use) by way of
an all-rows scan into Spool 2 (all_amps) (compressed columns
allowed), which is built locally on the AMPs. The size of Spool 2
is estimated with no confidence to be 610 rows. The estimated
time for this step is 0.01 seconds.
38) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of
an all-rows scan into Spool 59 (group_amps), which is
redistributed by hash code to all AMPs. Then we do a SORT to
order Spool 59 by the sort key in spool field1 eliminating
duplicate rows. The size of Spool 59 is estimated with no
confidence to be 610 rows. The estimated time for this step is
0.01 seconds.
39) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 59 are sent back to the user as the result
of statement 1.

Thanks!
3 REPLIES
Enthusiast

Re: Takes up cpu!

Dieter, I need ur suggestions on this one too
Enthusiast

Re: Takes up cpu!

Dieter, need your help please!
Enthusiast

Re: Takes up cpu!

One way is to put subqueries to pull the required fields data from the table in hte FROM clause instead of joining the entire table. You have 23 tables being joined and there might be some tabls where you are pulling only few fields out of say 100 columns present. Put a subquery to these kind of data pull. It will surely improve performance of the query.

It would be more better if you split the BIG query into few smaller ones. Instead of performing in one SQL query, try achieving it in splitting the query into 2 or 3 SQL's.