Optimizer Transformation: Join Predicate Pushdown
By Maria Colgan on Jan 03, 2011
Normally, a view cannot be joined with an index-based nested loop (i.e., index access) join, since a view, in contrast with a base table, does not have an index defined on it. A view can only be joined with other tables using three methods: hash, nested loop, and sort-merge joins.
Introduction
The join predicate pushdown (JPPD) transformation allows a view to be joined with index-based nested-loop join method, which may provide a more optimal alternative. In the join predicate pushdown transformation, the view remains a separate query block, but it contains the join predicate, which is pushed down from its containing query block into the view. The view thus becomes correlated and must be evaluated for each row of the outer query block. These pushed-down join predicates, once inside the view, open up new index access paths on the base tables inside the view; this allows the view to be joined with index-based nested-loop join method, thereby enabling the optimizer to select an efficient execution plan.The join predicate pushdown transformation is not always optimal. The join predicate pushed-down view becomes correlated and it must be evaluated for each outer row; if there is a large number of outer rows, the cost of evaluating the view multiple times may make the nested-loop join suboptimal, and therefore joining the view with hash or sort-merge join method may be more efficient.
The decision whether to push down join predicates into a view is determined by evaluating the costs of the outer query with and without the join predicate pushdown transformation under Oracle's cost-based query transformation framework. The join predicate pushdown transformation applies to both non-mergeable views and mergeable views and to pre-defined and inline views as well as to views generated internally by the optimizer during various transformations. The following shows the types of views on which join predicate pushdown is currently supported.
- UNION ALL/UNION view
- Outer-joined view
- Anti-joined view
- Semi-joined view
- DISTINCT view
- GROUP-BY view
Examples
Consider query A, which has an outer-joined view V. The view cannot be merged, as it contains two tables, and the join between these two tables must be performed before the join between the view and the outer table T4.A:
SELECT T4.unique1, V.unique3
FROM T_4K T4,
(SELECT T10.unique3, T10.hundred, T10.ten
FROM T_5K T5, T_10K T10
WHERE T5.unique3 = T10.unique3) V
WHERE T4.unique3 = V.hundred(+)
AND T4.ten = V.ten(+)
AND T4.thousand = 5;
The following shows the non-default plan for query A generated by disabling join predicate pushdown.
When query A undergoes join predicate pushdown, it yields query B. Note that query B is expressed in a non-standard SQL and shows an internal representation of the query.
B:
SELECT T4.unique1, V.unique3
FROM T_4K T4,
(SELECT T10.unique3, T10.hundred, T10.ten
FROM T_5K T5, T_10K T10
WHERE T5.unique3 = T10.unique3
AND T4.unique3 = V.hundred(+)
AND T4.ten = V.ten(+)) V
WHERE T4.thousand = 5;
The execution plan for query B is shown below.
In the execution plan BX, note the keyword 'VIEW PUSHED PREDICATE' indicates that the view has undergone the join predicate pushdown transformation. The join predicates (shown here in red) have been moved into the view V; these join predicates open up index access paths thereby enabling index-based nested-loop join of the view. With join predicate pushdown, the cost of query A has come down from 62 to 32.
As mentioned earlier, the join predicate pushdown transformation is cost-based, and a join predicate pushed-down plan is selected only when it reduces the overall cost.
Consider another example of a query C, which contains a view with the UNION ALL set operator.
C:
SELECT R.unique1, V.unique3
FROM T_5K R,
(SELECT T1.unique3, T2.unique1+T1.unique1
FROM T_5K T1, T_10K T2
WHERE T1.unique1 = T2.unique1
UNION ALL
SELECT T1.unique3, T2.unique2
FROM G_4K T1, T_10K T2
WHERE T1.unique1 = T2.unique1) V
WHERE R.unique3 = V.unique3 and R.thousand < 1;
The execution plan of query C is shown below.
In the above, 'VIEW UNION ALL PUSHED PREDICATE' indicates that the UNION ALL view has undergone the join predicate pushdown transformation. As can be seen, here the join predicate has been replicated and pushed inside every branch of the UNION ALL view. The join predicates (shown here in red) open up index access paths thereby enabling index-based nested loop join of the view.
Consider query D as an example of join predicate pushdown into a distinct view. We have the following cardinalities of the tables involved in query D: Sales (1,016,271), Customers (50,000), and Costs (787,766).
D:
SELECT C.cust_last_name, C.cust_city
FROM customers C,
(SELECT DISTINCT S.cust_id
FROM sales S, costs CT
WHERE S.prod_id = CT.prod_id and CT.unit_price > 70) V
WHERE C.cust_state_province = 'CA' and C.cust_id = V.cust_id;
The execution plan of query D is shown below.
As shown in XD, when query D undergoes join predicate pushdown transformation, the expensive DISTINCT operator is removed and the join is converted into a semi-join; this is possible, since all the SELECT list items of the view participate in an equi-join with the outer tables. Under similar conditions, when a group-by view undergoes join predicate pushdown transformation, the expensive group-by operator can also be removed.
With the join predicate pushdown transformation, the elapsed time of query D came down from 63 seconds to 5 seconds.
Since distinct and group-by views are mergeable views, the cost-based transformation framework also compares the cost of merging the view with that of join predicate pushdown in selecting the most optimal execution plan.
Summary
We have tried to illustrate the basic ideas behind join predicate pushdown on different types of views by showing example queries that are quite simple. Oracle can handle far more complex queries and other types of views not shown here in the examples. Again many thanks to Rafi Ahmed for the content of this blog post.
=================================================================================================================================================
Predicate Pushing ( into views )
Predicate pushing did not happen by default in Oracle 8i. In 8i the hidden parameter "_push_join_predicate" defaults to false whereas it defaults to true in 9i, 10G and 11G.
Predicate pushing 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 predicate pushing 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 --- Predicate pushing did happens automatically, however we can hint it with the PUSH_PRED hint
select /*+ GATHER_PLAN_STATISTICS PUSH_PRED(inner_view) */ su_pk,su_name,su_comment,inner_view.maxamount from
t_supplier_su,
( select max(or_totalamount) maxamount,su_fk from t_order_or group by su_fk ) inner_view
where t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_name is not null;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID gxxa8g94dzk7n, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS PUSH_PRED(inner_view) */
su_pk,su_name,su_comment,inner_view.maxamount from t_supplier_su, (
select max(or_totalamount) maxamount,su_fk from t_order_or group by
su_fk ) inner_view where t_supplier_su.su_pk = inner_view.su_fk(+) and
t_supplier_su.su_name is not null
Plan hash value: 3140464201
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 690K(100)| |
| 1 | NESTED LOOPS OUTER | | 100 | 21800 | 690K (1)| 02:18:01 |
|* 2 | TABLE ACCESS FULL | T_SUPPLIER_SU | 100 | 20300 | 3 (0)| 00:00:01 |
| 3 | VIEW PUSHED PREDICATE | | 1 | 15 | 6900 (1)| 00:01:23 |
| 4 | SORT GROUP BY | | 1 | 9 | 6900 (1)| 00:01:23 |
|* 5 | TABLE ACCESS FULL | T_ORDER_OR | 10101 | 90909 | 6900 (1)| 00:01:23 |
-------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T_SUPPLIER_SU@SEL$1
3 - SEL$639F1A6F / INNER_VIEW@SEL$1
4 - SEL$639F1A6F
5 - SEL$639F1A6F / T_ORDER_OR@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$639F1A6F")
PUSH_PRED(@"SEL$1" "INNER_VIEW"@"SEL$1" 1)
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
FULL(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1")
NO_ACCESS(@"SEL$1" "INNER_VIEW"@"SEL$1")
LEADING(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" "INNER_VIEW"@"SEL$1")
USE_NL(@"SEL$1" "INNER_VIEW"@"SEL$1")
FULL(@"SEL$639F1A6F" "T_ORDER_OR"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$639F1A6F")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T_SUPPLIER_SU"."SU_NAME" IS NOT NULL)
5 - filter("SU_FK"="T_SUPPLIER_SU"."SU_PK")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T_SUPPLIER_SU"."SU_PK"[NUMBER,22],
"T_SUPPLIER_SU"."SU_NAME"[VARCHAR2,400], "SU_COMMENT"[VARCHAR2,400],
"INNER_VIEW"."MAXAMOUNT"[NUMBER,22]
2 - "T_SUPPLIER_SU"."SU_PK"[NUMBER,22],
"T_SUPPLIER_SU"."SU_NAME"[VARCHAR2,400], "SU_COMMENT"[VARCHAR2,400]
3 - "INNER_VIEW"."MAXAMOUNT"[NUMBER,22]
4 - (#keys=1) "SU_FK"[NUMBER,22], MAX("OR_TOTALAMOUNT")[22]
5 - "SU_FK"[NUMBER,22], "OR_TOTALAMOUNT"[NUMBER,22]
71 rows selected.
If we run the below SQL we notice that we can avoid predicate pushing (at any cost) with the NO_PUSH_PRED hint.
select /*+ GATHER_PLAN_STATISTICS NO_PUSH_PRED(inner_view) */ su_pk,su_name,su_comment,inner_view.currency,inner_view.maxamount from
t_supplier_su,
( select max(or_totalamount) maxamount,su_fk,cr_fk currency from t_order_or group by su_fk,cr_fk ) inner_view
where t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_name is not null;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0gbc9khzuznzk, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS NO_PUSH_PRED(inner_view) */
su_pk,su_name,su_comment,inner_view.currency,inner_view.maxamount from
t_supplier_su, ( select max(or_totalamount) maxamount,su_fk,cr_fk
currency from t_order_or group by su_fk,cr_fk ) inner_view where
t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_name is
not null
Plan hash value: 83112093
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6954 (100)| |
|* 1 | HASH JOIN RIGHT OUTER| | 281 | 66597 | 6954 (2)| 00:01:24 |
| 2 | VIEW | | 281 | 9554 | 6950 (2)| 00:01:24 |
| 3 | HASH GROUP BY | | 281 | 3653 | 6950 (2)| 00:01:24 |
| 4 | TABLE ACCESS FULL | T_ORDER_OR | 1000K| 12M| 6905 (1)| 00:01:23 |
|* 5 | TABLE ACCESS FULL | T_SUPPLIER_SU | 100 | 20300 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / INNER_VIEW@SEL$1
3 - SEL$2
4 - SEL$2 / T_ORDER_OR@SEL$2
5 - SEL$1 / T_SUPPLIER_SU@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$2")
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1")
NO_ACCESS(@"SEL$1" "INNER_VIEW"@"SEL$1")
LEADING(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" "INNER_VIEW"@"SEL$1")
USE_HASH(@"SEL$1" "INNER_VIEW"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$1" "INNER_VIEW"@"SEL$1")
FULL(@"SEL$2" "T_ORDER_OR"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T_SUPPLIER_SU"."SU_PK"="INNER_VIEW"."SU_FK")
5 - filter("T_SUPPLIER_SU"."SU_NAME" IS NOT NULL)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "T_SUPPLIER_SU"."SU_PK"[NUMBER,22],
"INNER_VIEW"."MAXAMOUNT"[NUMBER,22], "INNER_VIEW"."CURRENCY"[VARCHAR2,12],
"SU_COMMENT"[VARCHAR2,400], "T_SUPPLIER_SU"."SU_NAME"[VARCHAR2,400]
2 - "INNER_VIEW"."MAXAMOUNT"[NUMBER,22], "INNER_VIEW"."SU_FK"[NUMBER,22],
"INNER_VIEW"."CURRENCY"[VARCHAR2,12]
3 - "SU_FK"[NUMBER,22], "CR_FK"[VARCHAR2,12], MAX("OR_TOTALAMOUNT")[22]
4 - "SU_FK"[NUMBER,22], "OR_TOTALAMOUNT"[NUMBER,22], "CR_FK"[VARCHAR2,12]
5 - "T_SUPPLIER_SU"."SU_PK"[NUMBER,22],
"T_SUPPLIER_SU"."SU_NAME"[VARCHAR2,400], "SU_COMMENT"[VARCHAR2,400]
71 rows selected.
Predicate pushing becomes more interesting when the inner view cannot be merged because there is an outer join. If the filtering predicate is selectif predicate pushing can then "push this filter down" into the inner view.
select /*+ GATHER_PLAN_STATISTICS PUSH_PRED(inner_view) */ su_pk,su_name,su_comment,inner_view.currency,inner_view.maxamount from
t_supplier_su,
( select max(or_totalamount) maxamount,su_fk,cr_fk currency from t_order_or group by su_fk,cr_fk ) inner_view
where t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_pk= 1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID bw56zj2jw7vng, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS PUSH_PRED(inner_view) */
su_pk,su_name,su_comment,inner_view.currency,inner_view.maxamount from
t_supplier_su, ( select max(or_totalamount) maxamount,su_fk,cr_fk
currency from t_order_or group by su_fk,cr_fk ) inner_view where
t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_pk= 1
Plan hash value: 484793169
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6902 (100)| |
| 1 | NESTED LOOPS OUTER | | 3 | 678 | 6902 (1)| 00:01:23 |
| 2 | TABLE ACCESS BY INDEX ROWID | T_SUPPLIER_SU | 1 | 203 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | SU_PK | 1 | | 0 (0)| |
| 4 | VIEW PUSHED PREDICATE | | 3 | 69 | 6901 (1)| 00:01:23 |
| 5 | SORT GROUP BY | | 3 | 39 | 6901 (1)| 00:01:23 |
|* 6 | FILTER | | | | | |
|* 7 | TABLE ACCESS FULL | T_ORDER_OR | 11636 | 147K| 6900 (1)| 00:01:23 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T_SUPPLIER_SU@SEL$1
3 - SEL$1 / T_SUPPLIER_SU@SEL$1
4 - SEL$639F1A6F / INNER_VIEW@SEL$1
5 - SEL$639F1A6F
7 - SEL$639F1A6F / T_ORDER_OR@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$639F1A6F")
PUSH_PRED(@"SEL$1" "INNER_VIEW"@"SEL$1" 1)
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" ("T_SUPPLIER_SU"."SU_PK"))
NO_ACCESS(@"SEL$1" "INNER_VIEW"@"SEL$1")
LEADING(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" "INNER_VIEW"@"SEL$1")
USE_NL(@"SEL$1" "INNER_VIEW"@"SEL$1")
FULL(@"SEL$639F1A6F" "T_ORDER_OR"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$639F1A6F")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T_SUPPLIER_SU"."SU_PK"=1)
6 - filter(1="T_SUPPLIER_SU"."SU_PK")
7 - filter("SU_FK"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T_SUPPLIER_SU"."SU_PK"[NUMBER,22], "SU_NAME"[VARCHAR2,400],
"SU_COMMENT"[VARCHAR2,400], "INNER_VIEW"."MAXAMOUNT"[NUMBER,22],
"INNER_VIEW"."CURRENCY"[VARCHAR2,12]
2 - "T_SUPPLIER_SU"."SU_PK"[NUMBER,22], "SU_NAME"[VARCHAR2,400],
"SU_COMMENT"[VARCHAR2,400]
3 - "T_SUPPLIER_SU".ROWID[ROWID,10], "T_SUPPLIER_SU"."SU_PK"[NUMBER,22]
4 - "INNER_VIEW"."MAXAMOUNT"[NUMBER,22], "INNER_VIEW"."CURRENCY"[VARCHAR2,12]
5 - (#keys=2) "SU_FK"[NUMBER,22], "CR_FK"[VARCHAR2,12], MAX("OR_TOTALAMOUNT")[22]
6 - "SU_FK"[NUMBER,22], "OR_TOTALAMOUNT"[NUMBER,22], "CR_FK"[VARCHAR2,12]
7 - "SU_FK"[NUMBER,22], "OR_TOTALAMOUNT"[NUMBER,22], "CR_FK"[VARCHAR2,12]
77 rows selected.
Another example of interesting predicate pushing is the example below. For the supplier 100 there are no orders (read no records in the inner view).
We need however an entry in the result set for supplier 100, thus we need an outer join.
The filtering predicate results in the usage of the index OR_IDX1, we only need (selectif) records for 1 supplier. ( supplier 1 )
select /*+ GATHER_PLAN_STATISTICS NO_MERGE(inner_view) PUSH_PRED(inner_view) */ su_pk,su_name,su_comment,inner_view.currency,inner_view.maxamount from
t_supplier_su,
( select max(or_totalamount) maxamount,su_fk,cr_fk currency from t_order_or group by su_fk,cr_fk ) inner_view
where t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_pk in (1,100);
SU_PK
----------
SU_NAME
--------------------------------------------------------------------------------
SU_COMMENT
--------------------------------------------------------------------------------
CURRENCY MAXAMOUNT
------------ ----------
1
ZAQMGJLUHAZPEVKNGPXEWNYVKUPXMDENWSGCWAFRBRJMNABHOLVHESPKVFKTMQIESGRBVPFIHHRKOZCZ
FKEQVUXRYRVJQNVUYBOJ
QGPVSNRSRSJBQPKIKFVRXMKMDBCFIFTEGRVYXWJNMHSPGFSATRLVOYVTISBBSLXYJEAOMTWWNNGDSCMW
SOBWEOYOKXAQHHMWMSDS
EUR 99985.21
1
ZAQMGJLUHAZPEVKNGPXEWNYVKUPXMDENWSGCWAFRBRJMNABHOLVHESPKVFKTMQIESGRBVPFIHHRKOZCZ
FKEQVUXRYRVJQNVUYBOJ
QGPVSNRSRSJBQPKIKFVRXMKMDBCFIFTEGRVYXWJNMHSPGFSATRLVOYVTISBBSLXYJEAOMTWWNNGDSCMW
SOBWEOYOKXAQHHMWMSDS
GBP 99967.1
1
ZAQMGJLUHAZPEVKNGPXEWNYVKUPXMDENWSGCWAFRBRJMNABHOLVHESPKVFKTMQIESGRBVPFIHHRKOZCZ
FKEQVUXRYRVJQNVUYBOJ
QGPVSNRSRSJBQPKIKFVRXMKMDBCFIFTEGRVYXWJNMHSPGFSATRLVOYVTISBBSLXYJEAOMTWWNNGDSCMW
SOBWEOYOKXAQHHMWMSDS
USD 99993.94
1
ZAQMGJLUHAZPEVKNGPXEWNYVKUPXMDENWSGCWAFRBRJMNABHOLVHESPKVFKTMQIESGRBVPFIHHRKOZCZ
FKEQVUXRYRVJQNVUYBOJ
QGPVSNRSRSJBQPKIKFVRXMKMDBCFIFTEGRVYXWJNMHSPGFSATRLVOYVTISBBSLXYJEAOMTWWNNGDSCMW
SOBWEOYOKXAQHHMWMSDS
YEN 99972.73
100
We love SQL tuning
We love SQL tuning
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 997w5gw1rvgdu, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS NO_MERGE(inner_view)
PUSH_PRED(inner_view) */ su_pk,su_name,su_comment,inner_view.currency,in
ner_view.maxamount from t_supplier_su, ( select max(or_totalamount)
maxamount,su_fk,cr_fk currency from t_order_or group by su_fk,cr_fk )
inner_view where t_supplier_su.su_pk = inner_view.su_fk(+) and
t_supplier_su.su_pk in (1,100)
Plan hash value: 331405424
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 299(100)| |
| 1 | NESTED LOOPS OUTER | | 2 | 452 | 299 (1)| 00:00:04 |
| 2 | INLIST ITERATOR | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | T_SUPPLIER_SU | 2 | 406 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SU_PK | 2 | | 1 (0)| 00:00:01 |
| 5 | VIEW PUSHED PREDICATE | | 1 | 23 | 148 (1)| 00:00:02 |
| 6 | SORT GROUP BY | | 6 | 78 | 148 (1)| 00:00:02 |
|* 7 | FILTER | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | T_ORDER_OR | 151 | 1963 | 147 (0)| 00:00:02 |
|* 9 | INDEX RANGE SCAN | OR_IDX1 | 151 | | 22 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T_SUPPLIER_SU@SEL$1
4 - SEL$1 / T_SUPPLIER_SU@SEL$1
5 - SEL$639F1A6F / INNER_VIEW@SEL$1
6 - SEL$639F1A6F
8 - SEL$639F1A6F / T_ORDER_OR@SEL$2
9 - SEL$639F1A6F / T_ORDER_OR@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$639F1A6F")
PUSH_PRED(@"SEL$1" "INNER_VIEW"@"SEL$1" 1)
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" ("T_SUPPLIER_SU"."SU_PK"))
NO_ACCESS(@"SEL$1" "INNER_VIEW"@"SEL$1")
LEADING(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" "INNER_VIEW"@"SEL$1")
USE_NL(@"SEL$1" "INNER_VIEW"@"SEL$1")
INDEX_RS_ASC(@"SEL$639F1A6F" "T_ORDER_OR"@"SEL$2" ("T_ORDER_OR"."SU_FK"))
USE_HASH_AGGREGATION(@"SEL$639F1A6F")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(("T_SUPPLIER_SU"."SU_PK"=1 OR "T_SUPPLIER_SU"."SU_PK"=100))
7 - filter((1="T_SUPPLIER_SU"."SU_PK" OR 100="T_SUPPLIER_SU"."SU_PK"))
9 - access("SU_FK"="T_SUPPLIER_SU"."SU_PK")
filter(("SU_FK"=1 OR "SU_FK"=100))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T_SUPPLIER_SU"."SU_PK"[NUMBER,22], "SU_NAME"[VARCHAR2,400],
"SU_COMMENT"[VARCHAR2,400], "INNER_VIEW"."MAXAMOUNT"[NUMBER,22],
"INNER_VIEW"."CURRENCY"[VARCHAR2,12]
2 - "T_SUPPLIER_SU"."SU_PK"[NUMBER,22], "SU_NAME"[VARCHAR2,400],
"SU_COMMENT"[VARCHAR2,400]
3 - "T_SUPPLIER_SU"."SU_PK"[NUMBER,22], "SU_NAME"[VARCHAR2,400],
"SU_COMMENT"[VARCHAR2,400]
4 - "T_SUPPLIER_SU".ROWID[ROWID,10], "T_SUPPLIER_SU"."SU_PK"[NUMBER,22]
5 - "INNER_VIEW"."MAXAMOUNT"[NUMBER,22], "INNER_VIEW"."CURRENCY"[VARCHAR2,12]
6 - (#keys=2) "SU_FK"[NUMBER,22], "CR_FK"[VARCHAR2,12], MAX("OR_TOTALAMOUNT")[22]
7 - "SU_FK"[NUMBER,22], "OR_TOTALAMOUNT"[NUMBER,22], "CR_FK"[VARCHAR2,12]
8 - "SU_FK"[NUMBER,22], "OR_TOTALAMOUNT"[NUMBER,22], "CR_FK"[VARCHAR2,12]
9 - "T_ORDER_OR".ROWID[ROWID,10], "SU_FK"[NUMBER,22]
85 rows selected.
Remember we can avoid that any query transformation ahppens with the NO_QUERY_TRANSFORMATION hint.
SQL> select /*+ GATHER_PLAN_STATISTICS NO_QUERY_TRANSFORMATION */ su_pk,su_name,su_comment,inner_view.currency,
2 inner_view.maxamount from t_supplier_su, ( select max(or_totalamount)
maxamount,su_fk,cr_fk currency from t_order_or group by su_fk,cr_fk )
inner_view where t_supplier_su.su_pk = inner_view.su_fk(+) and
t_supplier_su.su_pk in (1,100) ;
==================================================================================================================================================
调优案例
- 早上某数据库服务器CPU不断报警，应用系统管理员同时反馈应用响应明显变慢。登陆数据库主机查看，
- 应用连接上来的几个进程占用了大量的CPU资源，造成CPU空闲率很低。登陆数据库查询，发现有不少buffer cache chains的等待，
- 初步判断是应用上出现了某些性能糟糕的SQL语句。
- 通过进程捕获了几条耗资源的SQL语句，发现大部分都是类似同一条语句造成的。手工执行一下，需要2分多钟才能出结果。
- 捕获到的SQL语句如下：
- SELECT *
- FROM (SELECT DISTINCT e.n_event_id,
- e.n_parent_id,
- e.v_event_num,
- em.n_req_type_1,
- em.n_req_type_2,
- em.v_title,
- em.v_description,
- e.n_priority,
- cb.n_time_limit,
- cb.n_status,
- e.n_process_way,
- e.n_maintaince_event_id,
- e.v_maintaince_flag,
- e.v_replacedevice_flag,
- et.d_acbept_date,
- et.d_finish_date,
- et.v_exempt_status,
- et.n_callback_status,
- et.n_delay_time,
- erpl.n_creator,
- erpl.d_creation_date,
- e.n_req_id,
- el.v_res_notice_msg,
- el.v_res_notice_email,
- el.v_res_notice_im,
- vd.v_valid_status,
- vd.v_related_org_id,
- e.n_dev_process_way,
- e.v_over_time_reason,
- e.v_confirm_person,
- e.v_new_device_num
- FROM tb_event e,
- tb_event_related_person erpl,
- vorg_department vd,
- tb_callback cb,
- tb_event_log el,
- tb_event_marker em,
- tb_event_track et
- WHERE e.n_event_id = et.n_event_id(+)
- AND e.n_event_id = em.n_event_id(+)
- AND e.n_event_id = el.n_event_id(+)
- AND e.n_event_id = cb.n_event_id(+)
- AND erpl.n_dept_id = vd.recid(+)
- AND e.n_event_id = erpl.n_event_id(+)
- ORDER BY e.d_creation_date DESC)
- WHERE rownum <= 40;
- vorg_department为一个view，具体定义如下：
- create or replace view vorg_department as
- select d.recid,
- r.v_valid_status,
- d.v_related_org_id,
- r.org_parent,
- r.tree_no,
- d.dept_kind,
- d.dept_type,
- d.dept_name,
- d.status,
- d.dept_code,
- d.area_code,
- d.dept_prof,
- d.sort_num,
- d.link_addr,
- r.layer
- from ORG_DEPARTMENT d, ORG_DEPRELATION r
- where d.recid = r.org_child
- and r.relation_type = 1
- 该语句的执行计划如下：
- PLAN_TABLE_OUTPUT
- ———————————————————————————————————–
- | Id | Operation | Name | Rows | Bytes|TempSpc|Cost|
- ———————————————————————————————————–
- | 0 | SELECT STATEMENT | | 40| 15404| | 23M|
- |* 1 | COUNT STOPKEY | | | | | |
- | 2 | VIEW | | 110M| 395G| | 23M|
- |* 3 | SORT UNIQUE STOPKEY | | 110M| 27G| 58G| 11M|
- | 4 | NESTED LOOPS OUTER | | 110M| 27G| | 2797|
- |* 5 | HASH JOIN OUTER | | 1255K| 308M| 39M| 2797|
- |* 6 | HASH JOIN OUTER | | 153K| 37M| 28M| 1873|
- |* 7 | HASH JOIN OUTER | | 115K| 27M| 24M| 1384|
- |* 8 | HASH JOIN OUTER | | 101K| 23M| 23M| 990|
- |* 9 | HASH JOIN OUTER | | 100K| 22M| 17M| 571|
- | 10 | TABLE ACCESS FULL | TB_EVENT | 77044| 16M| | 256|
- | 11 | INDEX FAST FULL SCAN| IDX_EVENT_TRACK_N_E_ID| 100K| 491K| | 4|
- | 12 | INDEX FAST FULL SCAN |IDX_TB_CALLBAK_E_ID | 75959| 296K| | 21|
- | 13 | TABLE ACCESS FULL | TB_EVENT_MARKER | 3686| 18430| | 3|
- |* 14 | INDEX FAST FULL SCAN | IDX_TB_EVENT_RP_DUP1 | 101K| 895K| | 4|
- | 15 | INDEX FAST FULL SCAN |IDX_TB_EVENT_LOG_N_E_ID| 628K| 2455K| | 149|
- | 16 | VIEW PUSHED PREDICATE | VORG_DEPARTMENT | 88| 1144 | | |
- | 17 | NESTED LOOPS | | 1 | 15 | | 2|
- |* 18 | INDEX UNIQUE SCAN | PK_DEPARTMENT | 1 | 6| | 1|
- |* 19 | INDEX RANGE SCAN | ASSOCIATION8_FK | 1 | 9| | 1|
- ———————————————————————————————————–
- 首先了解了一下各表的统计信息情况和表的数据情况，统计信息都是最新的，除了tb_event_log表略微大点，有60多万数据外，其余表均10万左右或更小，
- 执行计划里面的小表table full sacn和一些索引的access情况也并无严重问题。接下来关注表与表之间的关联顺序和方式。
- 继续分析这个执行计划，第四行评估出了一个超级大的结果集，找到这个结果集的产生方式，第16行引起了我的注意。
- VIEW PUSHED PREDICATE,在存在out join和view的情况下，CBO自动选择了谓词推进到了视图VORG_DEPARTMENT中以过滤更多的数据，也算无可厚非。
- 但继续往上找，找到这个谓词的基数，第5行，发现评估出来的基数竟然有100多万行之多，而视图本身的结果集，只有不到3000行！
- 看来这是一个“愚蠢的”执行计划，CBO先选择视图之外的表做关联,tb_event虽然本身很小，但由于与其他表做多次外连接，
- 因此最终得到一个庞大的基数，而拿着这个结果集推进到视图中，想想是多么恐怖的事情！
- 一般来说，大集合的结果集合并不适合进行PUSHED PREDICATE。如果只是少量，会起到比较好的效果。
- 既然知道了原因，那就尝试着去改变这个执行计划。Oracle提供了no_push_pred和push_pred来改变pushed predication行为，
- 在上述语句中添加no_push_pred提示：
- SELECT *
- FROM (SELECT /*+ no_push_pred(vd) */ DISTINCT e.n_event_id,
- e.n_parent_id,
- e.v_event_num,
- ….
- FROM tb_event e,
- tb_event_related_person erpl,
- vorg_department vd,
- tb_callback cb,
- tb_event_log el,
- tb_event_marker em,
- tb_event_track et
- WHERE e.n_event_id = et.n_event_id(+)
- AND e.n_event_id = em.n_event_id(+)
- AND e.n_event_id = el.n_event_id(+)
- AND e.n_event_id = cb.n_event_id(+)
- AND erpl.n_dept_id = vd.recid(+)
- AND e.n_event_id = erpl.n_event_id(+)
- ORDER BY e.d_creation_date DESC)
- WHERE rownum <= 40;
- 修改后的执行计划如下：
- ———————————————————————————————————–
- | Id | Operation | Name | Rows | Bytes|TempSpc|Cost|
- ———————————————————————————————————–
- | 0 | SELECT STATEMENT | | 40 | 11553| | 181K|
- |* 1 | COUNT STOPKEY | | | | | |
- | 2 | VIEW | | 1470K| 5398M| | 181K|
- |* 3 | SORT UNIQUE STOPKEY | | 1470K| 370M| 765M| 92546|
- |* 4 | HASH JOIN OUTER | | 1470K| 370M| 46M| 3546|
- |* 5 | HASH JOIN OUTER | | 180K| 44M| 39M| 2499|、
- |* 6 | HASH JOIN OUTER | | 153K| 37M| 28M| 1873|
- |* 7 | HASH JOIN OUTER | | 115K| 27M| 24M| 1384|
- |* 8 | HASH JOIN OUTER | | 101K| 23M| 23M| 990|
- |* 9 | HASH JOIN OUTER | | 100K| 22M| 17M| 571|
- | 10 | TABLE ACCESS FULL | TB_EVENT | 77044| 16M| | 256|
- | 11 | INDEX FAST FULL SCAN| IDX_EVENT_TRACK_N_E_ID | 100K| 491K| | 4|
- | 12 | INDEX FAST FULL SCAN | IDX_TB_CALLBAK_E_ID | 75959| 296K| | 21|
- | 13 | TABLE ACCESS FULL | TB_EVENT_MARKER | 3686| 18430| | 3|
- |* 14 | INDEX FAST FULL SCAN | IDX_TB_EVENT_RP_DUP1 | 101K| 895K| | 4|
- | 15 | VIEW | VORG_DEPARTMENT | 3355| 20130| | 3|
- | 16 | NESTED LOOPS | | 3355| 50325| | 3|
- |* 17 | INDEX FAST FULL SCAN | ASSOCIATION8_FK | 3356| 30204| | 3|
- |* 18 | INDEX UNIQUE SCAN | PK_DEPARTMENT | 1| 6| | |
- | 19 | INDEX FAST FULL SCAN | IDX_TB_EVENT_LOG_N_E_ID| 628K| 2455K| | 149|
- ———————————————————————————————————
- 表tb_event e和tb_event_track et关联
- 通过e 和 tb_callback cb关联
- 通过 e和 tb_event_marker em,
- 调整后整个执行速度有了明显提升，15秒之内可以返回结果，可以看到view结果集与第六步产生的结果集进行hash join outer，然后得到的结果集合与最大的表tb_event_log再次进行hash join outer。当然这个执行计划可能还有完善的余地，需进一步根据数据情况调整表的关联顺序.
参考链接：
https://blogs.oracle.com/optimizer/entry/basics_of_join_predicate_pushdown_in_oracle
http://www.online-database.eu/index.php/sql-tuning/180-predicate-pushing
http://blog.csdn.net/zhaoyangjian724/article/details/17121707
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28786649/viewspace-1362588/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/28786649/viewspace-1362588/
904
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
