At now I am extensively testing the ADO, In-Memory functionalities and have couple of database in production.
They already use DBBP (Database Bundle Patch) 10 for engineering systems and database In-Memory and have several underscope parameters in effect.
Unfortunately, a few days ago we have faced with the wrong results caused by the Bug 20214168 - Wrong Results using aggregations of CASE expression with fix of bug 20003240 present.
This problem was already discussed on the freelist oracle-l thread Strange Behaviour (with Test Case).
Original thread was Exadata specific.
We dont have an Exadata yet but we use DBIM (Database In-Memory) and so we install DBBP patches.
I found out that solution from the thread with event 10055 don't help to resolve wrong results issue in our environment.
Let's create test table and data from the original bug:
SQL> create table test_fact(chrtype varchar2(3), rate number);
SQL> insert into test_fact values('R03', 1.3);
SQL> insert into test_fact values('LDU', 0.21);
SQL>
SQL> select *
2 from test_fact;
CHRTYPE RATE
--------- ----------
R03 1.3
LDU .21
Now execute below query:
SQL> select sum(
2 case
3 when chrtype in ('R03', 'LDU')
4 then rate/10
5 end) result
6 from test_fact;
RESULT
----------
.041
Notice wrong results. Above query should return 0.151 and not 0.041.Now we will execute same query with the event 10055 set:
SQL> alter session set events '10055 trace name context forever, level 0x200';
SQL>
SQL> select sum(
2 case
3 when chrtype in ('R03', 'LDU')
4 then rate/10
5 end) result
6 from test_fact;
RESULT
----------
.041
Notice that results are the same (they are wrong) and the event 10055 set to level 0x200 dont help.I observed these results in the environment with Patch 21188742 - Database Patch for Engineered Systems and DB In-Memory 12.1.0.2.10 (Jul2015) applied.
May be other levels of event 10055 could help?
[oracle@localhost ~]$ oerr ora 10055 10055, 00000, "Rowsets: turn off rowsets for various operations" // *Document: NO // *Cause: N/A // *Action: Turns off rowsets for various operations // Level: // 0x00000001 - turn off for table scan // 0x00000002 - turn off for hash join consume // 0x00000004 - turn off for hash join produce // 0x00000008 - turn off for group by // 0x00000010 - turn off for sort // 0x00000020 - turn off for table-queue out // 0x00000040 - turn off for table-queue in // 0x00000080 - turn off for identity // 0x00000100 - turn off for granule iterator // 0x00000200 - turn off for EVA functions // 0x00000400 - turn off for PL/SQL // 0x00000800 - turn off for upgrade // 0x00001000 - turn off for database startup // 0x00002000 - turn off for blobs and clobs // 0x00004000 - turn off for tracing row source // 0x00008000 - turn off rowset information in explain plan // 0x00010000 - disable hash join rowsets fast path // 0x00020000 - turn off for bloom create // 0x00040000 - turn off for bloom use // 0x00080000 - disable prefetch for hash join // 0x00100000 - disable prefetch for bloom // 0x00200000 - disable semi blocking hash join // 0x00400000 - turn off rowset for fixed table //Unfortunately, other levels also have not helped.
I found 2 possible solutions for this problem case.
1. set _rowsets_enabled to false:
SQL> select sum(
2 case
3 when chrtype in ('R03', 'LDU')
4 then rate/10
5 end) result
6 from test_fact;
RESULT
----------
.041
SQL>
SQL> alter session set "_rowsets_enabled"=false;
SQL>
SQL> select sum(
2 case
3 when chrtype in ('R03', 'LDU')
4 then rate/10
5 end) result
6 from test_fact;
RESULT
----------
.151
2. set _rowsets_max_rows to 1:
SQL> select sum(
2 case
3 when chrtype in ('R03', 'LDU')
4 then rate/10
5 end) result
6 from test_fact;
RESULT
----------
.041
SQL>
SQL> alter session set "_rowsets_max_rows"=1;
SQL>
SQL> select sum(
2 case
3 when chrtype in ('R03', 'LDU')
4 then rate/10
5 end) result
6 from test_fact;
RESULT
----------
.151
Off course, we shouldn't use underscope parameters without Oracle Support agreement.Instead we should apply the fix for bug 20214168 ASAP.
I tried to apply the patch 20214168 to this Oracle Home but OPatch informed me that patch already applied!
Recommended actions: The fixes by this patch are currently in the Oracle Home. There is no need to apply this patch.
Patch : 20214168
Bug SubSet of 21125181
Subset bugs are:
20214168
From the opatch lsinv I saw that definitely fix for bug 20214168 already installed:
So instead of install patch 21214168, we should install Patch 21553476: EXADATA X5-2 RESULTS WRONG NUMERIC CALCULATION.
I had installed patch 21553476 and problem was resolved after that!
SQL> select sum(
2 case
3 when chrtype in ('R03', 'LDU')
4 then rate/10
5 end) result
6 from test_fact
7 /
RESULT
----------
.151
