I don’t have an exadata I can play with, and this looks like a bug to me. Could
you try
cast(10 as number)
cast(10 as binary_float)
cast(10 as binary_double)
and
cast (10.0000 as number)
in place of your constant 10. If any of these deliver a different answer on the
“wrong answer” system, it may help with the bug solution in that it would
indicate the problem area to be parsing constants and possibly taking some
invalid optimization pathway.
Regards,
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Patrick Jolliffe
Sent: Wednesday, August 05, 2015 9:36 AM
To: Sayan Sergeevich Malakshinov
Cc: oracle-l; oracle-l-bounce@xxxxxxxxxxxxx
Subject: Re: Strange Behaviour (with Test Case)
21:34:53 SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats
last advanced'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------
SQL_ID cpyzpwfcpn902, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ SUM( CASE WHEN
(CHRTYPE = 'R03' OR CHRTYPE = 'LDU') THEN RATE /10
END ) FROM TEST_FACT
Plan hash value: 1326015606
---------------------------------------------------------------------------------------------------
-------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost
(%CPU)| E-Time |
A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
-------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
3 (100)| |
1 |00:00:00.01 | 7 |
| 1 | SORT AGGREGATE | | 1 | 1 | 16 |
| |
1 |00:00:00.01 | 7 |
| 2 | TABLE ACCESS STORAGE FULL| TEST_FACT | 1 | 3 | 48 |
3 (0)| 00:00:01 |
3 |00:00:00.01 | 7 |
---------------------------------------------------------------------------------------------------
-------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TEST_FACT@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TEST_FACT"@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) SUM(CASE "CHRTYPE" WHEN 'R03' THEN "RATE"/10 WHEN 'LDU' THEN
"RATE"/10 END )[22]
2 - (rowset=200) "CHRTYPE"[VARCHAR2,3], "RATE"[NUMBER,22]
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
On 5 August 2015 at 20:29, Patrick Jolliffe <jolliffe@xxxxxxxxx> wrote:
Raw trace means what - will try to generate tommorrow? Here is explain plan
for now, sorry about the formatting:
20:27:25 SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------
SQL_ID 67bm5czzp2hs0, child number 0
-------------------------------------
SELECT SUM( CASE WHEN (CHRTYPE = 'R03' OR CHRTYPE =
'LDU') THEN RATE /10 END ) FROM TEST_FACT
Plan hash value: 1326015606
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)|
|
| 1 | SORT AGGREGATE | | 1 | 16 | |
|
| 2 | TABLE ACCESS STORAGE FULL| TEST_FACT | 3 | 48 | 3 (0)|
00:00:01 |
----------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
19 rows selected.
Elapsed: 00:00:00.20
On 5 August 2015 at 19:37, Sayan Sergeevich Malakshinov
<malakshinovss@xxxxxxxxx> wrote:
Could you show the execution plan or raw trace?
