Tuesday Jul 28, 2015

Optimizer Issue in Oracle 12.0.1.2: "Reduce Group By"

Wrong Query Results BugDBAs 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

Thursday Nov 17, 2011

DOAG Conference 2011: Seven Flavors of Database Upgrades

Thanks to everybody who did attend at my DOAG Conference session in Nürnberg this year "Seven Flavor of Database Upgrades" (or in German: "7 Wege zum Datenbank-Upgrade - Geschichten, die das Leben schrieb"). And thanks for your patience staying with me in overtime as well ;-)

In case you'd like to download the slides I've presented at the session please download them via this link or from the download section to your right.

About

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle Corp

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite or remotely with reference customers. Acting as interlink between customers/partners and the Upgrade Development.

Follow me on TWITTER

Contact me via LinkedIn or XING

Search

Archives
« August 2015
SunMonTueWedThuFriSat
      
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
     
Today
Oracle related Tech Blogs
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers