Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Select from dblink

669768Feb 24 2012 — edited Feb 28 2012
Database: 11g
I have written a dynamic sql for
insert into local_table(col1, col2..) select col1, col2....from remote_table@dblink
But this is running forever.

My DBA suggested me to create a temp table as select from remote_table and insert into local_table from temp_table, like this.

create temp_table as select col1, col2.. from remote_table@dblink;
insert into local_table(col1, col2..) select col1, col2....from temp table;
Both are dynamic sqls.

The DBA suggested method is running very fast than my original method.

Does anybody know why the direct insert from dblink is running very slow, compared to the create table as select from dblink ?

Thanks in Advance.
Karbi

Comments

Fabricio_Jorge
Hi,

Check the links below:

http://stackoverflow.com/questions/2462901/select-and-insert-across-dblink
http://itknowledgeexchange.techtarget.com/itanswers/inserts-through-a-database-link/

Try using the WITH clause in your select statement, as explained in the first link.
damorgan
Your DBA needs to take a class ... and/or go to a conference ... and/or read a book.

Without running autotrace, or explain plan (DBMS_XPLAN), or a trace no one has any idea what is slow and why. Start by gathering the facts.

Next I wonder whether the "remote" database is in the same data center, on the same subnet, and whether there are any firewalls in there? You don't say. Again gather the facts.

Finally I wonder, as probably everyone else here is does, what the actual database version is 
select * from v$version;
what the actual SQL statement is
how many rows are returned
whether the data contains a 100GB blob
and how fast you can ping from one machine to the other
None of which you address.

We can help you if you do the background research and provide sufficient information.
unknown-7404
What is the full query? Is there a WHERE clause that is filtering records?
CKPT
KarbiVeera wrote:
Database: 11g
I have written a dynamic sql for
insert into local_table(col1, col2..) select col1, col2....from remote_table@dblink
But this is running forever.

My DBA suggested me to create a temp table as select from remote_table and insert into local_table from temp_table, like this.

create temp_table as select col1, col2.. from remote_table@dblink;
insert into local_table(col1, col2..) select col1, col2....from temp table;
Both are dynamic sqls.

The DBA suggested method is running very fast than my original method.

Does anybody know why the direct insert from dblink is running very slow, compared to the create table as select from dblink ?
karbi,

When you started Insert, What are the wait events you have seen?

First check with v$session_event, what are the wait events and so on.

Also check suggestions from dan morgan.
669768
Database version is 11.2.0.1.0

This is the stmt.

INSERT INTO local_tbl (col1,col2)
SELECT col1, col2
FROM remote_tbl1@dblink b
WHERE exists (SELECT null FROM remote_tbl2@dblink a
WHERE col1 = 1
and b.col2 = a.col2);

Thanks in Advance
Karbi
rajeysh
try this 
SQL> select name from v$database;
 
NAME
---------
DBDEMO
 
SQL> create user testing identified by passwd;
 
User created.
 
SQL> grant connect,resource to testing;
 
Grant succeeded.
 
SQL> conn testing/passwd;
Connected.
 
 
SQL> create table test_tb (id number);
 
Table created.
 
SQL> insert into test_tb values(123);
 
1 row created.
 
SQL> / 
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select count(*) from test_tb;
 
  COUNT(*)
----------
         2
 
SQL> 
 
on dblink created machine:-
 
SQL> select name from v$database;
 
NAME
---------
DB2
 
SQL> create user nic identified by nic;
 
User created.
 
SQL> grant connect,resource, create database link to nic;
 
Grant succeeded.
 
SQL> conn nic/nic;
Connected.
SQL> create database link test_db_link connect to testing identified by passwd using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbdemo)))
  2  ';
 
Database link created.
 
SQL> select count(*) from test_tb@test_db_link;
 
  COUNT(*)
----------
         2
 
SQL>
unknown-7404
>
try this
>
I'm confused. That isn't inserting into a local table by querying a remote table like the OP query.
Did you forget to paste the query?
Jonathan Lewis
KarbiVeera wrote:

INSERT INTO local_tbl (col1,col2)
SELECT col1, col2
FROM remote_tbl1@dblink b
WHERE exists (SELECT null FROM remote_tbl2@dblink a
WHERE col1 = 1
and b.col2 = a.col2);
The most obvious guess is that the insert as select happens to run as a filter subquery or nested loop with two remote statements while the create as select operates through a remote join. As Dan Morgan suggested, one of the first things you need to do is check the execution plan.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: <b><em>Oracle Core</em></b>
damorgan
I asked a number of questions ... you answered only two of them.

If you want help please return to my previous email and provide the information.
CKPT
KarbiVeera wrote:
Database version is 11.2.0.1.0

This is the stmt.

INSERT INTO local_tbl (col1,col2)
SELECT col1, col2
FROM remote_tbl1@dblink b
WHERE exists (SELECT null FROM remote_tbl2@dblink a
WHERE col1 = 1
and b.col2 = a.col2);

Thanks in Advance
Karbi
Karbi,

Please provide what Danmorgan requested,

At the same time, In your insert query internally it is calling another DB LInk, so there is chain to two db links.
But your CTAS method also using Two DB LINKS? I dont think so.
Please confirm this.

So when you are working with two db links, one is dependent on other link.
Mohamed Houri
Recently a similar question came up on a french forum

http://www.developpez.net/forums/d1187032/bases-donnees/oracle/pl-sql/commit-apres-1000-enregistrements/

where the poster was suffering from the same symptoms as those you're experiencing. When I suggested him to follow a systematic approach, he succeed to enhance his insert/select to an acceptable response time.

I have summarized this french exchange of question/answer here in this blog article

http://hourim.wordpress.com/2012/02/25/tuning-an-insertselect-via-dblink/

Mohamed Houri
669768
Karbi,

Please provide what Danmorgan requested,

At the same time, In your insert query internally it is calling another DB LInk, so there is chain to two db links.
But your CTAS method also using Two DB LINKS? I dont think so.
Please confirm this.

So when you are working with two db links, one is dependent on other link.
CTAS method is also using two DBLINKS.

Regards,
Karbi
669768
damorgan wrote:
how many rows are returned
whether the data contains a 100GB blob
and how fast you can ping from one machine to the other
None of which you address.

We can help you if you do the background research and provide sufficient information.
The stmt inserts only 4 rows.
There is no BLOB
The ping comes out in milliseconds.

If I run the only the select without the insert, then it comes out very fast.

Thanks in Advance
Karbi
1 - 13
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 27 2012
Added on Feb 24 2012
#general-database-discussions
13 comments
12,594 views