Disable rowsets enabled parameter in Oracle Database 12c

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.