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@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@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@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?

Have you run explains on the 2 queries to check that they are indeed
using the same execution plans for the select part of the query?

Can you confirm whether the table being inserted into is local and the
query accesses the remote view, or is the table being inserted into
remote also?

Are there any triggers which fire for row inserts on the table being
inserted into?

Also, probably not much help for this particular instance, but it's
PARALLEL, not PARRALLEL.

Regards,
Rob

<adagetz (AT) gmail (DOT) com> wrote


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/com...ce1.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

Thank you for noticing that PARRALLEL should be PARRALEL. It does make
a difference in query performance. I will test the changes from
PARRALLEL to PARRALEL to see if there any performance increases.

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@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@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@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?

As Jonathan stated, driving_site hint is probably not being respected
in your version.

Try creating your select statement as a view on the *remote* database.
Then do a insert ... select * from view@ndw;


Anurag

Did you check whether any insert triggers fire on the table being
inserted into?
Regards,
Rob