RE: query from sql server linked server slow

From: "Stauffer, Bob" <RStauffer@xxxxxxxx>
To: 'Andrew Kerber' <andrew.kerber@xxxxxxxxx>
Date: Wed, 26 Dec 2018 22:01:59 +0000

Hi Andrew,

Excellent thought, but they’re using dedicated connections.

Thanks.

Bob Stauffer
rstauffer@xxxxxxxx
(717) 327-2646 x10646

From: Andrew Kerber [mailto:andrew.kerber@xxxxxxxxx]
Sent: Wednesday, December 26, 2018 16:54
To: Stauffer, Bob <RStauffer@xxxxxxxx>
Cc: Kellyn Pot'Vin-Gorman <dbakevlar@xxxxxxxxx>; oracle-l@xxxxxxxxxxxxx
Subject: Re: query from sql server linked server slow

This message is from an external source.
Please use caution when opening links and attachments.

When you connect to the database, you are either using a dedicated server or a
shared server.  Shared servers share the connection with other users.
Dedicated server means it is all yours.

https://docs.oracle.com/cd/B28359_01/server.111/b28310/manproc001.htm#ADMIN11168

You should be able to look at the connect descriptor and tell whether you are
using a shared or dedicated.  If it doesnt say, you might try adding a
dedicated option to the descriptor.

On Wed, Dec 26, 2018 at 3:49 PM Stauffer, Bob
<RStauffer@xxxxxxxx<mailto:RStauffer@xxxxxxxx>> wrote:
Hi Andrew,

Could you point me to something that will refresh my memory on the old shared
server issue?  It’s been a while since I worked full time with Oracle and it
doesn’t ring a bell.

Thanks.

Bob Stauffer

From: Andrew Kerber
[mailto:andrew.kerber@xxxxxxxxx<mailto:andrew.kerber@xxxxxxxxx>]
Sent: Wednesday, December 26, 2018 16:38
To: Stauffer, Bob <RStauffer@xxxxxxxx<mailto:RStauffer@xxxxxxxx>>
Cc: Kellyn Pot'Vin-Gorman <dbakevlar@xxxxxxxxx<mailto:dbakevlar@xxxxxxxxx>>;
oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: Re: query from sql server linked server slow

This message is from an external source.
Please use caution when opening links and attachments.

Did you check and see if you are hitting the old shared server issue?

On Wed, Dec 26, 2018 at 3:36 PM Stauffer, Bob
<RStauffer@xxxxxxxx<mailto:RStauffer@xxxxxxxx>> wrote:
Hi Kellyn,

Using extended events, I can see that the query that the SQL Server (2012)
submits to the Oracle database via the OLE DB linked server is exactly the same
as the query I run in the Oracle database (12.2) using SQL Developer.  Both
have the same sql id and hash plan value in the Oracle database.  But the one
submitted from SQL Server has a different (poor) execution plan in the Oracle
database and takes forever to run.  And the one run from SQL Developer has a
good execution plan and runs in less than 20 seconds in the Oracle database.
The stats are up to date in the Oracle database.  And locking the good plan in
Oracle doesn’t make a difference.

Any ideas on why the exact same SQL statement would generate 2 different
execution plans in the same Oracle database?

Bob Stauffer
rstauffer@xxxxxxxx<mailto:rstauffer@xxxxxxxx>
(717) 327-2646 x10646

From: Kellyn Pot'Vin-Gorman
[mailto:dbakevlar@xxxxxxxxx<mailto:dbakevlar@xxxxxxxxx>]
Sent: Wednesday, December 26, 2018 14:06
To: Stauffer, Bob <RStauffer@xxxxxxxx<mailto:RStauffer@xxxxxxxx>>
Cc: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: Re: query from sql server linked server slow

This message is from an external source.
Please use caution when opening links and attachments.

Hi Bob,
Although I don't know what version of MSSQL, I'll assume a newer version...:)

From the SSMS side, set up an extended event, (tracing, to translate) and track
down what the difference is.  Identify plan migration due to links, views and
distributed query.  From there, once identified, lock the plan on the SQL
Server side to ensure the most optimal plan.

https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events?view=sql-server-2017

Kellyn

On Wed, Dec 26, 2018 at 10:47 AM Stauffer, Bob
<RStauffer@xxxxxxxx<mailto:RStauffer@xxxxxxxx>> wrote:
Hi all,

Oracle 12.2, Red Hat 7, SQL Server 2012

