Disable rowsets enabled parameter in Oracle Database 12c
In Oracle 12.1.0.1 version introduced this feature of rowset with hash join.
When you execute the query and its make execution plan with hash join with rowset.The rowset value in that is coming wrong in that case which caused the performance issue in Oracle 12c environment.
Example show the use of rowsets enabled
You can analysed this with help of checking the execution plan for query in advanced mode.
Note: Bold line in column project shot that it used rowset enabled in Query execution plan,
select * from table(dbms_xplan.display_cursor('arserag4r5',format => 'advanced'))
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 14118 (100)| |
| 1 | NESTED LOOPS | | 29 | 2958 | 14118 (2)| 00:00:01 |
|* 2 | HASH JOIN | | 892 | 57088 | 35 (3)| 00:00:01 |
| 3 | TABLE ACCESS FULL| ABC | 549 | 21411 | 17 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| DEF | 892 | 22300 | 17 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | XYZ | 1 | 38 | 16 (0)| 00:00:01 |
----------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ABC"."ABC_ID"[NUMBER,22], "DEF"."ABC_FK"[NUMBER,22],
"ABC"."COL2"[NUMBER,22], "ABC"."COL1"[NUMBER,22],
"DEF"."COL0"[VARCHAR2,20], "XYZ"."COL1"[NUMBER,22],
"XYZ"."COL2"[NUMBER,22], "XYZ"."COL0"[VARCHAR2,20]
2 - (#keys=1) "ABC"."ABC_ID"[NUMBER,22], "DEF"."ABC_FK"[NUMBER,22],
"ABC"."COL2"[NUMBER,22], "ABC"."COL1"[NUMBER,22],
"DEF"."COL0"[VARCHAR2,20]
3 - (rowset=200) "ABC"."ABC_ID"[NUMBER,22], "ABC"."COL1"[NUMBER,22],
"ABC"."COL2"[NUMBER,22]
4 - (rowset=200) "DEF"."ABC_FK"[NUMBER,22], "DEF"."COL0"[VARCHAR2,20]
5 - "XYZ"."COL1"[NUMBER,22], "XYZ"."COL2"[NUMBER,22],
"XYZ"."COL0"[VARCHAR2,20]
Solution
In Oracle 12c , Please disable this parameter.
alter system set "_rowsets_enabled" = FALSE;
Note: its fixed in 12.1.0.2 version.
