Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 SQL Server to Oracle using "bind variables"

Author  Topic 

kk2796
Starting Member

3 Posts

Posted - 2006-12-07 : 11:38:14
First, a disclaimer: I am not a SQL Server developer. However, I am working with a SQL Server developer who is running an application that accesses an Oracle database I work on.

The SQL Server application uses a LinkedServer (sp?) to run a query in the following fashion (this is a syntactically incorrect approximation):

SELECT * FROM OPENQUERY(OraServer, 'SELECT * FROM COMPLEX_VIEW WHERE INDEXED_FIELD = @SQL_SERVER_VARIABLE_NAME');

The exact syntax currently being used is inconsequential to our quandry. Basically, the SQL Server application is being used by hundreds of online users, and the above SQL Server command is being executed thousands of times per day. When a user runs the statement for an @SQL_SERVER_VARIABLE_NAME value of 'ABC123', here is what Oracle sees:

SELECT * FROM COMPLEX_VIEW WHERE INDEXED_FIELD = 'ABC123';

This is bad. COMPLEX_VIEW is, well, a hugely complex Oracle view pulling from nearly a dozen tables. It takes Oracle approximately 4 seconds to parse and generate an execution plan for:

SELECT * FROM COMPLEX_VIEW WHERE INDEXED_FIELD = 'ABC123';


And the resulting takes up approximately 10k of memeory in Oracle's shared pool. For comparison, once the plan is generated, the actual query on average runs in 0.033 seconds. So 99.3% of the time used by this query is parsing/processing it.

If another user runs the SQL Server statement for indexed field value of 'ABC124', there is again a huge drain of resources as Oracle now spends 4 seconds and 10k Shared Pool to parse/process:

SELECT * FROM COMPLEX_VIEW WHERE INDEXED_FIELD = 'ABC124';

The more users there are at a time, the more work the Oracle server is doing, and the slower the SQL Server application runs. On the Oracle side the situation is even worse: the entire server is being brought to its knees trying to parse thousands of variants of this query. Aside from general performance degredation, we have actually had system crashes because the Oracle Shared Pool (where Oracle puts the 10k execution plans for each query) has filled up faster than than Oracle could clean it out.

So here is my question: Is there ANYTHING that can be done on the SQL Server side so that Oracle will receive a query that looks like this:

SELECT * FROM COMPLEX_VIEW WHERE INDEXED_FIELD = :B1;

In Oracle terminology, this is called "using a bind variable" - I'm sure SQL Server has something similar. If this query is what SQL Server sent to Oracle, it would only get parsed/processed *once* (for the WHOLE DAY!), and every subsequent SQL Server user would get the 0.033 second response time.

So, can SQL Server run a query that uses Oracle's bind variables? Please let me know if any other information is needed. Thanks in advance for your assistance!

Edit: Just realized some obvious bits of info:
--> SQL Server version : SQL Server 2000
--> Oracle Database: 10gR2
--> (current) bus. requirements: The SQL Server app must be able to view "current state" of data in Oracle. Pushing all of the data underlying the COMPLEX_VIEW to a SQL Server table would be very costly - the full set of data would be gigabytes in size, and we'd need to synch it at least hourly.
--> Current planned work-around: we are considering creating an Oracle stored procedure which accepts a parameter for INDEXED_FIELD... so SQL Server: would code the following:

SELECT * FROM OPENQUERY(OraServer, 'SELECT FUNCTION_NAME(@SQL_SERVER_VARIABLE_NAME) from DUAL');

And Oracle would receive the following from the OLEDB:
SELECT FUNCTION_NAME('ABC123') from DUAL;

The complex_view query would be coded within FUNCTION_NAME, so Oracle would not re-parse COMPLEX_VIEW with each call... however, this approach feels very constrained and has many downsides from a long-term maintenance perspective.

kk2796
Starting Member

3 Posts

Posted - 2006-12-08 : 16:39:20
Is there anyone on these formums experienced enough with SQL Server's OLEDB interface to comment on the this? It would even be helpful to hear someone say "I've worked with linked servers in SQl Server before, and I'm not aware of any way to send parameterized queries through OLE DB".

Am I barking up the wrong tree by asking this here? If so, can anyone recommend a different resource/website/book better suited for helping me out?

Thanks in advance!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-08 : 17:13:06
SELECT * FROM OraServer..COMPLEX_VIEW WHERE INDEXED_FIELD = @SQL_SERVER_VARIABLE_NAME


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-12-08 : 17:20:09
For Oracle linked servers, I believe you're stuck with OPENQUERY. The fact that Oracle is not parsing it efficiently is not something you can address from the SQL Server side. I've never encountered performance issues like what you describe, and certainly never seen Oracle spend 90% of the time parsing a query. I think there is a bug in that view, it's certainly worth looking at it to see if it can be improved.

Your idea about using the function is a good one, my suggestion was to create a stored procedure in Oracle that can accept the paramter, so that the (rough) syntax would be:

SELECT * FROM OPENQUERY(OraServer, 'execute myOracleProcedure ''ABC123''');

But I don't know if that will work. This is assuming that Oracle caches stored procedure plans similar to how SQL Server does. I've never used Oracle stored procedures, my Oracle linked server experience is about what you've encountered, and I've passed literal strings to Oracle queries like you've described. You *may* be able to use a four-part named call to your Oracle linked server like this:

EXEC OraServer.myDbo.mySchema.myOracleProcedure 'ABC123'

That works with SQL Server linked servers, but I've never done it with other DB products.
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-12-08 : 17:44:18
what driver are you using in your linked server? are you using microsoft's oracle odbc/oledb drivers or are you using Oracle's odbc or oldedb driver?

You might have better luck using Oracle's drivers instead of the microsoft versions. Microsoft hasn't updated their version for many years.



-ec
Go to Top of Page

kk2796
Starting Member

3 Posts

Posted - 2006-12-08 : 18:30:08
Good stuff -

Peso, I'll give that a shot, I didn't even know that syntax existed.
Robvolk - thanks for the input. The view is "broken" in the sense that it is a view built on top of 3 other views, each of which accesses many tables. In 10g, the CBO has been structured to come up with more efficient plans (a good thing), but this means deriving a plan for a complex query useing 15 tables (I just counted) and all associated statistics is a time and CPU-intesnsive effort. Undoubtedly, the COMPLEX_VIEW definition could be rewritten to eliminate some complexity and redundancy - I'd be confident that it could be at made at least 20% "simpler". But we need order-of-magnitude levels of reduction, especially since we're projecting more users to start using the SQL Server app in a few months. I imagine we'll wind up going the Oracle stored procedure route.
Eyechart - good question. I'll find out.

Thanks everyone, SQL Server colleague is out until Monday - but I'll see if we can't try out some of the suggestions and ideas here first thing Monday morning. I'll report back with updates!
Go to Top of Page

rocky.community
Starting Member

1 Post

Posted - 2013-03-06 : 14:39:21
Hi Guys, this is old forum topic but even today I am facing same issue. Di anyone know solution for this issue. SQL Server is using variables but when it comes to oracle, it comes as literals. SQL Server Version is SQL Server 2008, Oracle is 11.2.0.2
We don't want to user cursor_sharing=FORCE in Oracle as it may affcet many other queries
Go to Top of Page
   

- Advertisement -