Home » RDBMS Server » Performance Tuning » Insert using DB Link (merged)
Insert using DB Link (merged) [message #295858] Wed, 23 January 2008 14:58 Go to next message
Leonard Martin
Messages: 45
Registered: May 2002
Location: Canada
Member
Hello All
I have the following statement that does an INSERT through a Database Link using a SELECT from the calling database.
This is in Oracle 10g. When I run the SELECT statement by itself, it uses the indexed column and takes just milliseconds to execute. However, when I run the whole INSERT statement, it takes about 25-30 seconds for each record and sometimes goes into a WAIT. We need the DBA to kill the session.

Can anyone justify why this happens. We upgraded from Oracle 8i to 10g and ever since it has been happening. Did anything change for the execution process in 10g ?


INSERT INTO DB1.STAGING@DB1.WORLD
(X_ROW, X_NUM, X_ID, X_TYPE, X_TYPE)
SELECT X_ROW, SITEID, REFERENCEID ||' '|| STLMTYPE, TYPE, SERVICE
FROM LOG_TABLE
WHERE X_ROW = in_X_ROW;
Re: INSERT USING DB LINK [message #295860 is a reply to message #295858] Wed, 23 January 2008 15:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Trace the execution to see where it spend time maybe it is in network.

Regards
Michel
Re: INSERT USING DB LINK [message #295861 is a reply to message #295860] Wed, 23 January 2008 15:05 Go to previous messageGo to next message
Leonard Martin
Messages: 45
Registered: May 2002
Location: Canada
Member
Yes, we did a trace and its the select statement. It does not seem to use the index when the insert is done.
However, when i run it separately it works fine.
I think the process first connects to the DBLink and comes back to the local table for execution. But not sure why it does not use the indexes.

I got around by placing the select in a cursor and using the values in the insert. However, i need to justify why the previous one was not working
Re: INSERT USING DB LINK [message #295869 is a reply to message #295861] Wed, 23 January 2008 15:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The driving is the one that does the modification.
Try to hint the index in the select part.

Regards
Michel
Re: INSERT USING DB LINK [message #295872 is a reply to message #295869] Wed, 23 January 2008 15:33 Go to previous messageGo to next message
Leonard Martin
Messages: 45
Registered: May 2002
Location: Canada
Member
I tried.
That didnt work too. So i used the cursor.
Insert using DB Link [message #296116 is a reply to message #295858] Thu, 24 January 2008 12:23 Go to previous messageGo to next message
Leonard Martin
Messages: 45
Registered: May 2002
Location: Canada
Member
Hello All
I have the following statement that does an INSERT through a Database Link using a SELECT from the calling database.
This is in Oracle 10g. When I run the SELECT statement by itself, it uses the indexed column and takes just milliseconds to execute. However, when I run the whole INSERT statement, it takes about 25-30 seconds for each record and sometimes goes into a WAIT. We need the DBA to kill the session.

Can anyone justify why this happens. We upgraded from Oracle 8i to 10g and ever since it has been happening. Did anything change for the execution process in 10g ?


INSERT INTO DB1.STAGING@DB1.WORLD
(X_ROW, X_NUM, X_ID, X_TYPE, X_TYPE)
SELECT X_ROW, SITEID, REFERENCEID ||' '|| STLMTYPE, TYPE, SERVICE
FROM LOG_TABLE
WHERE X_ROW = in_X_ROW;
Re: Insert using DB Link [message #296144 is a reply to message #296116] Thu, 24 January 2008 19:58 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Get an explain plan and post it here.

Also confirm the remote table has no parent foreign keys or triggers.

Ross Leishman
Re: Insert using DB Link [message #296186 is a reply to message #296116] Fri, 25 January 2008 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your purpose reposting the same question?

Regards
Michel
Re: Insert using DB Link [message #296282 is a reply to message #296186] Fri, 25 January 2008 09:19 Go to previous messageGo to next message
Leonard Martin
Messages: 45
Registered: May 2002
Location: Canada
Member
not sure why it appeared twice...my apologies.

Anyway here is the explain plan. As i had mentioned, the Select uses the right indexes when run by itself. But still not sure why it doesnt when used within the INSERT

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 4
TABLE ACCESS BY INDEX ROWID UTSMGR.UTS_EXCEPTION_LOG 1 555 4
INDEX RANGE SCAN UTSMGR.IX_EXCEPTION_X_SOURCE_ROW 1 3
Re: Insert using DB Link [message #296292 is a reply to message #296282] Fri, 25 January 2008 10:36 Go to previous message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
not sure why it appeared twice

It appears twice because you twice created the topic at one day interval.

Quote:
As i had mentioned, the Select uses the right indexes when run by itself. But still not sure why it doesnt when used within the INSERT

So post the explain plan for the INSERT.
Also please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Fri, 25 January 2008 10:36]

Report message to a moderator

Previous Topic: Index hint inside the view is not working
Next Topic: Rollback and trace/tkprof
Goto Forum:
  


Current Time: Mon Dec 05 10:46:56 CST 2016

Total time taken to generate the page: 0.14034 seconds