17 | 10 | 2017
Latest Articles
Popular Articles

SQL Tuning

Query Transformations : View Merging

User Rating:  / 2
PoorBest 

View Merging

 

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

View merging 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 complex view merging is and what the impact at explain plan level is.

Assume we setup the sample schema

 

When the aggregate view v_orderstat_os is not merged / expanded then the group by is at the level of the view ( which is not "expanded" )

When the aggregate view v_orderstat_os is merged / expanded into the main part of the query then the base table(s) is/are joined and the group by is at the level of the result set of that join


If we run the below SQL we notice that -- at least in our testcase --- complex view merging happens automatically

select /*+ GATHER_PLAN_STATISTICS */ t_supplier_su.su_name,
v_orderstat_os.os_avgamount OS_AVGAMOUNT, v_orderstat_os.os_minamount
OS_MINAMOUNT, v_orderstat_os.os_maxamount OS_MAXAMOUNT,
v_orderstat_os.cr_fk OS_CURRENCY from t_supplier_su left outer join
v_orderstat_os on t_supplier_su.su_pk = v_orderstat_os.su_fk order by 1,5


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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID    5wq9qvj68f383, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ t_supplier_su.su_name,
v_orderstat_os.os_avgamount OS_AVGAMOUNT, v_orderstat_os.os_minamount
OS_MINAMOUNT, v_orderstat_os.os_maxamount OS_MAXAMOUNT,
v_orderstat_os.cr_fk OS_CURRENCY from t_supplier_su left outer join
v_orderstat_os on t_supplier_su.su_pk = v_orderstat_os.su_fk order by
1,5

Plan hash value: 2106405536

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

| Id  | Operation           | Name          | Starts  | E-Rows   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |

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

|   0 | SELECT STATEMENT    |               |       1 |          |    397 |00:00:04.87 |    25162 |  25145 |       |       |          |

|   1 |  SORT GROUP BY      |               |       1 |      396 |    397 |00:00:04.87 |    25162 |  25145 |   106K|   106K|96256  (0)|

|*  2 |   HASH JOIN OUTER   |               |       1 |     1000K|   1000K|00:00:24.33 |    25162 |  25145 |   754K|   754K| 1244K (0)|

|   3 |    TABLE ACCESS FULL| T_SUPPLIER_SU |       1 |      100 |    100 |00:00:00.01 |        7 |      0 |       |       |          |

|   4 |    TABLE ACCESS FULL| T_ORDER_OR    |       1 |     1000K|   1000K|00:00:19.72 |    25155 |  25145 |       |       |          |

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


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

2 - access("T_SUPPLIER_SU"."SU_PK"="SU_FK")



If we run the below SQL we notice that we can avoid view merging with the NO_MERGE hint, we notice very clearly the view is not expanded.



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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID    5m9158kzx9w7d, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS NO_MERGE(v_orderstat_os) */
t_supplier_su.su_name, v_orderstat_os.os_avgamount OS_AVGAMOUNT,
v_orderstat_os.os_minamount OS_MINAMOUNT, v_orderstat_os.os_maxamount
OS_MAXAMOUNT, v_orderstat_os.cr_fk OS_CURRENCY from t_supplier_su left
outer join v_orderstat_os on t_supplier_su.su_pk = v_orderstat_os.su_fk
order by 1,5

Plan hash value: 2796673704

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

| Id  | Operation             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |

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

|   0 | SELECT STATEMENT      |                |      1 |        |    397 |00:00:04.57 |   25162 |  25145 |       |       |          |

|   1 |  SORT ORDER BY        |                |      1 |    281 |    397 |00:00:04.57 |   25162 |  25145 | 90112 | 90112 |79872  (0)|

|*  2 |   HASH JOIN OUTER     |                |      1 |    281 |    397 |00:00:04.57 |   25162 |  25145 |   763K|   763K| 1272K (0)|

|   3 |    TABLE ACCESS FULL  | T_SUPPLIER_SU  |      1 |    100 |    100 |00:00:00.01 |       7 |      0 |       |       |          |

|   4 |    VIEW               | V_ORDERSTAT_OS |      1 |    281 |    396 |00:00:04.57 |   25155 |  25145 |       |       |          |

|   5 |     HASH GROUP BY     |                |      1 |    281 |    396 |00:00:04.56 |   25155 |  25145 |   778K|   778K| 3035K (0)|

|   6 |      TABLE ACCESS FULL| T_ORDER_OR     |      1 |   1000K|   1000K|00:00:24.56 |   25155 |  25145 |       |       |          |

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


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

2 - access("T_SUPPLIER_SU"."SU_PK"="V_ORDERSTAT_OS"."SU_FK")



