"Performance Poll" Oracle -> SQL Server Analysis Services [message #689908] |
Thu, 11 July 2024 16:02 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Hey, long time no see...
My low level Oracle tinkering has had somewhat of a lull the last couple of years. But now I have run into something somewhat funny where I wanted to get some feedback from people in (hopefully) the know....
We had one application hat has been chugging along on SQL Server 2012 (Internal Version 11) Analysis Services for years now, that pulls Somewhat-Gigabites of data from Oracle Databases on a nightly basis. Job Runtime has been in the "lower than dozens of minutes" range with an Oracle 11 ODAC drivers.
The Oracle DB versions have gone from 10 to 21 in that time period, without any measurable change in performance.
Now the "Windows Guys" have set up a new SQL Server 2022 Server (Internal Version 16), and have tried the Oracle 21 ODAC drivers. Runtime has gone through the roof to "multiple hours, in some cases even more than 24".
( Driver Source: https://www.oracle.com/database/technologies/net-downloads.html )
After much unsuccessful tinkering with parameters like fetchsize and encryption Parameters we decided to try different ODAC drivers from the historic releases list ...
( Driver Source: https://www.oracle.com/database/technologies/dotnet-odacdev-downloads.html )
and have found that the 12.2 Version has run-times in the hours, while the 12.1 Version still has run-times in the dozens-of-minutes range, comparable to the old setup.
So after hours of Googling and groveling in the sand before ChatGTP, I decided to throw it in here.
The immediate problem is solved by using the 12.1 drivers, and will be solved for the next couple of years. But if somebody out there has encountered this, and has an easy fix available, I would be intrigued to know. ![Wink](/forum/images/smiley_icons/icon_wink.gif)
PS: The Oracle DB isn't doing anything during that time. The only wait state for the open session is waiting on "SQL*Net message from client".
[Updated on: Thu, 11 July 2024 16:16] Report message to a moderator
|
|
|