Unanswered: Slow performance on dblink since Oracle9i upg
Will anyone be willing to direct me to a specific kb article for my problem? I've read the following articles: 251248,244661,259959, 280106 regarding troubleshooting a link to an Oracle server from SQL Server.
Our query performed very well (took 1 second) until the Oracle database was upgraded to 9i. To test we have links to both production and test on our test box. We are running SQL Server 2kSP3. Both Oracle databases are at 9i.
The query running against production (which is on a bigger, faster Unix box) runs in 5+ minutes whereas the same query run against their test, takes 1 second.
Using the Oracle client tool, SQLPLUS, the data comes back at lightening speed, but when the data goes through SQL Server, using the link to the Oracle production database, it has horrible performance.
Both database links are created the same. Both links display the table list of tables in Oracle in about the same amount of time when looking through EM. I've verified the presence of the mtxoci81_win2k.reg in the correct path.
The Oracle dba assures me that, other than a larger SGA and similar init parameters (which would be expected for a production environment), the other init parameters are the same. I've supported Oracle over the years (although I prefer SQL Server), and I respect this dba. He is thorough.
I've also changed the Server Options on the database link definition, checking them all and removing them 1 at a time and testing my query at each step. The last thing I'm looking at is the Provider Options, in case those settings are affected by the production Oracle 9i environment. I'm not coming up with much information regarding the Provider Options, hence my question to you.
The only other piece that I can see might be an issue would be that we have our sqlnet.ora file renamed. When present, our links don't work, when renamed, they do.
Any ideas or suggestions would be greatly appreciated!
That's some situation you have there. I'm not sure If I can be of much help But I will be face with a simular situation soon. I prefer it. I've supported Oracle in the passed and now I'm supporting SQL Server as part of a team. We have 2 Oracle DBA and One other SQL DBA. I started Monday. My experience has been that Oracle has it's own setting and SQL has its own. Please keep me posted on your progress. I also saw a product today called BMC that manages both oracle & SQL. Please get a demo and test it.