Forum Stats

  • 3,741,953 Users
  • 2,248,511 Discussions
  • 7,862,086 Comments

Discussions

Reproducible testcase for Wrong Results

MajkiiT
MajkiiT Member Posts: 10 Green Ribbon
edited Feb 4, 2021 8:04PM in General Database Discussions

Hey all,

I think I have found a reproducible testcase for select with wrong results at 19.6 DB version. I can reproduce it at our databases, virtual appliance by Oracle, even at LiveSql. But Oracle Support still does not think so and cannot reproduce :) and want from me stats (which is not exported stats as I thought first):

When I say I need stats for the tables, I mean I need a reproducible testcase that includes stats!

So could you please tell me, if you can reproduce same results from this script:

Script at LiveSql: https://livesql.oracle.com/apex/livesql/s/jzc2uyw6ecf2z2ul35nyrxelv

 DROP TABLE TEST_SAPFI_COICAR_AT5DAT11;
 CREATE TABLE TEST_SAPFI_COICAR_AT5DAT11
 ("DATUCT" DATE,
  "DATUMZPRAC" NUMBER(8,0)
 ) 
 ROW STORE COMPRESS ADVANCED 
 PARTITION BY LIST ("DATUMZPRAC") 
 (PARTITION "P20000101" VALUES (20000101));
alter table TEST_SAPFI_COICAR_AT5DAT11 add partition P20200414 values (20200414);
insert /*+ append */ into TEST_SAPFI_COICAR_AT5DAT11
select date'2019-11-20' datuct, 20200414 datumzprac from dual connect by level < 2 union all
select date'2019-12-20' datuct, 20200414 datumzprac from dual connect by level < 2 union all
select date'2019-12-29' datuct, 20200414 datumzprac from dual connect by level < 4 union all
select date'2020-01-01' datuct, 20200414 datumzprac from dual connect by level < 55 union all
select date'2020-01-08' datuct, 20200414 datumzprac from dual connect by level < 3 union all
select date'2020-01-13' datuct, 20200414 datumzprac from dual connect by level < 8 union all
select date'2020-01-14' datuct, 20200414 datumzprac from dual connect by level < 117 union all
select date'2020-01-15' datuct, 20200414 datumzprac from dual connect by level < 65 union all
select date'2020-01-30' datuct, 20200414 datumzprac from dual connect by level < 2 union all
select date'2020-01-31' datuct, 20200414 datumzprac from dual connect by level < 12 union all
select date'2020-02-01' datuct, 20200414 datumzprac from dual connect by level < 20 union all
select date'2020-02-05' datuct, 20200414 datumzprac from dual connect by level < 4 union all
select date'2020-02-10' datuct, 20200414 datumzprac from dual connect by level < 5 union all
select date'2020-02-12' datuct, 20200414 datumzprac from dual connect by level < 2 union all
select date'2020-02-17' datuct, 20200414 datumzprac from dual connect by level < 2 union all
select date'2020-02-21' datuct, 20200414 datumzprac from dual connect by level < 16 union all
select date'2020-02-29' datuct, 20200414 datumzprac from dual connect by level < 37 union all
select date'2020-03-01' datuct, 20200414 datumzprac from dual connect by level < 1851 union all
select date'2020-03-02' datuct, 20200414 datumzprac from dual connect by level < 227 union all
select date'2020-03-03' datuct, 20200414 datumzprac from dual connect by level < 75 union all
select date'2020-03-04' datuct, 20200414 datumzprac from dual connect by level < 19 union all
select date'2020-03-05' datuct, 20200414 datumzprac from dual connect by level < 107 union all
select date'2020-03-06' datuct, 20200414 datumzprac from dual connect by level < 163 union all
select date'2020-03-07' datuct, 20200414 datumzprac from dual connect by level < 72 union all
select date'2020-03-08' datuct, 20200414 datumzprac from dual connect by level < 78 union all
select date'2020-03-09' datuct, 20200414 datumzprac from dual connect by level < 187 union all
select date'2020-03-10' datuct, 20200414 datumzprac from dual connect by level < 124 union all
select date'2020-03-11' datuct, 20200414 datumzprac from dual connect by level < 92 union all
select date'2020-03-12' datuct, 20200414 datumzprac from dual connect by level < 137 union all
select date'2020-03-13' datuct, 20200414 datumzprac from dual connect by level < 397 union all
select date'2020-03-14' datuct, 20200414 datumzprac from dual connect by level < 52 union all
select date'2020-03-15' datuct, 20200414 datumzprac from dual connect by level < 16 union all
select date'2020-03-16' datuct, 20200414 datumzprac from dual connect by level < 622 union all
select date'2020-03-17' datuct, 20200414 datumzprac from dual connect by level < 215 union all
select date'2020-03-18' datuct, 20200414 datumzprac from dual connect by level < 299 union all
select date'2020-03-19' datuct, 20200414 datumzprac from dual connect by level < 265 union all
select date'2020-03-20' datuct, 20200414 datumzprac from dual connect by level < 627 union all
select date'2020-03-21' datuct, 20200414 datumzprac from dual connect by level < 52 union all
select date'2020-03-22' datuct, 20200414 datumzprac from dual connect by level < 60 union all
select date'2020-03-23' datuct, 20200414 datumzprac from dual connect by level < 168 union all
select date'2020-03-24' datuct, 20200414 datumzprac from dual connect by level < 255 union all
select date'2020-03-25' datuct, 20200414 datumzprac from dual connect by level < 185 union all
select date'2020-03-26' datuct, 20200414 datumzprac from dual connect by level < 240 union all
select date'2020-03-27' datuct, 20200414 datumzprac from dual connect by level < 663 union all
select date'2020-03-28' datuct, 20200414 datumzprac from dual connect by level < 88 union all
select date'2020-03-29' datuct, 20200414 datumzprac from dual connect by level < 771 union all
select date'2020-03-30' datuct, 20200414 datumzprac from dual connect by level < 328 union all
select date'2020-03-31' datuct, 20200414 datumzprac from dual connect by level < 1675 union all
select date'2020-04-01' datuct, 20200414 datumzprac from dual connect by level < 641 union all
select date'2020-04-02' datuct, 20200414 datumzprac from dual connect by level < 251 union all
select date'2020-04-03' datuct, 20200414 datumzprac from dual connect by level < 84 union all
select date'2020-04-06' datuct, 20200414 datumzprac from dual connect by level < 325 union all
select date'2020-04-07' datuct, 20200414 datumzprac from dual connect by level < 366 union all
select date'2020-04-08' datuct, 20200414 datumzprac from dual connect by level < 459 union all
select date'2020-04-09' datuct, 20200414 datumzprac from dual connect by level < 2470 union all
select date'2020-04-10' datuct, 20200414 datumzprac from dual connect by level < 16 union all
select date'2020-04-11' datuct, 20200414 datumzprac from dual connect by level < 16 union all
select date'2020-04-12' datuct, 20200414 datumzprac from dual connect by level < 24 union all
select date'2020-04-13' datuct, 20200414 datumzprac from dual connect by level < 130 union all
select date'2020-04-14' datuct, 20200414 datumzprac from dual connect by level < 9;
commit;
/
declare
  schema_name varchar2(128);