I know, this isn’t a SQL Server forum, but I’m running out of ideas on how to
troubleshoot the following.  When running a specific query from SQL Developer
or SQLPlus on my Windows 7 PC in and Oracle database, that query is relatively
fast.  It returns 16 records in less than 20 seconds.  But when run in a SQL
Server instance using SSMS and using OPENQUERYlinked server, it never completes
– it will actually run for days if we don’t kill it.  I’ve tracked it down to a
difference in execution plans on the Oracle database side.  Of course, when run
in the Oracle database using SQL Developer, it generates an efficient execution
plan.  But when run via OPENQUERY/linked server in SQL Server, the execution
plan in Oracle is horrendous.  I’ve tried loading a better plan for the query
using dbms_spm.alter_sql_plan_baseline, etc., but it ignores the new plan.  And
I’ve tried creating a view for the query, but that makes no difference either.

First, can anyone explain why 2 different execution plans are used in the
Oracle db for the same query?  Supposedly OPENQUERY/linked server are just
submitting the query to Oracle for optimization without doing any
“pre-optimization”.  And any thoughts on how I can troubleshoot/figure out how
to fix or get around the problem?

Thanks much.

Bob Stauffer
rstauffer@xxxxxxxx<mailto:rstauffer@xxxxxxxx>

***CONFIDENTIALITY NOTICE***This email contains confidential information which
may also be legally privileged and which is intended only for the use of the
recipient(s) named above. If you are not the intended recipient, you are hereby
notified that forwarding or copying of this email, or the taking of any action
in reliance on its contents, may be strictly prohibited. If you have received
this email in error, please notify us immediately by reply email and delete
this message from your inbox. Thank you. E-Mail Opt Out Notice: This e-mail
message may include an advertisement. You have the right to request that we not
send future advertisements to you at this e-mail address. If you prefer not to
receive future advertisements from us at this e-mail address, please access the
following url http://www.optoutffc.com/ Fulton Financial Corporation, P.O. Box ;
4887, Lancaster, Pa. 17604
--

[Image removed by sender. Kellyn Pot'Vin on about.me]

Kellyn Pot'Vin-Gorman
DBAKevlar
Blog<https://protect2.fireeye.com/url?k=cfdb41b2541c1ea2.cfdaff77-07ed95c954874d23&u=http://dbakevlar.com>
President Denver SQL Server User
Group<https://protect2.fireeye.com/url?k=beac35d30c4f51fa.bead8b16-89612ddfa6d2909e&u=http://denversql.org/>
about.me/dbakevlar<http://about.me/dbakevlar>

***CONFIDENTIALITY NOTICE***This email contains confidential information which
may also be legally privileged and which is intended only for the use of the
recipient(s) named above. If you are not the intended recipient, you are hereby
notified that forwarding or copying of this email, or the taking of any action
in reliance on its contents, may be strictly prohibited. If you have received
this email in error, please notify us immediately by reply email and delete
this message from your inbox. Thank you. E-Mail Opt Out Notice: This e-mail
message may include an advertisement. You have the right to request that we not
send future advertisements to you at this e-mail address. If you prefer not to
receive future advertisements from us at this e-mail address, please access the
following url http://www.optoutffc.com/ Fulton Financial Corporation, P.O. Box ;
4887, Lancaster, Pa. 17604

--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'
***CONFIDENTIALITY NOTICE***This email contains confidential information which
may also be legally privileged and which is intended only for the use of the
recipient(s) named above. If you are not the intended recipient, you are hereby
notified that forwarding or copying of this email, or the taking of any action
in reliance on its contents, may be strictly prohibited. If you have received
this email in error, please notify us immediately by reply email and delete
this message from your inbox. Thank you. E-Mail Opt Out Notice: This e-mail
message may include an advertisement. You have the right to request that we not
send future advertisements to you at this e-mail address. If you prefer not to
receive future advertisements from us at this e-mail address, please access the
following url http://www.optoutffc.com/ Fulton Financial Corporation, P.O. Box ;
4887, Lancaster, Pa. 17604

--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'
***CONFIDENTIALITY NOTICE***This email contains confidential information which
may also be legally privileged and which is intended only for the use of the
recipient(s) named above. If you are not the intended recipient, you are hereby
notified that forwarding or copying of this email, or the taking of any action
in reliance on its contents, may be strictly prohibited. If you have received
this email in error, please notify us immediately by reply email and delete
this message from your inbox. Thank you. E-Mail Opt Out Notice: This e-mail
message may include an advertisement. You have the right to request that we not
send future advertisements to you at this e-mail address. If you prefer not to
receive future advertisements from us at this e-mail address, please access the
following url http://www.optoutffc.com/ Fulton Financial Corporation, P.O. Box ;
4887, Lancaster, Pa. 17604

References:
- query from sql server linked server slow
  - From: Stauffer, Bob
- Re: query from sql server linked server slow
  - From: Kellyn Pot'Vin-Gorman
- RE: query from sql server linked server slow
  - From: Stauffer, Bob
- Re: query from sql server linked server slow
  - From: Andrew Kerber
- RE: query from sql server linked server slow
  - From: Stauffer, Bob
- Re: query from sql server linked server slow
  - From: Andrew Kerber

RE: query from sql server linked server slow

Other related posts: