Archives of the TeradataForum

Message Posted: Thu, 08 Jan 2004 @ 18:38:26 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: View Performance

From:		Ganga Palakattil

Here is the explain from two versions of the query.

VIEW

replace view obc_master.recontacts as
locking table obc_master.lu_obc_job_info for access
locking table obc_master.fa_obc_mdn_mtd for access
locking table obc_master.lu_obc_rcontact_job_addnl_info for access
locking table obc_load.npa_nxx_outbound for access (
  select l.owner_carrier_id as ocid, recjob.location_id as orig,
    (recjob.reporting_year-2000)*12+recjob.reporting_month as mid,
recjob.scheduled_date, recmtd.run_date,
    recjob.job_id, recjob.contact_method,
    rec.orig_contact_method, rec.orig_metric_type,
    trim (recmtd.npanxxx) || recmtd.last_three_digit as mdn,
recmtd.config_id as cid, recmtd.activity_code as act
  from obc_master.lu_obc_rcontact_job_addnl_info rec
  join obc_master.lu_obc_job_info recjob on recjob.job_id = rec.job_id
  join obc_master.fa_obc_mdn_mtd recmtd on recmtd.job_id = rec.job_id
  join obc_load.npa_nxx_outbound l on l.npanxxx = recmtd.npanxxx
  where recjob.contact_type = 'r' and rec.orig_contact_type = 'p'

Query

select * from obc_master.recontacts where ocid = 9 and mid = 48 and act in (1,2)

It took forever and not finished

EXPLAIN for the above query

Explanation --------------------------------------------------

1) First, we lock obc_master.lu_obc_rcontact_job_addnl_info for access, we lock obc_master.fa_obc_mdn_mtd for access, we lock obc_master.lu_obc_job_info for access, and we lock obc_load.npa_nxx_outbound for access.

2) Next, we execute the following steps in parallel.

1) We do an all-AMPs JOIN step from obc_master.rec by way of a RowHash match scan. with a condition of ("obc_master.rec.ORIG_CONTACT_TYPE = 'p'"), which is joined to obc_master.recjob with a condition of ("(obc_master.recjob.CONTACT_TYPE = 'r') AND ((((obc_master.recjob.REPORTING_YEAR - 2000 )* 12 )+ obc_master.recjob.REPORTING_MONTH )= 48)"). obc_master.rec and obc_master.recjob are joined using a merge join, with a join condition of ("obc_master.recjob.JOB_ID = obc_master.rec.JOB_ID"). The result goes into Spool 2, which is duplicated on all AMPs. The size of Spool 2 is estimated with no confidence to be 2,656 rows. The estimated time for this step is 0.28 seconds.

2) We do an all-AMPs RETRIEVE step from obc_load.l by way of an all-rows scan with a condition of ("obc_load.l.OWNER_CARRIER_ID = 9") into Spool 3, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 3 by row hash. The size of Spool 3 is estimated with no confidence to be 15,360 rows. The estimated time for this step is 0.43 seconds.

3) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an all-rows scan, which is joined to obc_master.recmtd with a condition of ("(obc_master.recmtd.ACTIVITY_CODE = 1) OR (obc_master.recmtd.ACTIVITY_CODE = 2)"). Spool 2 and obc_master.recmtd are joined using a product join, with a join condition of ("obc_master.recmtd.JOB_ID = JOB_ID"). The input table obc_master.recmtd will not be cached in memory. The result goes into Spool 4, 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 no confidence to be 28,913 rows. The estimated time for this step is 1 minute and 39 seconds.

4) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of a RowHash match scan., which is joined to Spool 4 (Last Use). Spool 3 and Spool 4 are joined using a merge join, with a join condition of ("NPANXXX = NPANXXX"). The result goes into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 461,823 to 444,103,680 rows. The estimated time for this step is 3.98 seconds to 59 minutes and 11 seconds.

5) 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 1 minute and 44 seconds.

The view re-wrote in many ways and still the query never gave any performance improvement. Then created a Temp table with one column and two rows (values (1,2) and executed the following query. All data got back in 10 seconds.

select *
  from obc_master.recontacts A
          inner join obc_master.lu_temp B
      on A.act = B.act
where ocid = 9 and mid = 48

All data got back in 10 seconds

Explain for the above query

Explanation --------------------------------------------------

1) First, we lock a distinct obc_master."pseudo table" for read on a RowHash to prevent global deadlock for obc_master.a.

