Posted by: Clifford W. Bass
Hi All,
Here is a report from Log-A-Bug about a case that I submitted to Gupta.
Short version of what I originally submitted (I will not detail the
script I submitted; the select statement was a complicated one and as far as
I know, was the only one significantly impacted):
Attached is a SQL script that you can use to replicate the problem.
I noticed that the execution plan with version 8.5 is different from the
execution plan with version 9.0. These are noted at the end of the script.
The configurations for both versions of the servers that I tried it with are
essentially the same. It is really important to me that it not take so long
nor bog the server down like it does. I hope you can fix it soon. The
results I got with testing are 164 rows in 1.06 seconds with version 8.5 and
164 rows in 1941.99 seconds with version 9.0.
Their first reply:
Log-A-Bug @ Gupta Technologies has reviewed the issue listed below and
determined that there is insufficient information to determine exactly what
the problem is that is being reported.
Please feel free to re-submit the request with further information to help
us to log a defect.
I tested the query against the database you submitted in 9.0.1 and
9.0.0-ptf1. It was very fast in both versions (about a second). There were
changes that could cause different query plans, but I could see no
degradation in performance. 1942 seconds?!? Wow, I'd like to be able to
reproduce that to create a defect report. Any additional input to recreate
the problem is much appreciated.
My resubmission:
The person who checked this the first time gets his/her wish. The
response I got gave me an indirect clue that the issue may be connected to a
SQL.INI setting. After a little trial and error I discovered that it is!
My server SQL.INI has "oracleouterjoin=1". If I remove it the select
statement in the attached script runs quickly. When I put it back in, it
goes back to running very slowly.
Just to be totally sure, I double-checked my 8.5 (no PTFs)
installation and the select statement does run quickly with the
"oracleouterjoin=1" setting.
Their reply.
Log-A-Bug has reviewed the issue listed below and determined that this is a
defect. The defect number is 83503. For further information watch for the
fix in the Fixes.Wri file in future PTF's or Releases.
Oracleouterjoin! Who'd have thunk it? Thanks for the report and thanks for
making my wish come true!
Clifford Bass