DBAs biggest fears I’d guess are Optimizer Wrong Query Results bugs as usually the optimizer does not write a message into the alert.log saying “Sorry, I was in a bad mood today …”.
The Oracle Database Optimizer is a complex piece – and in Oracle 12c it delivers great performance results. Plus (my personal experience when you know what to do) it is more predictable which I like a lot when changing databases from one to another release. But due to its complexity sometimes we see issues – and sometimes it is necessary to switch off tiny little pieces until a fix is available.
Roy just came across this one – and we believe it’s worth to tell you about it. Again, our intention is only to prevent issues when upgrading or migrating to Oracle Database 12.1.0.2.
Symptom:
An outer join query with a bind variable and a group by clause can produce wrong results in some cases.
Analysis:
If all of the following match, you may be hitting this bug:
– two or more subquery views are outer-joined on column C1
– column C1 is specified on select list of top-most query block
– column C1 is filtered on a bind value
Example:
create table test1(c1 number(5),c2 varchar2(16)); insert into test1 values(1,'3'); commit; set NULL NULL variable num1 number execute :num1 :=1;
— Following query retuns wrong result(NULL), this should return 1
select V.c1 from (SELECT c1 FROM test1 GROUP BY c1) V, (SELECT c1 FROM test1 WHERE c2 = '1' GROUP BY c1) V2 where V.c1 = :num1 and V.c1 = V2.c1(+);
Workaround:
alter session set "_optimizer_reduce_groupby_key" = false;
Please don’t use the workaround:
alter session set optimizer_features_enable=’12.1.0.1′;
as this will switch off other good 12.1.0.2 optimizer features working very well.
More information:
See MOS Note:20634449.8 describing:
Bug 20634449 – Wrong results from OUTER JOIN with a bind variable and a GROUP BY clause in 12.1.0.2
As far as I can see there are no interim (one-off/single) patches available right now.
–Mike
If you find it useful, this tweak on _optimizer_reduce_groupby_key is also required to ensure that SQLDescribeCol behaves properly:
SELECT VEHICLE_SERVER_ID,SUM(VALUE) FROM VEHICLE_INDICATOR_NUMBER WHERE (VEHICLE_SERVER_ID = 282503) GROUP BY VEHICLE_SERVER_ID;
SELECT VEHICLE_SERVER_ID,SUM(VALUE) FROM VEHICLE_INDICATOR_NUMBER GROUP BY VEHICLE_SERVER_ID;
Here, VEHICLE_SERVER_ID is a NUMBER(10), but in 12.1.0.2 is reported as a NUMBER(38). Setting _optimizer_reduce_groupby_key to false solves the issue !!
So caution with 12.1.0.2 … this optimizer setting seems to be very dangerous …
Hi Mike,
After battling with 12.1.0.2 for the last 12 months, I can honestly say that we have had to turn off lots of features and have raised a raft of SR’s for which there appear to be no fixes as yet, not just "tiny little pieces"
Your "please don’t use workaround" is the first thing Support gets you to try! We have four upgraded databases now, and every one of them is set to 12.1.0.1 to overcome MASSIVE performance issues (queries that run for DAYS rather than MINUTES)
Dynamic Sampling taking 20 seconds on a 150millisecond query. Adaptive joins that toggle long before they should. Underestimating I/O costs. Assuming that parallel I/O can scale indefinitely without blocking. Choosing non-unique index rather than a unique because it is unique 99.9% of the time. Auto generation of column groups causing system wide failures by invalidating hundreds of packages.
We have spent 1,000’s of man hours with almost no help from Oracle Support resolving issues.
I have been using Oracle since version 4 and this has been one of the most difficult series of upgrades I have ever done, and I have done hundreds.
My firm recommendation to all who ask has been "wait for 12.2"
Hi Andrew,
I see all your point, I know most of them already. And I fully understand your frustration.
If you want to drop me an email please do so (firstname.lastname@oracle.com). Then we can discuss those things you have seen.
Cheers
Mike
I have been working in Oracle as a DBA for 10 years. I have to agree with Andrew’s points. I am genuinely concerned over my job at present having upgraded one of my databases from 11.2.0.4 to 12.1.0.2. The Application vendor is firmly pointing the finger at me for having done the upgrade via DBUA rather than their recommended DATAPUMP method.
I can say that as an immediate workaround to the issues setting OPTIMIZER_FEATURES_MODE back to 11.2.0.4 got the database functional again. Maybe I could have got away with setting it to 12.1.0.1 – but as far as I can see the 12.1.0.2 optimizer is simply broken.