With the MERGE hint we will get the original plan ( ie the plan with view merging )


select /*+ GATHER_PLAN_STATISTICS MERGE(v_orderstat_os) */
t_supplier_su.su_name, v_orderstat_os.os_avgamount OS_AVGAMOUNT,
v_orderstat_os.os_minamount OS_MINAMOUNT, v_orderstat_os.os_maxamount
OS_MAXAMOUNT, v_orderstat_os.cr_fk OS_CURRENCY from t_supplier_su left
outer join v_orderstat_os on t_supplier_su.su_pk = v_orderstat_os.su_fk
order by 1,5;

 

Interesting feedback also if we use the ADVANCED attribute with dbms_xplan.display_cursor


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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID    g161ktmqum22v, child number 1
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ t_supplier_su.su_name,
v_orderstat_os.os_avgamount OS_AVGAMOUNT, v_orderstat_os.os_minamount
OS_MINAMOUNT, v_orderstat_os.os_maxamount OS_MAXAMOUNT,
v_orderstat_os.cr_fk OS_CURRENCY from t_supplier_su left outer join
v_orderstat_os on t_supplier_su.su_pk = v_orderstat_os.su_fk order by
1,5

Plan hash value: 2106405536

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

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

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

|   0 | SELECT STATEMENT    |               |        |        |    170K(100)|          |

|   1 |  SORT GROUP BY      |               |     397|  51213 |    170K  (1)|999:59:59 |

|*  2 |   HASH JOIN OUTER   |               |   1000K|    123M|    170K  (1)|999:59:59 |

|   3 |    TABLE ACCESS FULL| T_SUPPLIER_SU |     100|  11600 |     42   (0)| 00:27:23 |

|   4 |    TABLE ACCESS FULL| T_ORDER_OR    |   1000K|     12M|    170K  (0)|999:59:59 |

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


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

1 - SEL$2727354A
3 - SEL$2727354A / T_SUPPLIER_SU@SEL$3
4 - SEL$2727354A / T_ORDER_OR@SEL$2

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

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2727354A")
MERGE(@"SEL$2")
OUTLINE(@"SEL$CD8351FA")
MERGE(@"SEL$F1D6E378")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$F1D6E378")
MERGE(@"SEL$1")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$1")
FULL(@"SEL$2727354A" "T_SUPPLIER_SU"@"SEL$3")
FULL(@"SEL$2727354A" "T_ORDER_OR"@"SEL$2")
LEADING(@"SEL$2727354A" "T_SUPPLIER_SU"@"SEL$3" "T_ORDER_OR"@"SEL$2")
USE_HASH(@"SEL$2727354A" "T_ORDER_OR"@"SEL$2")
END_OUTLINE_DATA
*/

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

2 - access("T_SUPPLIER_SU"."SU_PK"="SU_FK")

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

1 - (#keys=4) "T_SUPPLIER_SU"."SU_NAME"[VARCHAR2,400],"CR_FK"[VARCHAR2,12], ROWID[ROWID,10], "SU_FK"[NUMBER,22],COUNT("OR_TOTALAMOUNT")[22], SUM("OR_TOTALAMOUNT")[22],MIN("OR_TOTALAMOUNT")[22], MAX("OR_TOTALAMOUNT")[22]
2 - (#keys=1) "SU_FK"[NUMBER,22], ROWID[ROWID,10],"T_SUPPLIER_SU"."SU_NAME"[VARCHAR2,400], "CR_FK"[VARCHAR2,12],"OR_TOTALAMOUNT"[NUMBER,22]
3 - ROWID[ROWID,10], "T_SUPPLIER_SU"."SU_PK"[NUMBER,22],"T_SUPPLIER_SU"."SU_NAME"[VARCHAR2,400]
4 - "SU_FK"[NUMBER,22], "OR_TOTALAMOUNT"[NUMBER,22], "CR_FK"[VARCHAR2,12]

Note
-----
- cardinality feedback used for this statement

Elapsed: 00:00:00.04

 

We can avoid that view merging AND OTHER QUERY TRANSFORMATIONS happen at statement level using the NO_QUERY_TRANSFORMATION hint

 

select /*+ GATHER_PLAN_STATISTICS NO_QUERY_TRANSFORMATION */ t_supplier_su.su_name,
v_orderstat_os.os_avgamount OS_AVGAMOUNT, v_orderstat_os.os_minamount
OS_MINAMOUNT, v_orderstat_os.os_maxamount OS_MAXAMOUNT,
v_orderstat_os.cr_fk OS_CURRENCY from t_supplier_su left outer join
v_orderstat_os on t_supplier_su.su_pk = v_orderstat_os.su_fk order by 1,5;