Re: query from sql server linked server slow

First off, apologies- reading this on a larger screen, I now see the versions. So even if you creat a sql brofile or use a baseline, the query uses a different plan when executed from SSMS?

And you’ve confirmed it’s the same sql that’s issued once it is presented to the oracle database, (sql_id, hash plan) as when you execute it from sql developer?

Can you paste the plans for both here? Maybe there’s something amiss one of us will notice.

Thanks,
Kellyn

On Wed, Dec 26, 2018 at 1:35 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
>

-- 


[image: Kellyn Pot'Vin on about.me]

*Kellyn Pot'Vin-Gorman*
DBAKevlar Blog <http://dbakevlar.com>
President Denver SQL Server User Group <http://denversql.org/>
about.me/dbakevlar





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






(image/jpeg attachment: image001.jpg)