SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 SQL Server to Oracle using "bind variables"
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kk2796
Starting Member

USA
3 Posts

Posted - 12/07/2006 :  11:38:14  Show Profile  Reply with Quote
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.


Edited by - kk2796 on 12/07/2006 12:05:02

kk2796
Starting Member

USA
3 Posts

Posted - 12/08/2006 :  16:39:20  Show Profile  Reply with Quote
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

Sweden
30250 Posts

Posted - 12/08/2006 :  17:13:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
15676 Posts

Posted - 12/08/2006 :  17:20:09  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 12/08/2006 :  17:44:18  Show Profile  Reply with Quote
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

USA
3 Posts

Posted - 12/08/2006 :  18:30:08  Show Profile  Reply with Quote
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 Posts

Posted - 03/06/2013 :  14:39:21  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000