Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Speed up INSERT INTO that selects from remote database

Posted on 2005-05-09
19
Medium Priority
?
2,258 Views
Last Modified: 2012-06-21
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';
0
Comment
Question by:RunLolaRun
  • 5
  • 3
  • 3
  • +4
17 Comments
 
LVL 6

Expert Comment

by:jwittenm
ID: 13960296
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.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13960533
How many rows are you talking about? I agree with jwittenm's list...especially indexes and constraints.  Also, are there any triggers?
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 13960985
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:anand_2000v
ID: 13961164
as suggested in the first response try
INSERT /*+ APPEND */ INTO my_local  (id)
  SELECT id FROM my_remote@db_link
  WHERE firstname = 'Bob';
0
 

Author Comment

by:RunLolaRun
ID: 13961904
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
0
 
LVL 6

Expert Comment

by:jwittenm
ID: 13962925
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>'?
0
 
LVL 11

Expert Comment

by:pennnn
ID: 13968816
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!
0
 

Author Comment

by:RunLolaRun
ID: 13980503
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?  
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13980571
Nope, that's not possible.  Did you try creating a local table and running the same insert?
0
 

Author Comment

by:RunLolaRun
ID: 13987841
Yes, I tried creating a local table and running it and it completed very quickly (milliseconds).
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 13989333
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.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13990031
And did you just try:

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

This completes fast as well?
0
 
LVL 6

Expert Comment

by:jwittenm
ID: 13990910
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;
0
 
LVL 6

Expert Comment

by:jwittenm
ID: 13991035
is this update statement run by itself?  it's not in a procedure, script or application with other commands?
0
 
LVL 6

Expert Comment

by:jwittenm
ID: 13991285
this one bugs me :)
how many rows are being updated?
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 14528499
PAQ refund?
0
 
LVL 1

Accepted Solution

by:
DarthMod earned 0 total points
ID: 14613822
PAQed with no points refunded (of 125)

DarthMod
Community Support Moderator
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses
Course of the Month10 days, 8 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question