Wrong Result Set: Oracle 12c _rowsets_enabled Optimization

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

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

For first case I set array size very minimal that is 1; default is 15 and executed below query and as per output it is expected to get only 1000 rows but to surprise query fetched 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.------------> 1 row extra that is 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]

Just note above column projection you can see there rowset=200 optimization is used and this sounds new to me in Oracle 12C.

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

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 array size 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 which seems to be an optimization feature in 12C.

NAME                 VALUE                DEFLT     TYPE
-------------------- -------------------- --------- --------------------
_rowsets_enabled     TRUE                 TRUE      boolean

To see whether this new feature is causing this issue; here i executed same query with array size 1 and altered the hidden parameter to 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 paraneter; can see below no rowset produced.

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]

Enjoy Learning!!!