Wrong Result Set: Oracle 12c _rowsets_enabled Optimization

Recently I faced an issue where Oracle fetched wrong number of rows and seems to be a potential problem. 

Issue: Wrong number of rows returned when array size is less; and continued till array size of 98/99 <in some cases not for below> from 100 onwards result seems fine; I believe this can be potential issue for any query though i am not sure if it is reproducible all the time.

  

Test Case:

Oracle Version: 12.1.0.1/2

  

Here is the test case: Created 3 tables.

create table test_rowset1(x number,y number);

create table test_rowset2(x number,y number);

create table test_rowset3(x number,y number);

Inserted 1000 rows in each and kept same values.

declare

begin

for i in 1..1000 loop

insert into test_rowset1 values (i,1+1);

insert into test_rowset2 values (i,1+1);

insert into test_rowset3 values (i,1+1);

end loop;

end;

/

commit;

Gathered stats:

exec DBMS_STATS.GATHER_TABLE_STATS ( ownname=>'SCHE', tabname=>'TEST_ROWSET1', METHOD_OPT =>'FOR ALL COLUMNS SIZE 1',  granularity=>'DEFAULT',cascade=>TRUE, degree=>8, estimate_percent => 10);

exec DBMS_STATS.GATHER_TABLE_STATS ( ownname=>'SCHE', tabname=>'TEST_ROWSET2', METHOD_OPT =>'FOR ALL COLUMNS SIZE 1',  granularity=>'DEFAULT',cascade=>TRUE, degree=>8, estimate_percent => 10);

exec DBMS_STATS.GATHER_TABLE_STATS ( ownname=>'SCHE', tabname=>'TEST_ROWSET3', METHOD_OPT =>'FOR ALL COLUMNS SIZE 1',  granularity=>'DEFAULT',cascade=>TRUE, degree=>8, estimate_percent => 10);

  

Here I set array size very minimal; default is 15

Executed below query and as per output it is expected to get only 1000 rows but here it was more.

set autotrace on;

set array 1;

select /*+ leading(a,b) use_nl(c) */ a.x,a.y from test_rowset1 a,test_rowset2 b,test_rowset3 c

where a.x=b.x

and b.x=c.x;

1001 rows selected.------------à wrong result as only 1000 rows were expected

Elapsed: 00:00:00.28

Execution Plan

----------------------------------------------------------

Plan hash value: 3288938947

------------------------------------------------------------------------------------

| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |              |    82 |  4264 |    20   (0)| 00:00:01 |

|   1 |  NESTED LOOPS       |              |    82 |  4264 |    20   (0)| 00:00:01 |

|*  2 |   HASH JOIN         |              |    82 |  3198 |     4   (0)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| TEST_ROWSET1 |    82 |  2132 |     2   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL| TEST_ROWSET2 |    82 |  1066 |     2   (0)| 00:00:01 |

|*  5 |   TABLE ACCESS FULL | TEST_ROWSET3 |     1 |    13 |     0   (0)| 00:00:01 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("A"."X"="B"."X")

   5 - filter("B"."X"="C"."X")

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       8013  consistent gets

          0  physical reads

          0  redo size

      36890  bytes sent via SQL*Net to client

       3888  bytes received via SQL*Net from client

        502  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       1001  rows processed-----Wrong no of rows processed

Column Projection Information (identified by operation id):

-----------------------------------------------------------

      1 - "A"."X"[NUMBER,22], "A"."Y"[NUMBER,22]

   2 - (#keys=1) "A"."X"[NUMBER,22], "B"."X"[NUMBER,22], "A"."Y"[NUMBER,22]

   3 - (rowset=200) "A"."X"[NUMBER,22], "A"."Y"[NUMBER,22]

   4 - (rowset=200) "B"."X"[NUMBER,22]

See above rowset optimization is used and this sounds new in 12 C;

  

Next I executed with array size as 5000 that is max and after setting this value query fetched correct no of rows.

Sql> Set array 5000

1000 rows selected.----Correct no rows returned.

Elapsed: 00:00:00.08

Execution Plan

----------------------------------------------------------

Plan hash value: 3288938947

------------------------------------------------------------------------------------

| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |              |    82 |  4264 |    20   (0)| 00:00:01 |

|   1 |  NESTED LOOPS       |              |    82 |  4264 |    20   (0)| 00:00:01 |

|*  2 |   HASH JOIN         |              |    82 |  3198 |     4   (0)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| TEST_ROWSET1 |    82 |  2132 |     2   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL| TEST_ROWSET2 |    82 |  1066 |     2   (0)| 00:00:01 |

|*  5 |   TABLE ACCESS FULL | TEST_ROWSET3 |     1 |    13 |     0   (0)| 00:00:01 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("A"."X"="B"."X")

   5 - filter("B"."X"="C"."X")

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       7016  consistent gets

          0  physical reads

          0  redo size

       9150  bytes sent via SQL*Net to client

        389  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       1000  rows processed------Correct.

This arraysize is quite important for queries especially the big one the extract one’s. Even JDBC module queries can get affected due to this.

Now in 12C there is a workaround to suppress the issue and is to set _rowsets_enabled=false which is TRUE by default

Seem an optimization step in 12C

NAME                 VALUE                DEFLT     TYPE

-------------------- -------------------- --------- --------------------

_rowsets_enabled     TRUE                 TRUE      boolean

Executed same query with array size 1 and set hidden parameter as false. 

Set array 1;

Alter session set "_rowsets_enabled"=false;

Session altered.

Execution Plan

----------------------------------------------------------

Plan hash value: 3288938947

------------------------------------------------------------------------------------

| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |              |    82 |  4264 |    20   (0)| 00:00:01 |

|   1 |  NESTED LOOPS       |              |    82 |  4264 |    20   (0)| 00:00:01 |

|*  2 |   HASH JOIN         |              |    82 |  3198 |     4   (0)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| TEST_ROWSET1 |    82 |  2132 |     2   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL| TEST_ROWSET2 |    82 |  1066 |     2   (0)| 00:00:01 |

|*  5 |   TABLE ACCESS FULL | TEST_ROWSET3 |     1 |    13 |     0   (0)| 00:00:01 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("A"."X"="B"."X")

   5 - filter("B"."X"="C"."X")

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       7148  consistent gets

          0  physical reads

          0  redo size

      12200  bytes sent via SQL*Net to client

        850  bytes received via SQL*Net from client

         68  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       1000  rows processed-----------à Correct no of rows fetched.

After suppressing rowset info; can see below no rowset info.

Column Projection Information (identified by operation id):

-----------------------------------------------------------

   1 - "A"."X"[NUMBER,22], "A"."Y"[NUMBER,22]

   2 - (#keys=1) "A"."X"[NUMBER,22], "B"."X"[NUMBER,22], "A"."Y"[NUMBER,22]

   3 - "A"."X"[NUMBER,22], "A"."Y"[NUMBER,22]

   4 - "B"."X"[NUMBER,22]

Hope you enjoyed this edition.

Enjoy Learning!!!!