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!!!!
