Link to home
Start Free TrialLog in
Avatar of RunLolaRun
RunLolaRun

asked on

Speed up INSERT INTO that selects from remote database

I use an INSERT INTO...SELECT statement to get data from a remote database and insert it into a local table.  Executing just the SELECT statement returns complete results within milliseconds but when used together with the INSERT statement it takes at least 30 minutes.  Why the time difference?  Is there any way I can speed it up?

Thank you.

John

INSERT INTO my_local  (id)
  SELECT id FROM my_remote@db_link
  WHERE firstname = 'Bob';
Avatar of jwittenm
jwittenm
Flag of United States of America image

Lots of possibilities here.  My favorites would be indexes, extent sizes, local vs dictionary managed files, and log files.
How many indexes are on the table and what kind are they?  If you disable the constraints what happens to the time?
Are your extent sizes large enough to preclude space allocation during the insert?
    As part of this, are you using locally managed tablespaces, if they are available on your version?
Are your log files large enough to handle the insert without lots of switching?  If you use direct insert / append.  What happens to the time then?

Suggest turning on event 10046 to trace the session and see what you are waiting on.
Avatar of jrb1
How many rows are you talking about? I agree with jwittenm's list...especially indexes and constraints.  Also, are there any triggers?
Good points in the first two responses.

Have you tried creating a "spool" file by doing a select from the remote database, then using SQL*Loader for the (now local) data?  That would allow you to determine if the bottleneck is the remote link or the load time.  If use a "direct-path" load, that may be faster that any "insert into ... select ...", even if the source and target tables are both local.
as suggested in the first response try
INSERT /*+ APPEND */ INTO my_local  (id)
  SELECT id FROM my_remote@db_link
  WHERE firstname = 'Bob';
Avatar of RunLolaRun
RunLolaRun

ASKER

There are no indexes, triggers, or constraints.  I used to have a primary key index but removed it a while ago.  (This table is used to dump new data into.  A later job then takes the data from this table, performs the necessary functions, then truncates the table).
Concerning extent sizes:
INITIAL_EXTENT=524288
NEXT_EXTENT=524288
MIN_EXTENTS=1
MAX_EXTENTS=2147483645

I tried the APPEND hint but it didn't speed up the query at all.

I have tried a query for which there is only one row (5 varchar2 columns, 1 date column).  The select returns immediately, the INSERT INTO takes 3 minutes.

John
10046 would be your best bet.  To narrow it down further, what happens if you put that row into another local table, then issue the 'insert into ... as select from <local table>'?
Try adding the driving_site hint, and see if it helps:
INSERT /*+ APPEND */ INTO my_local  (id)
  SELECT /*+ DRIVING_SITE(my_remote) */ id FROM my_remote@db_link
  WHERE firstname = 'Bob';

Hope that helps!
Thanks for your help so far everyone, I appreciate it!  

I tried the DRIVING_SITE hint and it didn't have any effect whatsoever.  I've really run out of ideas.  I'm going to try turning on event 10046 to see what that gives me.  

It's almost as if the "WHERE" part of each statement doesn't get executed until after all the data is brought through the DB link.  Is this is possible?  
Nope, that's not possible.  Did you try creating a local table and running the same insert?
Yes, I tried creating a local table and running it and it completed very quickly (milliseconds).
Did you try to create a local ASCII file via a select over the db_link, then use SQL*Loader to load that (as I suggested a few days ago)?  I realize that is multiple steps, but it may be the fastest way.  It would be nice if the relatively simple-looking "insert into ... select ... from ...@[db_link]" would work fast, but it apparently doesn't for you.
And did you just try:

SELECT id FROM my_remote@db_link
  WHERE firstname = 'Bob';

This completes fast as well?
hmmm.  Check your sqlnet.ora file and let us know what is in it.  Also try this just for the heck of it, the results may be interesting;
1: alter table <tablename> disable table lock;
    run the insert.  what happens?  reenable the table lock.
2: alter session set isolation_level serializable;
    run the insert.  what happens?  reconnect;
is this update statement run by itself?  it's not in a procedure, script or application with other commands?
this one bugs me :)
how many rows are being updated?
PAQ refund?
ASKER CERTIFIED SOLUTION
Avatar of DarthMod
DarthMod
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial