Generally, join predicate push-down (JPPD) transformation is cost based. However, sometimes the optimizer falls back to the old-style (non-cost-based) JPPD (OJPPD). In such a case cost can be massively underestimated and the effect on the performance detrimental.<\/p>\n
I’ll be using a simple data set for demonstrating this on a 18.7 Oracle database:<\/p>\n
create table t1 (n1 integer) ;\ncreate table t2 (n1 integer, constraint pk_t2 primary key (n1)) ;\ncreate table t3 (n1 integer, n2 integer) ;\ncreate table t4 (n1 integer, c1 varchar2(4000)) ;\n\ninsert into t4 select 1, lpad('A',4000,'A') \n from dual connect by level <=100000 ;\ncommit ;\n\nbegin\n dbms_stats.gather_table_stats(null, 'T1' );\n dbms_stats.gather_table_stats(null, 'T2' );\n dbms_stats.gather_table_stats(null, 'T3' );\n dbms_stats.gather_table_stats(null, 'T4' );\nend;\n\/\n\nset pagesize 0\nset linesize 200<\/code><\/pre>\nLet’s start with a straightfoward query that produces a plan with JPPD:<\/p>\n
select \/*+ qb_name(MAIN) *\/ t1.n1 from \n t1,\n ( \n select \/*+ qb_name(RECEIVER) *\/ t2.n1 from t2, t4\n where t4.n1 = t2.n1\n ) v1\n where t1.n1 = v1.n1(+) \n;\n\nselect * from table(dbms_xplan.display_cursor(NULL,NULL)) ;\n\n---------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n---------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | | | 1615<\/span> (100)| |\n| 1 | NESTED LOOPS OUTER | | 1 | 15 | 1615<\/span> (1)| 00:00:01 |\n| 2 | TABLE ACCESS FULL | T1 | 1 | 13 | 2 (0)| 00:00:01 |\n| 3 | VIEW PUSHED PREDICATE | | 1 | 2 | 1613<\/span> (1)| 00:00:01 |\n| 4 | NESTED LOOPS | | 100K| 1562K| 1613<\/span> (1)| 00:00:01 |\n|* 5 | INDEX UNIQUE SCAN | PK_T2 | 1 | 13 | 0 (0)| |\n|* 6 | TABLE ACCESS FULL | T4 | 100K| 292K| 1613 (1)| 00:00:01 |\n---------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n 5 - access(\"T2\".\"N1\"=\"T1\".\"N1\")\n 6 - filter(\"T4\".\"N1\"=\"T1\".\"N1\")<\/code><\/pre>\nIt’s worth noting that the nested loop (NL) in the query block (QB) RECEIVER is the main contributor to the total cost. That’s the QB which receives the pushed predicate.<\/p>\n
The optimizer trace confirms the JPPD transformation:<\/p>\n
JPPD: Considering Cost-based predicate pushdown from query block MAIN (#1)\nCost-based predicate pushdown (JPPD)\nJPPD: Checking validity of push-down in query block MAIN (#1)\nJPPD: Checking validity of push-down from query block MAIN (#1) to query block RECEIVER (#2)\nJPPD: Passed validity checks\nJPPD: JPPD: Pushdown from query block MAIN (#1) passed validity checks.\nJPPD: Using search type: linear\nJPPD: Considering join predicate push-down\nJPPD: Starting iteration 1, state space = (2) : (0)\nJPPD: Performing join predicate push-down (no transformation phase) from query block MAIN (#1) to query block RECEIVER (#2)\nJPPD: Costing transformed query.\nJPPD: Updated best state, Cost = 1615.054794\nJPPD: Starting iteration 2, state space = (2) : (1)\nJPPD: Performing join predicate push-down (candidate phase) from query block MAIN (#1) to query block RECEIVER (#2)\nJPPD: Pushing predicate \"T1\".\"N1\"=\"V1\".\"N1\"(+)\nJPPD: Push dest of pred 0x80c9c730 is qb 0x80cab7b0:query block RECEIVER (#2)\nJPPD: Costing transformed query.\nJPPD: Retrieved original view card: 1.000000\nJPPD: Updated best state, Cost = 1614.996808\nJPPD: Will use JPPD from MAIN (#1) to RECEIVER (#2).\nJPPD: Applying transformation directives\nJPPD: Checking validity of push-down in query block MAIN (#1)\nJPPD: JPPD: Pushdown from query block MAIN (#1) passed validity checks.\nJPPD: Performing join predicate push-down (final phase) from query block MAIN (#1) to query block RECEIVER (#2)\nJPPD: Pushing predicate \"T1\".\"N1\"=\"V1\".\"N1\"(+)\nJPPD: Push dest of pred 0x80cab828 is qb 0x80cfeda0:query block RECEIVER (#2)\nJPPD: Retrieved original view card: 1.000000<\/code><\/pre>\nBut the cost suddenly drops after adding a hierarchical subquery:<\/p>\n
select \/*+ qb_name(MAIN) *\/ t1.n1 from \n t1,\n ( \n select \/*+ qb_name(RECEIVER) *\/ t2.n1 from t2, t4\n where t4.n1 = t2.n1\n ) v1,\n (\n select \/*+ qb_name(hierarchical) *\/ t3.n1 n1 from t3 \n\t connect by n2 = prior n1<\/span>\n ) v2\n where t1.n1 = v1.n1(+) and t1.n1 = v2.n1 \n;\n\nselect * from table(dbms_xplan.display_cursor(NULL,NULL)) ;\n\n----------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n----------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | | | 4<\/span> (100)| |\n|* 1 | HASH JOIN | | 1 | 39 | 4<\/span> (0)| 00:00:01 |\n| 2 | NESTED LOOPS OUTER | | 1 | 26 | 2<\/span> (0)| 00:00:01 |\n| 3 | TABLE ACCESS FULL | T1 | 1 | 13 | 2 (0)| 00:00:01 |\n| 4 | VIEW PUSHED PREDICATE | | 1 | 13 | 0<\/span> (0)| |\n| 5 | NESTED LOOPS | | 1 | 16 | 1613<\/span> (1)| 00:00:01 |\n|* 6 | INDEX UNIQUE SCAN | PK_T2 | 1 | 13 | 0 (0)| |\n|* 7 | TABLE ACCESS FULL | T4 | 1 | 3 | 1613 (1)| 00:00:01 |\n| 8 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |\n|* 9 | CONNECT BY WITHOUT FILTERING| | | | | |\n| 10 | TABLE ACCESS FULL | T3 | 1 | 26 | 2 (0)| 00:00:01 |\n----------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n 1 - access(\"T1\".\"N1\"=\"V2\".\"N1\")\n 6 - access(\"T2\".\"N1\"=\"T1\".\"N1\")\n 7 - filter((\"T4\".\"N1\"=\"T2\".\"N1\" AND \"T4\".\"N1\"=\"T1\".\"N1\"))\n 9 - access(\"N2\"=PRIOR NULL)<\/code><\/pre>\nSo instead of slightly increasing, the cost slumped. As you can see, the NL cost in the step 5 wasn’t propagated to its parent. The magnitude of the error is proportional to the following two values: QB RECEIVER cost and outer row source size. That’s not surprising at all, because the total cost is calculated as QB_RECEIVER_cost * num_outer_rows.<\/p>\n
The 10053 trace tells us that the optimizer, surprisingly, switched from JPPD to OJPPD:<\/p>\n
JPPD - join predicate push-down\nOJPPD - old-style (non-cost-based) JPPD\nOJPPD: Promoting index PK_T2\nOJPPD: Performing join predicate push-down to query block RECEIVER (#0)\nOJPPD: Pushing predicate \"T1\".\"N1\"=\"V1\".\"N1\"(+)\nOJPPD: Used promoted index: PK_T2\nOJPPD: Performing join predicate push-down to query block RECEIVER (#0)\nOJPPD: Pushing predicate \"T1\".\"N1\"=\"V1\".\"N1\"(+)\nOJPPD: Used promoted index: PK_T2<\/code><\/pre>\nFinally, I’m going to show an extremely unlucky case – with a large outer row source. To do that, first I’m going to insert lots of rows into the outer table T1.<\/p>\n
insert into t1 select 1\n from dual connect by level <=1e6 ;\ncommit ;\n\nexec dbms_stats.gather_table_stats(null, 'T1' );<\/code><\/pre>\nExpectedly, the execution plan for the query without the hierarchical subquery changed – JPPD isn’t performed anymore because the transformed query cost exploded after increasing the outer row source size.<\/p>\n
select \/*+ qb_name(MAIN) *\/ t1.n1 from \n t1,\n ( \n select \/*+ qb_name(RECEIVER) *\/ t2.n1 from t2, t4\n where t4.n1 = t2.n1\n ) v1\n where t1.n1 = v1.n1(+) \n;\n\nselect * from table(dbms_xplan.display_cursor(NULL,NULL)) ;\n\n-------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n-------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | | | 1659 (100)| |\n|* 1 | HASH JOIN RIGHT OUTER| | 1000K| 15M| 1659 (1)| 00:00:01 |\n| 2 | VIEW | | 1 | 13 | 1613 (1)| 00:00:01 |\n|* 3 | HASH JOIN | | 1 | 16 | 1613 (1)| 00:00:01 |\n| 4 | INDEX FULL SCAN | PK_T2 | 1 | 13 | 0 (0)| |\n| 5 | TABLE ACCESS FULL | T4 | 100K| 292K| 1613 (1)| 00:00:01 |\n| 6 | TABLE ACCESS FULL | T1 | 1000K| 2929K| 45 (3)| 00:00:01 |\n-------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n 1 - access(\"T1\".\"N1\"=\"V1\".\"N1\")\n 3 - access(\"T4\".\"N1\"=\"T2\".\"N1\")<\/code><\/pre>\nThe optimizer trace shows the exact costs of both transformed and non-transformed queries:<\/p>\n
opt_qb.awk<\/a> -v qb=MAIN DB_ora_13845.trc\n---------------\n36 Registered qb: MAIN 0x86311848 (HINT MAIN)\n1181 JPPD: Performing join predicate push-down (no transformation<\/span> phase) from query block MAIN (#1) to query block RECEIVER (#2)\n1689 Final cost for query block MAIN (#1) - All Rows Plan:\n1690 Best join order: 1\n1691 Cost: 1659.234715<\/span> Degree: 1 Card: 1000000.000000 Bytes: 16000000.000000\n---------------\n36 Registered qb: MAIN 0x86311848 (HINT MAIN)\n1704 JPPD: Performing join predicate push-down (candidate<\/span> phase) from query block MAIN (#1) to query block RECEIVER (#2)\n2065 Final cost for query block MAIN (#1) - All Rows Plan:\n2066 Best join order: 1\n2067 Cost: 1612996725.467509<\/span> Degree: 1 Card: 1000000.000000 Bytes: 5000000.000000\n---------------\n36 Registered qb: MAIN 0x86311848 (HINT MAIN)\n2089 JPPD: Performing join predicate push-down (no transformation phase) from query block MAIN (#1) to query block RECEIVER (#2)\n2741 Final cost for query block MAIN (#1) - All Rows Plan:\n2742 Best join order: 1\n2743 Cost: 1659.234715 Degree: 1 Card: 1000000.000000 Bytes: 16000000.000000<\/code><\/pre>\nBy the way, opt_qb.awk<\/a> comes in handy for analyzing query transformations. It’s an awk script for extracting the essential information about QBs and is documented in a previous blog post<\/a>.<\/p>\nIn contrast, the execution plan for the query with the hierarchical subquery still contains the PUSHED PREDICATE operation:<\/p>\n
-----------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n-----------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | | | 48 (100)| |\n| 1 | NESTED LOOPS OUTER | | 1 | 29 | 48 (5)| 00:00:01 |\n|* 2 | HASH JOIN | | 1 | 16 | 48 (5)| 00:00:01 |\n| 3 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |\n|* 4 | CONNECT BY WITHOUT FILTERING| | | | | |\n| 5 | TABLE ACCESS FULL | T3 | 1 | 26 | 2 (0)| 00:00:01 |\n| 6 | TABLE ACCESS FULL | T1 | 1000K| 2929K| 45 (3)| 00:00:01 |\n| 7 | VIEW PUSHED PREDICATE | | 1 | 13 | 0<\/span> (0)| |\n| 8 | NESTED LOOPS | | 1 | 16 | 1613 (1)| 00:00:01 |\n|* 9 | INDEX UNIQUE SCAN | PK_T2 | 1 | 13 | 0 (0)| |\n|* 10 | TABLE ACCESS FULL | T4 | 1 | 3 | 1613 (1)| 00:00:01 |\n-----------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n 2 - access(\"T1\".\"N1\"=\"V2\".\"N1\")\n 4 - access(\"N2\"=PRIOR NULL)\n 9 - access(\"T2\".\"N1\"=\"T1\".\"N1\")\n 10 - filter((\"T4\".\"N1\"=\"T2\".\"N1\" AND \"T4\".\"N1\"=\"T1\".\"N1\"))<\/code><\/pre>\nIn this particular case, the cost was hugely underestimated – by the factor 33 million.<\/p>\n
A possible workaround is to selectively disable pushing predicates with the following hint:<\/p>\n
opt_param('_push_join_predicate', 'false')<\/code><\/pre>\nOn the downside, this will also prevent useful predicates from being pushed.<\/p>\n
In summary, hierarchical subqueries can lead to suboptimal execution plans in conjunction with JPPD. The cost will be massively underestimated for large outer row sources. This problem can be easily detected by comparing the VIEW PUSHED PREDICATE cost to its children – VIEW PUSHED PREDICATE has a lower cost. In fact, I’m just thinking of writing a procedure for parsing execution plans and reporting such issues.<\/p>\n
Update on August 25th 2019<\/h1>\n
The wrong cost calculation seems to be caused by the bug 22582700. The issue is fixed in Oracle 19.3.
\nMany thanks to Timur Akhmadeev for providing this information (see the comments section).<\/p>\n","protected":false},"excerpt":{"rendered":"
Suboptimal execution plans after falling back to OJPPD with hierarchical subqueries Continue Reading