High ImpactcPU

Tools

High ImpactcPU

 
2 REPLIES

Re: High ImpactcPU

Explain DELETE 
FROM    ETL_DB.D_TRAN
WHERE   ID in   (
SEL     ID
FROM    ETL_DB.D_C
WHERE   CURRENT_DATE - F_DT > 1825

);

  1) First, we lock a distinct TBL_DB."pseudo table" for write on
     a RowHash to prevent global deadlock for
     TBL_DB.CMGT_D_ACCTNBR.
  2) Next, we lock a distinct TBL_DB."pseudo table" for write on a
     RowHash to prevent global deadlock for
     TBL_DB.CMGT_D_TRAN_JI.
  3) We lock a distinct TBL_DB."pseudo table" for write on a
     RowHash to prevent global deadlock for
     TBL_DB.D_TRAN_PRSN_X_TRAN_JI.
  4) We lock a distinct TBL_DB."pseudo table" for write on a
     RowHash to prevent global deadlock for TBL_DB.D_TRAN_JI.
  5) We lock a distinct TBL_DB."pseudo table" for write on a
     RowHash to prevent global deadlock for TBL_DB.D_TRAN.
  6) We lock TBL_DB.CMGT_D_ACCTNBR for write, we lock
     TBL_DB.CMGT_D_TRAN_JI for write, we lock
     TBL_DB.D_TRAN_PRSN_X_TRAN_JI for write, we lock
     TBL_DB.D_TRAN_JI for write, we lock
     TBL_DB.D_TRAN in view ETL_DB.D_TRAN for
     write, we lock TBL_DB.DCTRPE1_ for access, and we lock
     TBL_DB.D_C for access.
  7) We execute the following steps in parallel.
       1) We do an all-AMPs RETRIEVE step from TBL_DB.D_TRAN
          in view ETL_DB.D_TRAN by way of an all-rows
          scan with no residual conditions into Spool 2 (all_amps),
          which is redistributed by the hash code of (
          TBL_DB.D_TRAN.Field_1025) to all AMPs.  The size
          of Spool 2 is estimated with high confidence to be
          118,766,826 rows (20,309,127,246 bytes).  The estimated time
          for this step is 11.94 seconds.
       2) We do an all-AMPs RETRIEVE step from TBL_DB.D_C by
          way of an all-rows scan with a condition of ("((DATE
          '2015-09-28')- (TBL_DB.D_C.F_DT ))> 1825")
          into Spool 4 (all_amps), which is built locally on the AMPs.
          Then we do a SORT to order Spool 4 by the sort key in spool
          field1 (TBL_DB.D_C.ID) eliminating duplicate
          rows.  The size of Spool 4 is estimated with no confidence to
          be 2,906,556 rows (241,244,148 bytes).  The estimated time
          for this step is 0.10 seconds.
  8) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by way of
     an all-rows scan into Spool 3 (all_amps), which is built locally
     on the AMPs.  Then we do a SORT to order Spool 3 by the hash code
     of (TBL_DB.D_C.ID).  The size of Spool 3 is estimated
     with no confidence to be 2,906,556 rows (241,244,148 bytes).
  9) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
     all-rows scan, which is joined to Spool 3 (Last Use) by way of an
     all-rows scan.  Spool 2 and Spool 3 are joined using a single
     partition inclusion hash join, with a join condition of ("ID =
     ID").  The result goes into Spool 1 (all_amps), which is
     redistributed by the rowkey of (
     TBL_DB.D_TRAN.Field_1025,
     TBL_DB.D_TRAN.Field_1026,
     TBL_DB.D_TRAN.Field_1028) to all AMPs.  Then we do a
     SORT to partition Spool 1 by rowkey.  The size of Spool 1 is
     estimated with no confidence to be 39,702,431 rows (6,789,115,701
     bytes).  The estimated time for this step is 1 hour and 53 minutes.
 10) We do an all-AMPs MERGE DELETE to TBL_DB.D_TRAN in view
     ETL_DB.D_TRAN from Spool 1.  The size is estimated
     with no confidence to be 39,702,431 rows.  The estimated time for
     this step is 10 hours and 6 minutes.
 11) We do an all-AMPs RETRIEVE step from Spool 1 by way of an all-rows
     scan into Spool 6 (all_amps), which is redistributed by the hash
     code of (TBL_DB.D_TRAN.ID) to all AMPs.  Then we do
     a SORT to order Spool 6 by row hash.  The size of Spool 6 is
     estimated with no confidence to be 39,702,431 rows (7,900,783,769
     bytes).  The estimated time for this step is 1 hour and 35 minutes.
 12) We execute the following steps in parallel.
      1) We do an all-AMPs MERGE DELETE to
         TBL_DB.CMGT_D_TRAN_JI from Spool 6 (Last Use).  The
         size is estimated with no confidence to be 39,702,431 rows.
         The estimated time for this step is 10 hours and 19 minutes.
      2) We do an all-AMPs RETRIEVE step from Spool 1 by way of an
         all-rows scan into Spool 7 (all_amps), which is redistributed
         by the rowkey of (TBL_DB.D_TRAN.ID_INT) to all
         AMPs.  Then we do a SORT to partition Spool 7 by rowkey.  The
         size of Spool 7 is estimated with no confidence to be
         39,702,431 rows (3,136,492,049 bytes).  The estimated time for
         this step is 50 minutes and 26 seconds.
 13) We execute the following steps in parallel.
      1) We do an all-AMPs MERGE DELETE to TBL_DB.D_TRAN_JI
         from Spool 7 (Last Use).  The size is estimated with no
         confidence to be 39,702,431 rows.  The estimated time for this
         step is 9 hours and 22 minutes.
      2) We do an all-AMPs RETRIEVE step from Spool 1 by way of an
         all-rows scan with a condition of ("(NOT (TRAN_ID IS NULL ))
         AND (NOT (ID IS NULL ))") into Spool 9 (all_amps) fanned
         out into 12 hash join partitions, which is built locally on
         the AMPs.  The size of Spool 9 is estimated with no confidence
         to be 39,702,431 rows (5,756,852,495 bytes).  The estimated
         time for this step is 1.43 seconds.
      3) We do an all-AMPs RETRIEVE step from TBL_DB.DCTRPE1_
         by way of an all-rows scan with no residual conditions into
         Spool 10 (all_amps) fanned out into 12 hash join partitions,
         which is redistributed by the hash code of (
         TBL_DB.DCTRPE1_.CTR1_7_1_,
         TBL_DB.DCTRPE1_.TRAN3_7_1_) to all AMPs.  The size of
         Spool 10 is estimated with high confidence to be 129,847,495
         rows (8,829,629,660 bytes).  The estimated time for this step
         is 5.68 seconds.
 14) 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 hash join
     of 12 partitions, with a join condition of ("(ID = ID) AND
     (TRAN_ID = TRAN_ID)").  The result goes into Spool 8 (all_amps),
     which is redistributed by the hash code of (
     TBL_DB.D_TRAN.ID) to all AMPs.  Then we do a SORT
     to order Spool 8 by row hash.  The size of Spool 8 is estimated
     with no confidence to be 46,923,939 rows (13,607,942,310 bytes).
     The estimated time for this step is 2 hours and 53 minutes.
 15) We execute the following steps in parallel.
      1) We do an all-AMPs MERGE DELETE to
         TBL_DB.D_TRAN_PRSN_X_TRAN_JI from Spool 8 (Last Use).
         The size is estimated with no confidence to be 46,923,939 rows.
         The estimated time for this step is 13 hours and 2 minutes.
      2) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way
         of an all-rows scan with a condition of ("NOT (ID IS NULL)")
         into Spool 12 (all_amps), which is redistributed by the hash
         code of (TBL_DB.D_TRAN.ID) to all AMPs.  The
         size of Spool 12 is estimated with no confidence to be
         39,702,431 rows (1,826,311,826 bytes).  The estimated time for
         this step is 1.24 seconds.
 16) We do an all-AMPs JOIN step from TBL_DB.dailyctr1_ by way of
     an all-rows scan with no residual conditions, which is joined to
     Spool 12 (Last Use) by way of an all-rows scan locking
     TBL_DB.dailyctr1_ for access.  TBL_DB.dailyctr1_ and
     Spool 12 are joined using a single partition hash join, with a
     join condition of ("ID = TBL_DB.dailyctr1_.ID").  The
     result goes into Spool 11 (all_amps), which is redistributed by
     the hash code of (TBL_DB.D_TRAN.ACCT_NBR) to all AMPs.
     Then we do a SORT to order Spool 11 by row hash.  The size of
     Spool 11 is estimated with no confidence to be 39,702,431 rows (
     1,985,121,550 bytes).  The estimated time for this step is 33
     minutes and 19 seconds.
 17) We do an all-AMPs MERGE DELETE to TBL_DB.CMGT_D_ACCTNBR
     from Spool 11 (Last Use).  The size is estimated with no
     confidence to be 39,702,431 rows.  The estimated time for this
     step is 9 hours and 9 minutes.
 18) We spoil the parser's dictionary cache for the table.
 19) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.

Hi,

I have this query which has a very high impact cPU. There  are multiple join indexes involved but we beed to retain them since they have been created to help with highly frequent queries runnign daily.

How do we get the impact cPU down ?

Re: High ImpactcPU

Hi.

Impact CPU = CPU time of the most used AMP * number of amps.

So, roughly speaking, it usually means skew.

Cheers.

Carlos.