Re: query from sql server linked server slow

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_at_fult.com> 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_at_fult.com
>
> (717) 327-2646 x10646
>
>
>
> *From:* Kellyn Pot'Vin-Gorman [mailto:dbakevlar_at_gmail.com]
> *Sent:* Wednesday, December 26, 2018 14:06
> *To:* Stauffer, Bob <RStauffer_at_fult.com>
> *Cc:* oracle-l_at_freelists.org
> *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_at_fult.com> 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_at_fult.com
>
>
>
> ***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: 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
>
>
>
>
> ***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.'





--
http://www.freelists.org/webpage/oracle-l






(image/jpeg attachment: image001.jpg)