Re: Performance Issues on Insert Statement to Remote Table

<adagetz_at_gmail.com> wrote in message
news:1142859712.359341.216010_at_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?
>

Although it's not documented, I believe the driving_site() hint is not valid for either 'create as select' or 'insert .. select'.

You will have to find a way to make the query run faster at the local site, before converting it to an 'insert .. select'

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html