Re: Performance Issues on Insert Statement to Remote Table

From: adagetz <adagetz_at_gmail.com>
Date: 20 Mar 2006 11:34:17 -0800
Message-ID: <1142883257.675263.320040@i40g2000cwc.googlegroups.com>

In this case the hints /*+ PARALLEL (profit,8) PARALLEL (int_sub,8) DRIVING_SITE (profit) */ on the select statement actually has slowed performance.

The following query takes 4 minutes to execute:

select

       profit.subscriber_id     subscriber_id,
       profit.bill_cycle        bill_cycle,
       profit.b_access_rev + profit.ub_access_rev       bill_usage,


       '200601'                  processed_month

from profitadm.profit_rev_snpsht_finvw_200601_at_ndw  profit,
     intadm.nxpp_tc_residual_subscriber            int_sub

where profit.subscriber_id = int_sub.subscriber_id

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE
  NESTED LOOPS
    REMOTE NDW.COM SERIAL
    INDEX UNIQUE SCAN
INTADM.NXPP_TC_RESIDUAL_SUBSCRIBER_PK While the following query takes over 40 minutes to execute

select /*+ PARALLEL (profit,8) PARALLEL (int_sub,8) DRIVING_SITE (profit) */

       profit.subscriber_id     subscriber_id,
       profit.bill_cycle        bill_cycle,
       profit.b_access_rev + profit.ub_access_rev       bill_usage,


       '200601'                  processed_month

from profitadm.profit_rev_snpsht_finvw_200601_at_ndw  profit,
     intadm.nxpp_tc_residual_subscriber            int_sub

where profit.subscriber_id = int_sub.subscriber_id

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT REMOTE Optimizer Mode=HINT: ALL_ROWS 327 511

  NESTED LOOPS		327  	95 K	511
    TABLE ACCESS FULL	PROFITADM.PROFIT_REV_SNPSHT_FINAL_200601	15
M	4G	510  	:Q1880873000	 P->S 	 QC (RANDOM)
    REMOTE		1  	13  	1  	!	SERIAL

So the optimizer does a better job of picking the optimal query plan than I did for the select statement.

However this still does not resolve that the insert statement takes over 40 minutes when the included select statement takes only 5 minutes.

  insert into intadm.nxpp_tc_profit_rev_snpsht_fin

     ( subscriber_id, bill_cycle, bill_usage, processed_month )   select

       profit.subscriber_id     subscriber_id,
       profit.bill_cycle        bill_cycle,
       profit.b_access_rev + profit.ub_access_rev       bill_usage,


       '200601'                  processed_month

from profitadm.profit_rev_snpsht_finvw_200601_at_ndw  profit,
     intadm.nxpp_tc_residual_subscriber            int_sub

where profit.subscriber_id = int_sub.subscriber_id

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

INSERT STATEMENT Optimizer Mode=CHOOSE
  NESTED LOOPS

    REMOTE		  	 	 	NDW.COM	SERIAL
    INDEX UNIQUE SCAN	INTADM.NXPP_TC_RESIDUAL_SUBSCRIBER_PK

The execution plan is the same for the insert statement and the select statement.

Any other suggestions to speed up the performance of the insert statement? Received on Mon Mar 20 2006 - 13:34:17 CST