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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » Working with Oracle » SQL transactional replication to Oracle 11g...

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

Rate Topic

Display Mode

Topic Options

Author

Message

JP10

Posted Friday, January 04, 2013 5:05 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:10 PM
Points: 22, Visits: 139

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..

Post #1403162

Jo Pattyn

Posted Friday, January 04, 2013 6:25 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 7:46 PM
Points: 1,329, Visits: 4,303

Have you also checked the oracle-side? (tracing,locks etc)

Post #1403177

JP10

Posted Friday, January 04, 2013 8:51 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:10 PM
Points: 22, Visits: 139

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!

Post #1403188

Jo Pattyn

Posted Monday, January 07, 2013 7:48 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 7:46 PM
Points: 1,329, Visits: 4,303

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...

Post #1403617

JP10

Posted Monday, January 07, 2013 10:23 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:10 PM
Points: 22, Visits: 139

Thanks for the link much apprecaited. I will find if there were any changes thanks.

Post #1403739

Jo Pattyn

Posted Tuesday, January 08, 2013 3:10 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 7:46 PM
Points: 1,329, Visits: 4,303

Other oracle tools:
tracing, perhaps on module sqlserver.exe, or enable from your openquerysession
General oracle performance tuning

Post #1404086

« Prev Topic | Next Topic »

Permissions