begin
  select sys_context('userenv', 'current_schema') into schema_name from dual;
  dbms_stats.gather_table_stats(
      ownname => schema_name,
      tabname => 'TEST_SAPFI_COICAR_AT5DAT11',
      partname => 'P20200414',
      estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
      method_opt=> 'FOR ALL INDEXED COLUMNS SIZE AUTO'
  );
end;
/
 DROP TABLE TEST_DWF_SAPFI;
 CREATE TABLE TEST_DWF_SAPFI
 (
  "DATUMUCETNIPOM_CODE" NUMBER(8,0) NOT NULL ENABLE
 ) 
 ROW STORE COMPRESS ADVANCED 
 PARTITION BY LIST ("DATUMUCETNIPOM_CODE") 
 (PARTITION "P20000101" VALUES (20000101) );
/
begin
  for i in (select unique to_char(datuct, 'YYYYMMDD') DATUMUCETNIPOM_CODE from TEST_SAPFI_COICAR_AT5DAT11 order by 1)
  loop
      execute immediate 'alter table TEST_DWF_SAPFI add partition P' || i.DATUMUCETNIPOM_CODE || ' values (' || i.DATUMUCETNIPOM_CODE || ')';
  end loop;
end;
/
insert /*+ append */ into TEST_DWF_SAPFI select to_number(to_char(datuct, 'YYYYMMDD')) from TEST_SAPFI_COICAR_AT5DAT11 where datumzprac = 20200414;
commit;
select /*+ use_hash(TEST_DWF_SAPFI) */ count(*) count_hash from TEST_DWF_SAPFI
where exists (SELECT 1 FROM test_sapfi_coicar_at5dat11
              WHERE DATUMZPRAC = 20200414
              AND to_char(DATUCT,'YYYYMMDD') = DATUMUCETNIPOM_CODE
);
select count(*) count_nl from TEST_DWF_SAPFI
where exists (SELECT /*+ use_nl(test_sapfi_coicar_at5dat11) */ 1 FROM test_sapfi_coicar_at5dat11
              WHERE DATUMZPRAC = 20200414
              AND to_char(DATUCT,'YYYYMMDD') = DATUMUCETNIPOM_CODE
);

Selects at the end are identical expect hint.

From my view first select returns correct result: 15197, but the second returns 8, which is wrong.


Or any advices how to help Support with reproducing? Thanks!

Answers

  • JohnWatson2
    JohnWatson2 Member Posts: 4,268 Silver Crown

    In my release 19.3, your second query is getting two execution plans, because of the adaptive features:

    orclz>
    orclz> alter system flush shared_pool;
    
    System altered.
    
    orclz> sho parameter adapt
    
    NAME                 TYPE    VALUE
    ------------------------------------ ----------- ------------------------------
    fileio_network_adapters       string
    optimizer_adaptive_plans       boolean   TRUE
    optimizer_adaptive_reporting_only  boolean   FALSE
    optimizer_adaptive_statistics    boolean   FALSE
    parallel_adaptive_multi_user     boolean   FALSE
    orclz> select count(*) count_nl from TEST_DWF_SAPFI
     2 where exists (SELECT /*+ use_nl(test_sapfi_coicar_at5dat11) */ 1 FROM test_sapfi_coicar_at5dat11
     3        WHERE DATUMZPRAC = 20200414
     4        AND to_char(DATUCT,'YYYYMMDD') = DATUMUCETNIPOM_CODE
     5 );
    
        COUNT_NL
    ---------------
           8
    
    orclz> select * from table(dbms_xplan.display_cursor(format=>'adaptive'));
    
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID 0wjkj1sm64u10, child number 0
    -------------------------------------
    select count(*) count_nl from TEST_DWF_SAPFI where exists (SELECT /*+
    use_nl(test_sapfi_coicar_at5dat11) */ 1 FROM test_sapfi_coicar_at5dat11
           WHERE DATUMZPRAC = 20200414        AND
    to_char(DATUCT,'YYYYMMDD') = DATUMUCETNIPOM_CODE )
    
    Plan hash value: 4136990162
    
    -----------------------------------------------------------------------------------------------------------------------------
    |  Id | Operation          | Name            | Rows | Bytes | Cost (%CPU)| Time   | Pstart| Pstop |
    -----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT       |              |    |    |  321 (100)|     |    |    |
    |   1 | SORT AGGREGATE       |              |   1 |  20 |      |     |    |    |
    |- * 2 |  HASH JOIN         |              | 38491 |  751K|  321  (1)| 00:00:01 |    |    |
    |   3 |  PART JOIN FILTER CREATE  | :BF0000          | 38491 |  751K|  321  (1)| 00:00:01 |    |    |
    |   4 |   NESTED LOOPS       |              | 38491 |  751K|  321  (1)| 00:00:01 |    |    |
    |-  5 |   STATISTICS COLLECTOR  |              |    |    |      |     |    |    |
    |   6 |    SORT UNIQUE      |              |  152 | 2128 |  13  (0)| 00:00:01 |    |    |
    |   7 |    PARTITION LIST SINGLE |              |  152 | 2128 |  13  (0)| 00:00:01 |   2 |   2 |
    |   8 |     TABLE ACCESS FULL  | TEST_SAPFI_COICAR_AT5DAT11 |  152 | 2128 |  13  (0)| 00:00:01 |   2 |   2 |
    |   9 |   PARTITION LIST ITERATOR |              |  253 | 1518 |  307  (1)| 00:00:01 |  KEY |  KEY |
    | * 10 |    TABLE ACCESS FULL   | TEST_DWF_SAPFI       |  253 | 1518 |  307  (1)| 00:00:01 |  KEY |  KEY |
    |-  11 |  PARTITION LIST JOIN-FILTER|              | 15197 | 91182 |  307  (1)| 00:00:01 |:BF0000|:BF0000|
    |-  12 |   TABLE ACCESS FULL    | TEST_DWF_SAPFI       | 15197 | 91182 |  307  (1)| 00:00:01 |:BF0000|:BF0000|
    -----------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
      2 - access("DATUMUCETNIPOM_CODE"=TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("DATUCT"),'YYYYMMDD')))
     10 - filter("DATUMUCETNIPOM_CODE"=TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("DATUCT"),'YYYYMMDD')))
    
    Hint Report (identified by operation id / Query Block Name / Object Alias):
    Total hints for statement: 1 (U - Unused (1))
    ---------------------------------------------------------------------------
    
      8 - SEL$5DA710D3 / [email protected]$2
         U - use_nl(test_sapfi_coicar_at5dat11)
    
    Note
    -----
      - this is an adaptive plan (rows marked '-' are inactive)
    
    
    44 rows selected.
    
    orclz> select count(*) count_nl from TEST_DWF_SAPFI
     2 where exists (SELECT /*+ use_nl(test_sapfi_coicar_at5dat11) */ 1 FROM test_sapfi_coicar_at5dat11
     3        WHERE DATUMZPRAC = 20200414
     4        AND to_char(DATUCT,'YYYYMMDD') = DATUMUCETNIPOM_CODE
     5 );
    
        COUNT_NL
    ---------------
         15197
    
    orclz> select * from table(dbms_xplan.display_cursor(format=>'adaptive'));
    
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID 0wjkj1sm64u10, child number 1
    -------------------------------------
    select count(*) count_nl from TEST_DWF_SAPFI where exists (SELECT /*+
    use_nl(test_sapfi_coicar_at5dat11) */ 1 FROM test_sapfi_coicar_at5dat11
           WHERE DATUMZPRAC = 20200414        AND
    to_char(DATUCT,'YYYYMMDD') = DATUMUCETNIPOM_CODE )
    
    Plan hash value: 1883320889
    
    ---------------------------------------------------------------------------------------------------------------------------
    | Id | Operation          | Name            | Rows | Bytes | Cost (%CPU)| Time   | Pstart| Pstop |
    ---------------------------------------------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT       |              |    |    |  400 (100)|     |    |    |
    |  1 | SORT AGGREGATE       |              |   1 |  20 |      |     |    |    |
    |* 2 |  HASH JOIN         |              |  120K| 2349K|  400  (1)| 00:00:01 |    |    |
    |  3 |  PART JOIN FILTER CREATE  | :BF0000          | 15197 |  207K|  13  (0)| 00:00:01 |    |    |
    |  4 |   SORT UNIQUE       |              | 15197 |  207K|  13  (0)| 00:00:01 |    |    |
    |  5 |   PARTITION LIST SINGLE  |              | 15197 |  207K|  13  (0)| 00:00:01 |   2 |   2 |
    |  6 |    TABLE ACCESS FULL   | TEST_SAPFI_COICAR_AT5DAT11 | 15197 |  207K|  13  (0)| 00:00:01 |   2 |   2 |
    |  7 |  PARTITION LIST JOIN-FILTER|              | 15197 | 91182 |  307  (1)| 00:00:01 |:BF0000|:BF0000|
    |  8 |   TABLE ACCESS FULL    | TEST_DWF_SAPFI       | 15197 | 91182 |  307  (1)| 00:00:01 |:BF0000|:BF0000|
    ---------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
      2 - access("DATUMUCETNIPOM_CODE"=TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("DATUCT"),'YYYYMMDD')))
    
    Hint Report (identified by operation id / Query Block Name / Object Alias):
    Total hints for statement: 1 (U - Unused (1))
    ---------------------------------------------------------------------------
    
      6 - SEL$5DA710D3 / [email protected]$2
         U - use_nl(test_sapfi_coicar_at5dat11)
    
    Note
    -----
      - statistics feedback used for this statement
    
    
    39 rows selected.
    
    orclz>
    


  • user9540031
    user9540031 Member Posts: 101 Silver Badge

    Hello,

    Totally reproduced here on DB versions 12.2 and 19.5. As @JohnWatson2 mentioned, the wrong results only affect the 1st execution of the query; on the 2nd execution the cursor is reoptimized, yielding a new plan without the bug.

    Just talk to Support... Good luck!

    Meanwhile, there's to say about the faulty query:

    • The hint is incorrect, as shown in the Hint Report section of the plan in @JohnWatson2's comment: U stands for "unused"
    • The predicate to_char(DATUCT, 'YYYYMMDD') = DATUMUCETNIPOM_CODE takes a date and a number, and does a char vs number comparison; it is rewritten by the optimizer as: "DATUMUCETNIPOM_CODE"=TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("DATUCT"),'YYYYMMDD')); I don't know what the INTERNAL_FUNCTION is doing exactly, nevertheless it would be simpler to write that comparison as DATUCT = to_date(to_char(DATUMUCETNIPOM_CODE), 'YYYYMMDD'), making it a date vs date comparison with no implicit conversion.

    And the call to dbms_stats.gather_table_stats shown in the test, because of the method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO' parameter—but there's no index here!—only gathers partition statistics (numrows, blocks, avg_row_len, etc.), but does not gather column statistics at partition-level; why not use method_opt => 'FOR ALL COLUMNS SIZE 1'?

    But yes, that's clearly a (discomforting) bug.

    Regards,

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,525 Black Diamond

    Try disabling bloom filter prooning.

    alter session set "_bloom_pruning_enabled"=FALSE;
    

    Or try disabling bloom filters completely

    alter session set "_bloom_filter_enabled"=FALSE;
    

    And see if it fixes the issue. You'll have a workaround if it does. And open SR, as others already suggested.

    SY.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,682 Gold Crown
    edited Feb 5, 2021 7:23PM

    There seems to be something wrong in the adaptive plan mechanism - the clue is in John Watson's full plan report. Operation 3 shows

    |   3 |  PART JOIN FILTER CREATE  | :BF0000          |
    

    Note particularly that it doesn't have a '-' by it to indicate that it's "inactive"; but a Bloom filter is something that should apply only for a hash join and this is running a nested loop join (all the hash join lines are marked as inactive).

    The 8 rows that you get back correspond to the 8 rows in partition p20200414 of TEST_DWF_SAPFI because somehow Oracle has decided that it needs a Bloom filter to eliminate data that doesn't correspond to the partiion you're using as the driving table, and that's partition p20200414 of TEST_SAPFI_COICAR_AT5DAT11

    Here's the plan when forced to go wrong - 19.3, with rowsource execution stats:

    <pre>
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                  | Name                       | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT           |                            |      1 |        |   318 (100)|       |       |      1 |00:00:00.02 |     215 |       |       |          |
    |   1 |  SORT AGGREGATE            |                            |      1 |      1 |            |       |       |      1 |00:00:00.02 |     215 |       |       |          |
    |   2 |   PART JOIN FILTER CREATE  | :BF0000                    |      1 |   1515K|   318  (23)|       |       |      8 |00:00:00.02 |     215 |       |       |          |
    |   3 |    NESTED LOOPS            |                            |      1 |   1515K|   318  (23)|       |       |      8 |00:00:00.02 |     215 |       |       |          |
    |   4 |     SORT UNIQUE            |                            |      1 |  15197 |     8  (13)|       |       |     60 |00:00:00.01 |      26 |  4096 |  4096 | 4096  (0)|
    |   5 |      PARTITION LIST SINGLE |                            |      1 |  15197 |     8  (13)|     2 |     2 |  15197 |00:00:00.01 |      26 |       |       |          |
    |   6 |       TABLE ACCESS FULL    | TEST_SAPFI_COICAR_AT5DAT11 |      1 |  15197 |     8  (13)|     2 |     2 |  15197 |00:00:00.01 |      26 |       |       |          |
    |   7 |     PARTITION LIST ITERATOR|                            |     60 |   3192 |   152   (3)|   KEY |   KEY |      8 |00:00:00.01 |     189 |       |       |          |
    |*  8 |      TABLE ACCESS FULL     | TEST_DWF_SAPFI             |     60 |   3192 |   152   (3)|   KEY |   KEY |      8 |00:00:00.01 |     189 |       |       |          |
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    </pre>
    

    Note how you start operations 7 and 8 60 times (once for each distinct value from TEST_SAPFI_COICAR_AT5DAT11 but somehow only manage to total 8 rows returned - it's as if the Bloom filter which shouldn't be relevant is applied at line 8.

    As Solomon Yakobson says, if you disable Bloom filter pruning the problem goes away:

    <pre>
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                 | Name                       | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT          |                            |      1 |        |   165 (100)|       |       |      1 |00:00:00.03 |     154 |       |       |          |
    |   1 |  SORT AGGREGATE           |                            |      1 |      1 |            |       |       |      1 |00:00:00.03 |     154 |       |       |          |
    |   2 |   NESTED LOOPS            |                            |      1 |  15197 |   165   (9)|       |       |  15197 |00:00:00.03 |     154 |       |       |          |
    |   3 |    SORT UNIQUE            |                            |      1 |  15197 |     8  (13)|       |       |     60 |00:00:00.02 |      25 |  4096 |  4096 | 4096  (0)|
    |   4 |     PARTITION LIST SINGLE |                            |      1 |  15197 |     8  (13)|     2 |     2 |  15197 |00:00:00.01 |      25 |       |       |          |
    |   5 |      TABLE ACCESS FULL    | TEST_SAPFI_COICAR_AT5DAT11 |      1 |  15197 |     8  (13)|     2 |     2 |  15197 |00:00:00.01 |      25 |       |       |          |
    |   6 |    PARTITION LIST ITERATOR|                            |     60 |    253 |     3  (34)|   KEY |   KEY |  15197 |00:00:00.01 |     129 |       |       |          |
    |*  7 |     TABLE ACCESS FULL     | TEST_DWF_SAPFI             |     60 |    253 |     3  (34)|   KEY |   KEY |  15197 |00:00:00.01 |     129 |       |       |          |
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    
    </pre>
    
    

    As you can see in this case (I added the hint opt_param('_bloom_pruning_enabled' 'false') to the SQL) the tablescans at operation 7 total 15,197 rows which are echoed in the nested loop operation 2.

    Regards

    Jonathan Lewis

  • MajkiiT
    MajkiiT Member Posts: 10 Green Ribbon

    Thanks to all for responses!

    It looks like support accepted it as a bug after notice of not using a DBA user. With DBA user there is no problem.


    the wrong results only affect the 1st execution of the query; on the 2nd execution the cursor is reoptimized, yielding a new plan without the bug.

    Yes, I have noticed it. And slightly different results of repeated selects are there with OPTIMIZER_ADAPTIVE_STATISTICS parameter enabled, which we have.

    And the call to dbms_stats.gather_table_stats shown in the test, because of the method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO' parameter—but there's no index here!—only gathers partition statistics (numrows, blocks, avg_row_len, etc.), but does not gather column statistics at partition-level; why not use method_opt => 'FOR ALL COLUMNS SIZE 1'?

    There is no index here, because it is extracted from tables with more than 100 cols 🙂 But you're right, this paramerer for statistics was unintended error at our side, but because of it I have discovered the issue... Btw. I have first noticed it in much more complex query (not production query) with MINUS clause (and without hint), where the MINUS almost did not work because of this issue. And few hours of work was for me the simplifying of testcase from originally 4,5 millions rows to 15 thousand, so it did not exceeded the 10 MB limit at LiveSQL 😀

    Try disabling bloom filter prooning.

    Support has also recommended, did not help.

    Or try disabling bloom filters completely

    Just tested, did not help. 😶

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,682 Gold Crown

    Try disabling bloom filter prooning.

    Support has also recommended, did not help.

    Or try disabling bloom filters completely

    Just tested, did not help. 😶

    Can you post the plan you got when you had disabled Bloom filters and still got the wrong result.

    Regards

    Jonathan Lewis

  • MajkiiT
    MajkiiT Member Posts: 10 Green Ribbon

    Well, don't know why, but looks like that disabling "_bloom_filter_enabled" parameter does not have any impact on our machine in this case...

    If I disable "_bloom_pruning_enabled" parameter, then I get another plan (still with wrong results), but with bloom filtering disable I still got the plan with bloom filters...

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,682 Gold Crown


    I asked you to supply an exection plan, but you didn't. If you want further help please supply two execution plans - one with _bloom_filter_enabled set to false, one with _bloom_pruning_enabled set to false. Best method would be through the opt_param() hint mechanism I supplied.

    set linesize 255
    set pagesize 60
    set trimspool on
    set serveroutput off
    
    --- execute the query
    
    select * from table(dbms_xplan.display_cursor(format=>'cost outline allstats last partition hint_repo
    


    Whilst this may simply show that you're still getting the wrong results and the Bloom filters are irrelevant, it could well tell us what else is wrong; and if it's not the Bloom filtering we need the plan to get more clues.


    Regards

    Jonathan Lewis

  • MajkiiT
    MajkiiT Member Posts: 10 Green Ribbon

    alter session set "_bloom_filter_enabled"=FALSE;

    SQL_ID 11n5gd3ja2bhr, child number 9
    -------------------------------------
    select count(*) count_nl from coicar_odi.TEST_DWF_SAPFI where exists 
    (SELECT /*+ use_nl(test_sapfi_coicar_at5dat11) */ 1 FROM 
    coicar_odi.test_sapfi_coicar_at5dat11              WHERE DATUMZPRAC = 
    20200414              AND to_char(DATUCT,'YYYYMMDD') = 
    DATUMUCETNIPOM_CODE )
    
    Plan hash value: 4136990162
    
    --------------------------------------------------------------------------------------------------------------------------------------
    |  Id | Operation                   | Name                      | E-Rows | Cost (%CPU)| Pstart| Pstop | OMem | 1Mem | Used-Mem |
    --------------------------------------------------------------------------------------------------------------------------------------
    |    0 | SELECT STATEMENT            |                           |       |  227 (100)|      |      |      |      |         |
    |    1 | SORT AGGREGATE             |                           |     1 |           |      |      |      |      |         |
    |- * 2 |  HASH JOIN                 |                           | 38491 |  227  (1)|      |      | 3180K| 1019K|         |
    |    3 |   PART JOIN FILTER CREATE  | :BF0000                   | 38491 |  227  (1)|      |      |      |      |         |
    |    4 |    NESTED LOOPS            |                           | 38491 |  227  (1)|      |      |      |      |         |
    |-   5 |     STATISTICS COLLECTOR   |                           |       |           |      |      |      |      |         |
    |    6 |      SORT UNIQUE           |                           |   152 |    8  (0)|      |      | 4096 | 4096 | 4096 (0)|
    |    7 |       PARTITION LIST SINGLE |                           |   152 |    8  (0)|    2 |    2 |      |      |         |
    |    8 |        TABLE ACCESS FULL   | TEST_SAPFI_COICAR_AT5DAT11 |   152 |    8  (0)|    2 |    2 |      |      |         |
    |    9 |     PARTITION LIST ITERATOR |                           |   253 |  218  (0)|  KEY |  KEY |      |      |         |
    | * 10 |      TABLE ACCESS FULL     | TEST_DWF_SAPFI            |   253 |  218  (0)|  KEY |  KEY |      |      |         |
    |-  11 |   PARTITION LIST JOIN-FILTER|                           | 15197 |  218  (0)|:BF0000|:BF0000|      |      |         |
    |-  12 |    TABLE ACCESS FULL       | TEST_DWF_SAPFI            | 15197 |  218  (0)|:BF0000|:BF0000|      |      |         |
    --------------------------------------------------------------------------------------------------------------------------------------
    
    Outline Data
    -------------
    
     /*+
         BEGIN_OUTLINE_DATA
         FULL(@"SEL$5DA710D3" "TEST_DWF_SAPFI"@"SEL$1")
         USE_NL(@"SEL$5DA710D3" "TEST_DWF_SAPFI"@"SEL$1")
         IGNORE_OPTIM_EMBEDDED_HINTS
         OPTIMIZER_FEATURES_ENABLE('19.1.0')
         DB_VERSION('19.1.0')
         OPT_PARAM('_bloom_filter_enabled' 'false')
         ALL_ROWS
         OUTLINE_LEAF(@"SEL$5DA710D3")
         UNNEST(@"SEL$2")
         OUTLINE(@"SEL$1")
         OUTLINE(@"SEL$2")
         FULL(@"SEL$5DA710D3" "TEST_SAPFI_COICAR_AT5DAT11"@"SEL$2")
         LEADING(@"SEL$5DA710D3" "TEST_SAPFI_COICAR_AT5DAT11"@"SEL$2" "TEST_DWF_SAPFI"@"SEL$1")
         SEMI_TO_INNER(@"SEL$5DA710D3" "TEST_SAPFI_COICAR_AT5DAT11"@"SEL$2")
         END_OUTLINE_DATA
     */
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
      2 - access("DATUMUCETNIPOM_CODE"=TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("DATUCT"),'YYYYMMDD')))
     10 - filter("DATUMUCETNIPOM_CODE"=TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("DATUCT"),'YYYYMMDD')))
    
    Hint Report (identified by operation id / Query Block Name / Object Alias):
    Total hints for statement: 1 (U - Unused (1))
    ---------------------------------------------------------------------------
    
      8 - SEL$5DA710D3 / [email protected]$2
            U - use_nl(test_sapfi_coicar_at5dat11)
    
    Note
    -----
      - this is an adaptive plan (rows marked '-' are inactive)
      - Warning: basic plan statistics not available. These are only collected when:
          * hint 'gather_plan_statistics' is used for the statement or
          * parameter 'statistics_level' is set to 'ALL', at session or system level
    


    alter session set "_bloom_pruning_enabled"=FALSE;

    SQL_ID 11n5gd3ja2bhr, child number 9
    -------------------------------------
    select count(*) count_nl from coicar_odi.TEST_DWF_SAPFI where exists 
    (SELECT /*+ use_nl(test_sapfi_coicar_at5dat11) */ 1 FROM 
    coicar_odi.test_sapfi_coicar_at5dat11              WHERE DATUMZPRAC = 
    20200414              AND to_char(DATUCT,'YYYYMMDD') = 
    DATUMUCETNIPOM_CODE )
    
    Plan hash value: 3263470184
    
    ------------------------------------------------------------------------------------------------------------------------------------
    |  Id | Operation                 | Name                      | E-Rows | Cost (%CPU)| Pstart| Pstop | OMem | 1Mem | Used-Mem |
    ------------------------------------------------------------------------------------------------------------------------------------
    |    0 | SELECT STATEMENT          |                           |       |  227 (100)|      |      |      |      |         |
    |    1 | SORT AGGREGATE           |                           |     1 |           |      |      |      |      |         |
    |- * 2 |  HASH JOIN               |                           | 38491 |  227  (1)|      |      | 3180K| 1019K|         |
    |    3 |   NESTED LOOPS           |                           | 38491 |  227  (1)|      |      |      |      |         |
    |-   4 |    STATISTICS COLLECTOR  |                           |       |           |      |      |      |      |         |
    |    5 |     SORT UNIQUE          |                           |   152 |    8  (0)|      |      | 4096 | 4096 | 4096 (0)|
    |    6 |      PARTITION LIST SINGLE|                           |   152 |    8  (0)|    2 |    2 |      |      |         |
    |    7 |       TABLE ACCESS FULL  | TEST_SAPFI_COICAR_AT5DAT11 |   152 |    8  (0)|    2 |    2 |      |      |         |
    |    8 |    PARTITION LIST ITERATOR|                           |   253 |  218  (0)|  KEY |  KEY |      |      |         |
    | * 9 |     TABLE ACCESS FULL    | TEST_DWF_SAPFI            |   253 |  218  (0)|  KEY |  KEY |      |      |         |
    |-  10 |   PARTITION LIST ALL     |                           | 15197 |  218  (0)|    1 |   61 |      |      |         |
    |-  11 |    TABLE ACCESS FULL     | TEST_DWF_SAPFI            | 15197 |  218  (0)|    1 |   61 |      |      |         |
    ------------------------------------------------------------------------------------------------------------------------------------
    
    Outline Data
    -------------
    
     /*+
         BEGIN_OUTLINE_DATA
         FULL(@"SEL$5DA710D3" "TEST_DWF_SAPFI"@"SEL$1")
         USE_NL(@"SEL$5DA710D3" "TEST_DWF_SAPFI"@"SEL$1")
         IGNORE_OPTIM_EMBEDDED_HINTS
         OPTIMIZER_FEATURES_ENABLE('19.1.0')
         DB_VERSION('19.1.0')
         OPT_PARAM('_bloom_pruning_enabled' 'false')
         ALL_ROWS
         OUTLINE_LEAF(@"SEL$5DA710D3")
         UNNEST(@"SEL$2")
         OUTLINE(@"SEL$1")
         OUTLINE(@"SEL$2")
         FULL(@"SEL$5DA710D3" "TEST_SAPFI_COICAR_AT5DAT11"@"SEL$2")
         LEADING(@"SEL$5DA710D3" "TEST_SAPFI_COICAR_AT5DAT11"@"SEL$2" "TEST_DWF_SAPFI"@"SEL$1")
         SEMI_TO_INNER(@"SEL$5DA710D3" "TEST_SAPFI_COICAR_AT5DAT11"@"SEL$2")
         END_OUTLINE_DATA
     */
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
      2 - access("DATUMUCETNIPOM_CODE"=TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("DATUCT"),'YYYYMMDD')))
      9 - filter("DATUMUCETNIPOM_CODE"=TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("DATUCT"),'YYYYMMDD')))
    
    Hint Report (identified by operation id / Query Block Name / Object Alias):
    Total hints for statement: 1 (U - Unused (1))
    ---------------------------------------------------------------------------
    
      7 - SEL$5DA710D3 / [email protected]$2
            U - use_nl(test_sapfi_coicar_at5dat11)
    
    Note
    -----
      - this is an adaptive plan (rows marked '-' are inactive)
      - Warning: basic plan statistics not available. These are only collected when:
          * hint 'gather_plan_statistics' is used for the statement or
          * parameter 'statistics_level' is set to 'ALL', at session or system level
    
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,682 Gold Crown


    Thanks,

    There are a couple of things I want to play around with, but one detail to note is that the plans that produce the wrong result are not using your hint. Does this mean that at some point you were getting the result you wanted with that hint and something has now changed.

    If you want to make that hint work you will also need a leading() hint to tell Oracle about the correct join order, and then for each row in TEST_DWF_SAPFI you'd be doing scan of one partition in TEST_SAPFI_COICAR_AT5DAT11 is that what you actually want?

    Te hints to get this plan would be:

            /*+
                    leading(@sel$5da710d3 [email protected]$1 [email protected]$2)
                    use_nl (@sel$5da710d3 [email protected]$2)
            */
    

    This gave me the correct answer with the following plan:

    --------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation               | Name                       | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers |
    ---------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |                            |      1 |        |   178K(100)|       |       |      1 |00:00:00.14 |     692 |
    |   1 |  SORT AGGREGATE         |                            |      1 |      1 |            |       |       |      1 |00:00:00.14 |     692 |
    |   2 |   NESTED LOOPS SEMI     |                            |      1 |    253 |   178K  (4)|       |       |  15197 |00:00:00.14 |     692 |
    |   3 |    PARTITION LIST ALL   |                            |      1 |  15197 |   314   (1)|     1 |    61 |  15197 |00:00:00.01 |     129 |
    |   4 |     TABLE ACCESS FULL   | TEST_DWF_SAPFI             |     61 |  15197 |   314   (1)|     1 |    61 |  15197 |00:00:00.01 |     129 |
    |   5 |    PARTITION LIST SINGLE|                            |     60 |      3 |    12   (9)|     2 |     2 |     60 |00:00:00.13 |     563 |
    |*  6 |     TABLE ACCESS FULL   | TEST_SAPFI_COICAR_AT5DAT11 |     60 |      3 |    12   (9)|     2 |     2 |     60 |00:00:00.13 |     563 |
    ---------------------------------------------------------------------------------------------------------------------------------------------
    
    


    Note that the cost is HUGE because it's roughly 15197 * the cost of the single partition scan of the TEST_SAPFI_COICAR_AT5DAT11 table.

    This doesn't tell us anything about why the optimizer is introducing a bug, of course, or why the Bloom filter suggestion turned out to be irrelevant.

    Regards

    Jonathan Lewis

  • MajkiiT
    MajkiiT Member Posts: 10 Green Ribbon

    If you want to make that hint work you will also need a leading() hint to tell Oracle about the correct join order, and then for each row in TEST_DWF_SAPFI you'd be doing scan of one partition in TEST_SAPFI_COICAR_AT5DAT11 is that what you actually want?

    Originally I did not wanted at all to tell Oracle what plan to use. I have just noticed that the original select (which has the extracted part - which we are solving - in minus clause) producing wrong results shows the nested loops in explain plan, so the hint is just need how to keep wrong results also in this simplified query.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,682 Gold Crown


    The problem seems to be hiding somewhere between missing statistics and adaptive plans.

    If I gather stats on TEST_DWF_SAPFI I go straight to the hash join and get the right results.

    If I don't gather stats but put in explicit hints to get the path that (unhinted) produces the wrong results I get the right results (although I don't get the spurious Bloom filter operation):

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                 | Name                       | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT          |                            |      1 |        |  2454 (100)|       |       |      1 |00:00:00.01 |     154 |       |       |          |
    |   1 |  SORT AGGREGATE           |                            |      1 |      1 |            |       |       |      1 |00:00:00.01 |     154 |       |       |          |
    |   2 |   NESTED LOOPS            |                            |      1 |    120K|  2454   (1)|       |       |  15197 |00:00:00.01 |     154 |       |       |          |
    |   3 |    SORT UNIQUE            |                            |      1 |  15197 |    13   (0)|       |       |     60 |00:00:00.01 |      25 |  4096 |  4096 | 4096  (0)|
    |   4 |     PARTITION LIST SINGLE |                            |      1 |  15197 |    13   (0)|     2 |     2 |  15197 |00:00:00.01 |      25 |       |       |          |
    |   5 |      TABLE ACCESS FULL    | TEST_SAPFI_COICAR_AT5DAT11 |      1 |  15197 |    13   (0)|     2 |     2 |  15197 |00:00:00.01 |      25 |       |       |          |
    |   6 |    PARTITION LIST ITERATOR|                            |     60 |    253 |     5   (0)|   KEY |   KEY |  15197 |00:00:00.01 |     129 |       |       |          |
    |*  7 |     TABLE ACCESS FULL     | TEST_DWF_SAPFI             |     60 |    253 |     5   (0)|   KEY |   KEY |  15197 |00:00:00.01 |     129 |       |       |          |
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    
    


    There's an interesting detail in the number of rows returned if I don't gather stats and don't hint the query is that this nested loop (with the Bloom filter operation that should have been associated with the hash join that doesn't operate the first time) - see plan at this link further up the page: https://community.oracle.com/tech/developers/discussion/comment/16784120/#Comment_16784120

    At the scan of test_dwf_sapfi, and at the nested loop join the A-rows is 8; which is the number of rows in the last partition of test_dwf_sapfi (and you can check the match by changing the number of rows). Since the nested loop is driven by a set of values produced by a SORT UNIQUE that may explain why the only partition that seems to supply the data is the one matching the last value in the sort - and maybe the sort unique is supplying the wrong data to the nested loop join.

    The plan is quite sensitive to system stats, by the way. I had to have default values for system stats and the db_file_multiblock_read_count to ensure that Oracle didn't jump straight to the hash join path. This may help to explain why Oracle Support had a problem replicating the problem initlally.

    Regards

    Jonathan Lewis


    P.S. I'll probably write up a little note about this for my blog in a few days.

Sign In or Register to comment.