| SLOW Updates [message #189714] |
Fri, 25 August 2006 16:22  |
dpodborny
Messages: 3
Registered: August 2006
Location: Schaumburg, Illinois
|
Junior Member |
|
|
VERY slow updates over WAN using informatica or database links:
I can do a 100,000 updates on local database in 48 seconds.
Remote database same table, faster database, same index, same update statement, takes 1 hour.
I have opened tickets with Oracle, Informatica, and my network team, havent gotten a good answer back yet. My question is, I believe Informatica needs response back for every update, so it does throw alot of sql net messages, but locally, thats no big deal, about 40 seconds. But remotely, about 4,000 seconds. I AM Reading the tkprof correct right?? Is the difference really the network??? am I stuck with it? my pings and tnspings are under 80 msec. which is fast to the remote server, so I am told.
Also behaves EXACTLY same if running sql straight through with database links. If update is run on database itself, finishes in 10 seconds.
Output from tkprof.
local database:
Event waited on Times Max. Wait Total Waited
--------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 90094 0.02 40.71
**************************************************************
remote database:
Event waited on Times Max. Wait Total Waited
--------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 99066 0.02 3486.75
**************************************************************
Any input would be great! Indexes are always used. database almost sits idle while update is happening through informatica or database link.
|
|
|
|
| Re: SLOW Updates [message #189723 is a reply to message #189714] |
Fri, 25 August 2006 20:02  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
Make sure your network ip packet size can handle the row size.
Are other remote queries taking this long or is it just this one? Is the response of the remote database good?
Does the remote database have current optimizer statistics?
Does the remote database require extent growth for this quiry?
Are the initrans and maxtrans table parameters the same on both databases?
Does the remote database having row locking problems?
|
|
|
|
| Re: SLOW Updates [message #189822 is a reply to message #189723] |
Mon, 28 August 2006 00:24 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
With Informatica, if you can, split your INSERTs and UPDATES into two separate data streams with a Router and apply them separately to the database with array sizes of 100-1000.
If you cannot separate them, write them as INSERTs to a junk table (use a bulk loader) and then MERGE the junk table with the target table (using the SQL MERGE statement) after the job is complete.
Never, ever, ever, perform INSERTs and UPDATES in the same data stream with ETL tools.
Ross Leishman
|
|
|
|
| Re: SLOW Updates [message #189911 is a reply to message #189723] |
Mon, 28 August 2006 07:57 |
dpodborny
Messages: 3
Registered: August 2006
Location: Schaumburg, Illinois
|
Junior Member |
|
|
We have tested the idea of inserts versus updates in the same mapping. Inserts seem to happen just fine at about 1000-2000 rows a sec. However if we put an update strategy in it, just as you said not to do, we can make the inserts happen at 7 rows a sec as well.
Make sure your network ip packet size can handle the row size.
Are other remote queries taking this long or is it just this one? All deletes and updates take a very long time done remotely through links or informatica.
Is the response of the remote database good? Yes. The ping times are acceptable as well.
Does the remote database have current optimizer statistics? YES
Does the remote database require extent growth for this quiry? NO
Are the initrans and maxtrans table parameters the same on both databases? YES
Does the remote database having row locking problems? Not that I am aware of.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
