March 13, 2012 at 9:58 am
Hi all!
I've basically given up trying to research this issue as it appears useless, however, I figured I'd at least ask here before I quit alltogether. Here is the issue.
I have a SQL 2008 R2 database that needs to use a view located in Oracle as a lookup table. Because the underlying table that the view utilzes in oracle contains so many records (140 million +), we decided to leave it staged in Oracle so we do not have redundant copies of it. Also, per the vendor we are working with, using the openquery syntax is not an option as they would need to re-write a significant amount of their core logic to implement this. What they are using is the 4 part naming scheme while accessing the view, for example 'SELECT * FROM [linkedserver]..[schema].
'.
This works fine and pulls back the records, but the issue that arises is when we apply the WHERE clause to the end of the statement. It doesn't appear that the condition is actually being passed to Oracle when view the session that is hitting the view. This obviously creates a performance problem where if we were to run the query via an oracle tool, it returns in less than a second.
My belief is that it has something to do with the collations between the two not being compatible, but I can not determine a way to configure the provider/linked server in a way that it allows them to match up correctly. Any help or explanation would be appreciated as I know you've probably heard this before, but, it worked last week fine but now this week we are experiencing the performance issue. Nothing has changed, but I also didn't verify if the WHERE clause was being passed to Oracle last week.
Here are my current configurations:
Windows Server 2008 R2 x64
SQL Server 2008 R2 SP2 x64
Oracle11GDAC (11.2.0) x64
MS SQL Server Collation SQL_Latin1_General_CP1_CI_AS
MS SQL DB Collation SQL_Latin1_General_CP1_CI_AS
Linked Server Provider - OraOLEDB.Oracle (Options set @ provider; Dynamic Parameter = True, Nested Queries = True, Allow inprocess = True, supports like operator = true)
Linked Server Setup - Options set as; Collation Compatible = False, Data Access = True, RPC = True, RPC Out = True, Use Remote Collation = True, Collation Name = SQL_Latin1_General_CP437_BIN, Enable Promotion of Distributed Transactions = True, everything else is false.
I have also tried to set linked server option Collation Compatible = True without specifying a specific Collation Name. Still to no avail....
Our Oracle Instance is sitting on Top of AIX and is at version 11G. Here are the options that are relevant via Oracle:
NLS_Language = AMERICAN
NLS_NUMERIC_CHARACTERS = .,
NLS_CHARACTERSET = US7ASCII
NLS_SORT = BINARY
NLS_NCHAR_CHARACTERSET = AL16UTF16
NLS_COMP = BINARY
NLS_NCHAR_CONV_EXCP = FALSE
Thanks and any input is appreciated!
March 14, 2012 at 3:45 am
Any chance to trace on the oracle side the query mssql generates, to see if mssql actually passes the where clause or tries to pull the whole table.
March 14, 2012 at 6:51 am
Yes,
We can see what query is passed to the server when looking at the session that is created by MSSQL. If we run the query with the 4 part syntax, it clearly shows a basic Select * against the table/view without the where clause. If I wrap the MSSQL query up in the OPENQUERY format, you can see the entire query passed to Oracle and it executes at the desired speed.
March 15, 2012 at 6:42 am
dgossdba (3/14/2012)
Yes,We can see what query is passed to the server when looking at the session that is created by MSSQL. If we run the query with the 4 part syntax, it clearly shows a basic Select * against the table/view without the where clause. If I wrap the MSSQL query up in the OPENQUERY format, you can see the entire query passed to Oracle and it executes at the desired speed.
Correct.
I understand this is an issue affecting Microsoft OLEDB provider when using four parts syntax and DBPROP_MAXROWS is set to zero. Basically, the whole table is moved to the SQL Server side then the predicate is applied to filter out the desired rows.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 15, 2012 at 7:26 am
Correct, I have read that it is a problem with the Microsoft OLEDB version of the driver. However, I'm using the Oracle Client version of the provider on the system. From everything I've tried, still a no go on being able to pass the where clause. I still assume it's a problem with the differences in collations between the two platforms. That being said, the vendor agreed to implement a temporary work around utilizing the OPENQUERY syntax until they can change the core engine logic. I hate giving up on things like this....just don't have the time to invest. Thanks all.
March 15, 2012 at 7:56 am
dgossdba (3/15/2012)
Correct, I have read that it is a problem with the Microsoft OLEDB version of the driver. However, I'm using the Oracle Client version of the provider on the system. From everything I've tried, still a no go on being able to pass the where clause. I still assume it's a problem with the differences in collations between the two platforms. That being said, the vendor agreed to implement a temporary work around utilizing the OPENQUERY syntax until they can change the core engine logic. I hate giving up on things like this....just don't have the time to invest. Thanks all.
I think it is not the collation but an unwanted feature of four-part name syntax.
Four-part name syntax assumes - and requires that - the query statement is written in T-SQL then SQL Server has to parse it, rewrite it and send it to the linked server in a version of SQL the linked server would understand.
It beats me why a predicate can't be parsed - I'm sure there is a very good reason for it - and sent to the Oracle side but I think parser sends a very simple query that retrieves the whole content of the remote table then applies locally any WHERE, GROUP BY, HAVING and SORT BY conditions the original query may include. That's why traced query on Oracle side doesn't show a predicate.
In my limited experience with linked servers and after seeing how different performance is I have settled to use openquery syntax.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 29, 2012 at 12:46 am
Dear All,
I need your help.
I have one production server working on environment Windows Server 2008 (32 bit) with SQL Server 2008 R2 Service pack 1 CU4 (32 bit).
We have created one link server to Oracle 9i server.
I migrated my 2000 database to 2008. I have created link server also.
But after that I am facing a very serious problem.
Sometimes my SQL server stop automatically and when I checked in error log I found stack dump in that. And event viewer log shows me following errors.
SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).
The OLE DB provider "MSDAORA" for linked server "IIS" reported an error 0x80004005 aborting the current transaction.
Can anyone please help me on this
March 29, 2012 at 1:58 pm
what oracle driver did you install on the sql server ? 9i, 10g, 11g?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply