| Insert using DB Link (merged) [message #295858] |
Wed, 23 January 2008 14:58  |
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 #295861 is a reply to message #295860] |
Wed, 23 January 2008 15:05  |
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
|
|
|
|
|
|
|
|
| Insert using DB Link [message #296116 is a reply to message #295858] |
Thu, 24 January 2008 12:23 |
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 |
rleishman
Messages: 3728
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 #296282 is a reply to message #296186] |
Fri, 25 January 2008 09:19 |
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 |
 |
Michel Cadot
Messages: 68665
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
