Performance Issues on Insert Statement to Remote Table

From: <adagetz_at_gmail.com>
Date: 20 Mar 2006 05:01:52 -0800
Message-ID: <1142859712.359341.216010@u72g2000cwu.googlegroups.com>

I am noticing big differences in performance between a select sql statement on a remote database in Oracle versus an insert sql statement on a remote database.

The select statement on a remote database takes about 5 minutes to execute. It returns 216 records.

select /*+ PARRALLEL (profit,8) PARRALLEL (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

However, adding an insert statement as the beginning causes the query to take over 40 minutes to execute. It inserts 216 records.

insert
  into intadm.nxpp_tc_profit_rev_snpsht_fin   (subscriber_id, bill_cycle, bill_usage, processed_month ) select /*+ PARRALLEL (profit,8) PARRALLEL (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

Adding /*+ Append */ to the insert statement does nothing for query performance

insert /*+ APPEND */
  into intadm.nxpp_tc_profit_rev_snpsht_fin   (subscriber_id, bill_cycle, bill_usage, processed_month )

The view profitadm.profit_rev_snpsht_finvw_200601_at_ndw has about 16 million records in it, and the table intadm.nxpp_tc_residual_subscriber has only 216 records in it.

Any suggestions for speeding up the performance of the insert query? Received on Mon Mar 20 2006 - 07:01:52 CST