Home » RDBMS Server » Performance Tuning » SLOW Updates
SLOW Updates [message #189714] Fri, 25 August 2006 16:22 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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.
Re: SLOW Updates [message #189913 is a reply to message #189822] Mon, 28 August 2006 08:05 Go to previous message
dpodborny
Messages: 3
Registered: August 2006
Location: Schaumburg, Illinois
Junior Member
This mapping we are testing with. It is ONLY an update mapping
Previous Topic: Analyze Command
Next Topic: Connection Latency
Goto Forum:
  


Current Time: Sat Dec 03 16:30:22 CST 2016

Total time taken to generate the page: 0.07796 seconds