SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL transactional replication to Oracle 11g server taking slower than normal


SQL transactional replication to Oracle 11g server taking slower than normal

Author
Message
JP10
JP10
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 668
Hi, I have a push transactional replication set up that pushes to a Oracle 11g db. What used to take around 9 to 10 minutes now takes around a hour. There are about 3.5 million rows in this table (well call it table1 for this purpose) on the sql server. The oracle table (well call it table2) also has about 3.5 mill rows. There are only 2 indexs on each table.

I ran a trace to try and find any long duration queries hanging, used sp_who2 to look for locks, cpu, i/o, memory and they all look fine.

We also looked at the Virtual Log Files and they were around 3500 so we shrinked them to around 400 and right now were are going to check if that helped as I type this.

Anything else I am missing your help is much appreciated?

p.s. sorry i originally posted this same topic in another section..
Jo Pattyn
Jo Pattyn
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2800 Visits: 9903
Have you also checked the oracle-side? (tracing,locks etc)
JP10
JP10
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 668
I was affraid you would say that. I only looked at he cpu, i/o and memory of the Oracle server. I unfortunately am not a Oracle dba. If you have links or quick code snippets that I can run on Oracle to see that would be much appreciated!

Are there any Distributor checks I can do to see if it could be the Distributor that is causing the slowness?

Appreciate the help!
Jo Pattyn
Jo Pattyn
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2800 Visits: 9903
Haven't time to setup a mssql to oracle replication, however some possible helpfull links.

SQL Server Replication
Locks in oracle
Perhaps Statspack if installed on the oracle db (small interval)

Dreaded question: anything changed on hardware level? network, storage, db-settings...
JP10
JP10
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 668
Thanks for the link much apprecaited. I will find if there were any changes thanks.
Jo Pattyn
Jo Pattyn
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2800 Visits: 9903
Other oracle tools:
tracing, perhaps on module sqlserver.exe, or enable from your openquerysession
General oracle performance tuning
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search