I've got a very sticky problem that I don't know how to solve, and I hoped that some of the Oracle gurus might have some advice.
I work in a large bank in a department that produces reports for different areas of the bank. By and large, we use Microsoft Office products to interface with our Oracle databases.
Recently, we had two new databases come online that use Oracle 11g - we were not using any 11g databases before this point. We have two other databases that we use that run on Oracle 10g.
Up until the two new databases were brought in, our reporting was done from systems that used 10g and 9I. We all ran the Oracle 9I driver to connect to them, and it worked very well without issue. With the addition of the 11g databases to our reporting pool, we have been forced to upgrade our ODBC connections to the 11g driver, and it has not gone well at all. I had one query that typically ran in 30 minutes take +13 hours+ to run yesterday. Speed is not the only issue, either; we have sporadic ODBC call fails, crashes, and other general failures to deal with.
Our Oracle DBAs have been trying to solve the issue, but have not yet found a solution, and each day that this goes on we fall further and further behind, as I have daily time-sensitive reports to send out that depend on this data.
One of our DBAs said she read somewhere that Oracle had not included MS Access support in the 11g driver, and that the errors were due to the imperfect connection that the driver created. I don't know of there's any truth to that, but it would provide an explanation for our troubles. We have to use Access for our reporting, as over 90% of our existing reports and processes use Access, and having to change over everything at once is just not feasible.
I'm at my wits' end. Is there any way to force the 9I and 11g ODBC connections to coexist, so that we don't have to use the 11g driver for our 10g databases? Or is there a better 11g driver available?
Yes, you can have multiple versions of Oracle client installed on a single machine (along with the corresponding ODBC driver), and yes you can use more than one version of ODBC at once. You'll need to have the 9i home first in the PATH though, as it doesn't support multiple homes as well as 11g does.
With respect to your ODBC crashes, make sure you're using 11203 client and ODBC. It's pretty stable at this point.
With respect to your poor performance, has your DBA said why? Failure to use index due to wrong bind type for example? There are workarounds in the ODBC driver that were added due to issues discovered as a result of MSAccess. Specifically, you might try the "bind number as float" and "bind timestamp as date" workarounds to see if they help performance. You'll need to relink the tables after changing the DSN options, or just create a new DSN with the option enabled right off the bat, and test with it.