Re: Strange Behaviour (with Test Case)

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Wed, 5 Aug 2015 16:37:45 -0500
Message-ID: <CAMHX9JJ_pozkaKztMvJ84z3QQh8T4i5JTCWFhoUpPTQZt7TFiA_at_mail.gmail.com>



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).

This might not even be exadata-specific as projection and summing stuff happens inside the DB layer, maybe your Exadata version is just new enough to hit this bug:

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
Tanel. On Wed, Aug 5, 2015 at 4:29 PM, Tanel Poder <tanel_at_tanelpoder.com> wrote: > Try with this ;-) > > ALTER SESSION SET EVENTS '10055 trace name context forever, level 0x200'; > > (parse a new cursor or flush shared pool for testing) > > > On Tue, Aug 4, 2015 at 4:51 AM, Patrick Jolliffe <jolliffe_at_gmail.com> > wrote: > >> I have been working though a strange case, seems specific to Exadata 12c. >> I have simplified as much as possible for now, testcase below. >> Obviously we are following up with Oracle support, but wonder if anyone >> is able to confirm what we are seeing. >> And/Or anybody want to hazard a guess as to what is going wrong? >> TIA >> Patrick >> >> >> CREATE TABLE TEST_FACT(CHRTYPE VARCHAR2(3), RATE NUMBER) >> INSERT INTO TEST_FACT VALUES('R03', 1.3) >> INSERT INTO TEST_FACT VALUES('R03', 1.3) >> INSERT INTO TEST_FACT VALUES('LDU', 0.21) >> COMMIT; >> >> SELECT SUM( >> CASE >> WHEN (CHRTYPE = 'R03' OR CHRTYPE = 'LDU') >> THEN RATE /10 >> END ) >> FROM TEST_FACT; >> >> Result on 11.2.0.4.4 and 12.1.0.2.3 (Linux) >> 0.281 >> >> 12.1.0.2.7 (Exadata) >> -8.70E+103 >>

>
>
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Aug 05 2015 - 23:37:45 CEST

Original text of this message