2) Next, we lock obc_master.a for read, we lock obc_master.lu_obc_rcontact_job_addnl_info for access, we lock obc_master.fa_obc_mdn_mtd for access, we lock obc_master.lu_obc_job_info for access, and we lock obc_load.npa_nxx_outbound for access.

3) We do an all-AMPs RETRIEVE step from obc_master.rec by way of an all-rows scan with a condition of ("obc_master.rec.ORIG_CONTACT_TYPE = 'p'") into Spool 2, which is duplicated on all AMPs. Then we do a SORT to order Spool 2 by row hash. The size of Spool 2 is estimated with no confidence to be 6,048 rows. The estimated time for this step is 0.04 seconds.

4) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an all-rows scan, which is joined to obc_master.recmtd by way of a traversal of index # 4 extracting row ids only. Spool 2 and obc_master.recmtd are joined using a nested join, with a join condition of ("obc_master.recmtd.JOB_ID = JOB_ID"). The input table obc_master.recmtd will not be cached in memory. The result goes into Spool 3, which is built locally on the AMPs. Then we do a SORT to order Spool 3 by field Id 1. The size of Spool 3 is estimated with no confidence to be 346,516 rows. The estimated time for this step is 1 minute and 54 seconds.

5) We execute the following steps in parallel.

1) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an all-rows scan, which is joined to obc_master.recmtd. Spool 3 and obc_master.recmtd are joined using a row id join, with a join condition of ("(1=1)"). The input table obc_master.recmtd will not be cached in memory. The result goes into Spool 4, 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 no confidence to be 346,516 rows. The estimated time for this step is 1 minute and 58 seconds.

2) We do an all-AMPs RETRIEVE step from obc_load.l by way of an all-rows scan with a condition of ("obc_load.l.OWNER_CARRIER_ID = 9") into Spool 5, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 5 by row hash. The size of Spool 5 is estimated with no confidence to be 15,360 rows. The estimated time for this step is 0.43 seconds.

6) We do an all-AMPs JOIN step from obc_master.recjob by way of a RowHash match scan. with a condition of ("((((obc_master.recjob.REPORTING_YEAR - 2000 )* 12 )+ obc_master.recjob.REPORTING_MONTH )= 48) AND (obc_master.recjob.CONTACT_TYPE = 'r')"), which is joined to Spool 4 (Last Use). obc_master.recjob and Spool 4 are joined using a merge join, with a join condition of ("obc_master.recjob.JOB_ID = JOB_ID"). The result goes into Spool 6, which is redistributed by hash code 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 346,516 rows. The estimated time for this step is 6.50 seconds.

7) We execute the following steps in parallel.

1) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of a RowHash match scan., which is joined to Spool 6 (Last Use). Spool 5 and Spool 6 are joined using a merge join, with a join condition of ("NPANXXX = NPANXXX"). The result goes into Spool 1, which is redistributed by hash code to all AMPs. The size of Spool 1 is estimated with no confidence to be 1,598,400 to 5,322,485,760 rows. The estimated time for this step is 16.62 seconds to 14 hours and 5 minutes.

2) We do an all-AMPs RETRIEVE step from obc_master.a by way of an all-rows scan with no residual conditions into Spool 8, which is duplicated on all AMPs. The size of Spool 8 is estimated with low confidence to be 1,024 rows. The estimated time for this step is 0.16 seconds.

8) We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of an all-rows scan, which is joined to Spool 1 (Last Use) with a condition of ("(OCID = 9) AND (MID = 48)"). Spool 8 and Spool 1 are joined using a product join, with a join condition of ("act = ACT"). The result goes into Spool 7, which is built locally on the AMPs. The size of Spool 7 is estimated with no confidence to be 1,598,400 to 5,322,485,760 rows. The estimated time for this step is 23.18 seconds to 17 hours and 55 minutes.

9) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.

-> The contents of Spool 7 are sent back to the user as the result of statement 1. The total estimated time is 4 minutes and 39 seconds.


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference