19 | 08 | 2017
Latest Articles
Popular Articles

SQL Tuning

Query Transformations : Subquery unnesting

User Rating:  / 1
PoorBest 

Subquery unnesting

 

Subquery unnesting did not happen by default in Oracle 8i. In 8i the hidden parameter "_unnest_subquery" defaults to false whereas it defaults to true in 9i, 10G and 11G.

Subquery unnesting is part from query transformations. Query transformation was based on heuristic rules in 9i. From 10G R1 however by default query transformations are cost based since "_optimizer_cost_based_transformation" defaults to linear. ( there are bugs related to cost based query transformation causing huge performance degradation )

In the below example I try to show what subquery unnesting is and what the impact at explain plan level is.

Assume we setup the sample schema


If we run the below SQL we notice that -- at least in our testcase --- subquery unnesting happens automatically

 

select /*+ GATHER_PLAN_STATISTICS */ * from t_order_or or1 where
cr_fk = 'EUR' and or_totalamount =
(select max(or_totalamount) from t_order_or or2 where or2.cr_fk = or1.cr_fk );


SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID    dznh67wddvrdd, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from t_order_or or1 where cr_fk
= 'EUR' and or_totalamount = (select max(or_totalamount) from
t_order_or or2 where or2.cr_fk = or1.cr_fk )

Plan hash value: 2544547732

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

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

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

|   0 | SELECT STATEMENT     |            |       |       | 13816 (100)|          |

|*  1 |  HASH JOIN           |            |     1 |   263 | 13816   (1)| 00:02:46 |

|   2 |   VIEW               | VW_SQ_1    |     1 |    21 |  6907   (1)| 00:01:23 |

|   3 |    HASH GROUP BY     |            |     1 |    10 |  6907   (1)| 00:01:23 |

|*  4 |     TABLE ACCESS FULL| T_ORDER_OR |   251K|  2459K|  6907   (1)| 00:01:23 |

|*  5 |   TABLE ACCESS FULL  | T_ORDER_OR |   251K|    58M|  6907   (1)| 00:01:23 |

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


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$C772B8D1
2 - SEL$683B0107 / VW_SQ_1@SEL$7511BFD2
3 - SEL$683B0107
4 - SEL$683B0107 / OR2@SEL$2
5 - SEL$C772B8D1 / OR1@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$683B0107")
OUTLINE_LEAF(@"SEL$C772B8D1")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$7511BFD2")
OUTLINE(@"SEL$1")
NO_ACCESS(@"SEL$C772B8D1" "VW_SQ_1"@"SEL$7511BFD2")
FULL(@"SEL$C772B8D1" "OR1"@"SEL$1")
LEADING(@"SEL$C772B8D1" "VW_SQ_1"@"SEL$7511BFD2" "OR1"@"SEL$1")
USE_HASH(@"SEL$C772B8D1" "OR1"@"SEL$1")
FULL(@"SEL$683B0107" "OR2"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$683B0107")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OR_TOTALAMOUNT"="MAX(OR_TOTALAMOUNT)" AND
"ITEM_1"="OR1"."CR_FK")
4 - filter("OR2"."CR_FK"='EUR')
5 - filter("CR_FK"='EUR')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=2) "OR_TOTALAMOUNT"[NUMBER,22], "OR1"."CR_FK"[VARCHAR2,12],
"OR1"."OR_PK"[NUMBER,22], "OR1"."SU_FK"[NUMBER,22],
"OR1"."OR_ORDERDATE"[DATE,7], "OR1"."OR_DELIVERYDATE"[DATE,7],
"OR1"."OR_PAYMENTMODE"[NUMBER,22], "OR1"."OR_LONGCOMMENT"[LOB,4000],
"OR1"."OR_SHORTCOMMENT"[VARCHAR2,400]
2 - "MAX(OR_TOTALAMOUNT)"[NUMBER,22], "ITEM_1"[VARCHAR2,12]
3 - "OR2"."CR_FK"[VARCHAR2,12], MAX("OR_TOTALAMOUNT")[22]
4 - "OR_TOTALAMOUNT"[NUMBER,22], "OR2"."CR_FK"[VARCHAR2,12]
5 - "OR1"."OR_PK"[NUMBER,22], "OR1"."SU_FK"[NUMBER,22],
"OR1"."OR_ORDERDATE"[DATE,7], "OR1"."OR_DELIVERYDATE"[DATE,7],
"OR1"."OR_PAYMENTMODE"[NUMBER,22], "OR_TOTALAMOUNT"[NUMBER,22],
"OR1"."OR_SHORTCOMMENT"[VARCHAR2,400], "OR1"."OR_LONGCOMMENT"[LOB,4000],
"CR_FK"[VARCHAR2,12]


77 rows selected.

 

If we run the below SQL we notice that we can avoid subquery unnesting with the NO_UNNEST hint, we notice very clearly the subquery is not unnested.

 

select /*+ GATHER_PLAN_STATISTICS NO_UNNEST(@"SEL$2") */ * from t_order_or or1 where
cr_fk = 'EUR' and or_totalamount =
(select max(or_totalamount) from t_order_or or2 where or2.cr_fk = or1.cr_fk );


SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID    gwmy7qck18dyy, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS NO_UNNEST(@"SEL$2") */ * from
t_order_or or1 where cr_fk = 'EUR' and or_totalamount = (select
max(or_totalamount) from t_order_or or2 where or2.cr_fk = or1.cr_fk )

Plan hash value: 1300620506

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

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

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

|   0 | SELECT STATEMENT    |            |       |       | 13815 (100)|          |

|*  1 |  FILTER             |            |       |       |            |          |

|*  2 |   TABLE ACCESS FULL | T_ORDER_OR |   251K|    58M|  6908   (1)| 00:01:23 |

|   3 |   SORT AGGREGATE    |            |     1 |    10 |            |          |

|*  4 |    TABLE ACCESS FULL| T_ORDER_OR |   250K|  2441K|  6907   (1)| 00:01:23 |

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


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / OR1@SEL$1
3 - SEL$2
4 - SEL$2 / OR2@SEL$2

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "OR1"@"SEL$1")
FULL(@"SEL$2" "OR2"@"SEL$2")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OR_TOTALAMOUNT"=)
2 - filter("CR_FK"='EUR')
4 - filter("OR2"."CR_FK"=:B1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "OR1"."OR_PK"[NUMBER,22], "OR1"."SU_FK"[NUMBER,22],
"OR1"."OR_ORDERDATE"[DATE,7], "OR1"."OR_DELIVERYDATE"[DATE,7],
"OR1"."OR_PAYMENTMODE"[NUMBER,22], "OR_TOTALAMOUNT"[NUMBER,22],
"OR1"."OR_SHORTCOMMENT"[VARCHAR2,400], "OR1"."OR_LONGCOMMENT"[LOB,4000],
"CR_FK"[VARCHAR2,12]
2 - "OR1"."OR_PK"[NUMBER,22], "OR1"."SU_FK"[NUMBER,22],
"OR1"."OR_ORDERDATE"[DATE,7], "OR1"."OR_DELIVERYDATE"[DATE,7],
"OR1"."OR_PAYMENTMODE"[NUMBER,22], "OR_TOTALAMOUNT"[NUMBER,22],
"OR1"."OR_SHORTCOMMENT"[VARCHAR2,400], "OR1"."OR_LONGCOMMENT"[LOB,4000],
"CR_FK"[VARCHAR2,12]
3 - (#keys=0) MAX("OR_TOTALAMOUNT")[22]
4 - "OR_TOTALAMOUNT"[NUMBER,22]


65 rows selected.



We can avoid that subquery unnesting happens at session level using the underscore "_unnest_subquery" parameter


SQL> alter session set "_unnest_subquery"=false;

Session altered.

select /*+ GATHER_PLAN_STATISTICS */ * from t_order_or or1 where
cr_fk = 'EUR' and or_totalamount =
(select max(or_totalamount) from t_order_or or2 where or2.cr_fk = or1.cr_fk );

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID    dznh67wddvrdd, child number 1
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from t_order_or or1 where cr_fk
= 'EUR' and or_totalamount = (select max(or_totalamount) from
t_order_or or2 where or2.cr_fk = or1.cr_fk )

Plan hash value: 1300620506

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

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

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

|   0 | SELECT STATEMENT    |            |         |       | 13815 (100)|          |

|*  1 |  FILTER             |            |         |       |            |          |

|*  2 |   TABLE ACCESS FULL | T_ORDER_OR |     251K|    58M|  6908   (1)| 00:01:23 |

|   3 |   SORT AGGREGATE    |            |       1 |    10 |            |          |

|*  4 |    TABLE ACCESS FULL| T_ORDER_OR |     250K|  2441K|  6907   (1)| 00:01:23 |

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


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / OR1@SEL$1
3 - SEL$2
4 - SEL$2 / OR2@SEL$2

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
OPT_PARAM('_unnest_subquery' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "OR1"@"SEL$1")
FULL(@"SEL$2" "OR2"@"SEL$2")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OR_TOTALAMOUNT"=)
2 - filter("CR_FK"='EUR')
4 - filter("OR2"."CR_FK"=:B1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "OR1"."OR_PK"[NUMBER,22], "OR1"."SU_FK"[NUMBER,22],
"OR1"."OR_ORDERDATE"[DATE,7], "OR1"."OR_DELIVERYDATE"[DATE,7],
"OR1"."OR_PAYMENTMODE"[NUMBER,22], "OR_TOTALAMOUNT"[NUMBER,22],
"OR1"."OR_SHORTCOMMENT"[VARCHAR2,400], "OR1"."OR_LONGCOMMENT"[LOB,4000],
"CR_FK"[VARCHAR2,12]
2 - "OR1"."OR_PK"[NUMBER,22], "OR1"."SU_FK"[NUMBER,22],
"OR1"."OR_ORDERDATE"[DATE,7], "OR1"."OR_DELIVERYDATE"[DATE,7],
"OR1"."OR_PAYMENTMODE"[NUMBER,22], "OR_TOTALAMOUNT"[NUMBER,22],
"OR1"."OR_SHORTCOMMENT"[VARCHAR2,400], "OR1"."OR_LONGCOMMENT"[LOB,4000],
"CR_FK"[VARCHAR2,12]
3 - (#keys=0) MAX("OR_TOTALAMOUNT")[22]
4 - "OR_TOTALAMOUNT"[NUMBER,22]


66 rows selected.


We can avoid that subquery unnesting happens at statement level using the hint OPT_PARAM('_unnest_subquery','false')

select /*+ GATHER_PLAN_STATISTICS OPT_PARAM('_unnest_subquery','false') */ * from t_order_or or1 where
cr_fk = 'EUR' and or_totalamount =
(select max(or_totalamount) from t_order_or or2 where or2.cr_fk = or1.cr_fk );

We can avoid that subquery unnesting AND OTHER QUERY TRANSFORMATIONS happens at statement level using the NO_QUERY_TRANSFORMATION hint

select /*+ GATHER_PLAN_STATISTICS NO_QUERY_TRANSFORMATION */ * from t_order_or or1 where
cr_fk = 'EUR' and or_totalamount =
(select max(or_totalamount) from t_order_or or2 where or2.cr_fk = or1.cr_fk );