Bob,
The core of your question is: " why is statement X parsed (and then
executed) differently when issued from connection A or B".
If there is still a valid plan in SGA, but not used by other session, there
must be a reason.
I'd start checking V$SQL_OPTIMIZER_ENV
what's the reason for the different plans.
I'm not sure if that's sufficient, but it should help as a starting point.
My private, pure unscientific guess would be different collation settings
which drives some indexes useless. But please don't follow my guesswork. Go
for facts.
Hth,
Martin
Am Mi., 26. Dez. 2018, 19:48 hat Stauffer, Bob <RStauffer@xxxxxxxx>
geschrieben:
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
***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
