Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Performance Issues on Insert Statement to Remote Table
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_subwhere 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_subwhere 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
![]() |
![]() |