INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

improvise the qry perf

improvise the qry perf

(OP)
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!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close