Re: Strange Behaviour (with Test Case)

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Thu, 6 Aug 2015 12:26:52 +0800
Message-ID: <CABx0cSWRFPWUjNDDjQGtZKppP5e5bbZ5X8SqM_s1kje47K60JA_at_mail.gmail.com>



Support identified as existing Bug 20214168 (QUERY WITH CASE CLAUSE IS RETURNING INCORRECT RESULT) On 6 August 2015 at 07:19, Patrick Jolliffe <jolliffe_at_gmail.com> wrote:

> Nice one, thanks Tanel.
>
> SQL> ALTER SESSION SET EVENTS '10055 trace name context forever, level
> 0x200';
>
>
> Session
> altered.
>
>
>
> SQL>
>
> SQL> SELECT /*+ gather_plan_statistics tanel */
> SUM(
> 2
> CASE
>
> 3 WHEN (CHRTYPE = 'R03' OR CHRTYPE =
> 'LDU')
> 4 THEN RATE
> /10
> 5 END
> )
>
> 6 FROM
> TEST_FACT;
>
>
>
> SUM(CASEWHEN(CHRTYPE='R03'ORCHRTYPE='LDU')THENRATE/10END)
>
> ---------------------------------------------------------
>
>
> .281
>
>
> SQL>
>
> On 6 August 2015 at 07:04, Stefan Koehler <contact_at_soocs.de> wrote:
>
>> Hi Tanel,
>> maybe a little OT, but i don't want to miss the chance as you already
>> have given a basic description about rowsets.
>>
>> Martin Bach asked me about the rowset spec in the column projection
>> information some time ago and i could not figure out (or find) any details
>> about
>> it. The question was in a different context (FTS in his case), but the
>> only stuff i could figure out was event 10055 and some hidden parameters to
>> control it. You can find my test case here:
>> http://www.soocs.de/public/research/150120_dbms_xplan_count_rowset.txt
>>
>> Do you have any more details about the implementation of this pipeline
>> optimization (rowsets)?
>>
>> Thank you very much.
>>
>> Best Regards
>> Stefan Koehler
>>
>> Freelance Oracle performance consultant and researcher
>> Homepage: http://www.soocs.de
>> Twitter: _at_OracleSK
>>
>> > Tanel Poder <tanel_at_tanelpoder.com> hat am 5. August 2015 um 23:37
>> geschrieben:
>> >
>> > Just in case you're wondering what the heck this is - rowsets are a
>> yet another execution plan pipeline optimization to save a few CPU cycles,
>> > which is apparently buggy. The CASE statements are evaluated in EVA
>> functions, thus 0x200 should help (helped on my exadata test).
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Aug 06 2015 - 06:26:52 CEST

Original text of this message