本案例来自同事咨询我的一个sql案例，数据库版本为11.2.0.4，经过同事的分析发现，sql性能差的原因是没有做连接谓词推入，但是没有找到原因。
sql文本：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
WITH TEMP AS (SELECT /*+ INLINE */ DO.PROVORGCODE, DO.PROVORGNAME, DO.CITYORGCODE, DO.CITYORGNAME, DO.TOWNORGCODE, DO.TOWNORGNAME, D.TEAM_ID_AREA, D.TEAM_NAME_AREA, D.TEAM_ID_DEPT, D.TEAM_NAME_DEPT, D.TEAM_ID_GRP, D.TEAM_NAME_GRP, T02.CHANNEL_ID, T02.SALES_NAME, T02.SALES_CODE, T02.PROBATION_DATE, T02.RANK, T02.ENTER_RANK FROM ODSUSER.T02SALESINFO_BACKUP T02 INNER JOIN DMUSER.D_AGENT DA ON T02.SALES_CODE = DA.AGENTCODE INNER JOIN DMUSER.D_ORG DO ON T02.BRANCH_ID = DO.ORGCODE INNER JOIN DMUSER.D_TEAMINFO_CHANNEL D ON T02.TEAM_ID = D.TEAM_ID AND T02.CHANNEL_ID = D.CHANNEL_ID WHERE T02.YEAR_MONTH = TO_CHAR(to_date('2023-11-30', 'yyyy-mm-dd'), 'YYYYMM') AND DA.ENTERCOMPDATE <= to_date('2023-11-30', 'yyyy-mm-dd') AND (DA.LEAVECOMPDATE > to_date('2023-11-30', 'yyyy-mm-dd') OR DA.LEAVECOMPDATE IS NULL) and D.TEAM_ID_GRP = '1411005026' AND T02.CHANNEL_ID IN ('05')), A AS (SELECT * FROM TEMP LEFT JOIN (SELECT /*+ PUSH_PRED */ T.AGENTCODE, SUM(T.ZX_CUST_CNT) ZX_CUST_CNT, LEAST(SUM(NVL(T.ZT_PLCY_CNT_L, 0)), 5) + SUM(T.ZT_PLCY_CNT) ZT_PLCY_CNT, SUM(T.SX_PLCY_CNT) SX_PLCY_CNT, SUM(T.SHARE_COUNT) SHARE_COUNT, 0 HD_CUST_CNT, 0 HY_CUST_CNT, 0 BD_CUST_CNT, SUM(TARGET_PREM_NUM) TARGET_PREM_NUM, SUM(OFFLINE_NUM) OFFLINE_NUM, SUM(VALIDATE_CUST_NUM) VALIDATE_CUST_NUM, SUM(FSNN_SX_CNT) FSNN_SX_CNT, SUM(T.JCX_CNT) JCX_CNT, 0 ZF_CNT, SUM(NVL(T.SX_PLCY_CNT_L, 0)) SX_PLCY_CNT_L, SUM(NVL(T.JCX_CNT_L, 0)) JCX_CNT_L, 0 ZF_CNT_L FROM DMA_XSHDL_BFZ_RPT T WHERE T.DATEID >= TO_DATE('2023-11-01', 'YYYY-MM-DD') AND T.DATEID <= TO_DATE('2023-11-30', 'YYYY-MM-DD') GROUP BY T.AGENTCODE UNION ALL SELECT /*+ PUSH_PRED */T.AGENT_CODE, 0 ZX_CUST_CNT, 0 ZT_PLCY_CNT, 0 SX_PLCY_CNT, 0 SHARE_COUNT, 0 HD_CUST_CNT, 0 HY_CUST_CNT, 0 BD_CUST_CNT, 0 TARGET_PREM_NUM, COUNT(1) OFFLINE_NUM, 0 VALIDATE_CUST_NUM, 0 FSNN_SX_CNT, 0 JCX_CNT, 0 ZF_CNT, 0 SX_PLCY_CNT_L, 0 JCX_CNT_L, 0 ZF_CNT_L FROM (SELECT DISTINCT T.AGENT_CODE, T.CUST_NAME, T.GENDER, T.CONTACT_PHONE_NUM FROM intf_rpt_user.DMA_MKT_MARKET_CUST_MEMBER T WHERE T.SIGN_TIME >= to_date('2023-11-01', 'yyyy-mm-dd') AND T.SIGN_TIME < to_date('2023-11-30', 'yyyy-mm-dd') + 1) T GROUP BY T.AGENT_CODE) T ON TEMP.SALES_CODE = T.AGENTCODE) select /*+ 11 */* from A |
sql很长，看看执行计划：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 |
============ Plan Table ============ -----------------------------------------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | -----------------------------------------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 108K | | | 1 | NESTED LOOPS | | 12 | 6372 | 108K | 00:22:13 | | 2 | NESTED LOOPS | | 12 | 6372 | 108K | 00:22:13 | | 3 | HASH JOIN OUTER | | 12 | 6048 | 108K | 00:22:12 | | 4 | NESTED LOOPS | | 12 | 3996 | 542 | 00:00:07 | | 5 | NESTED LOOPS | | 12 | 3996 | 542 | 00:00:07 | | 6 | NESTED LOOPS | | 12 | 2736 | 518 | 00:00:07 | | 7 | TABLE ACCESS BY INDEX ROWID | D_TEAMINFO_CHANNEL | 1 | 147 | 5 | 00:00:01 | | 8 | INDEX RANGE SCAN | IDX_TEAMINFO_TEAMGRP | 1 | | 3 | 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID | T02SALESINFO_BACKUP | 86 | 6966 | 513 | 00:00:07 | | 10 | INDEX RANGE SCAN | IDX_T02SALESBACK_TEAM_CHA | 608 | | 4 | 00:00:01 | | 11 | INDEX RANGE SCAN | IDX_ORGCODE | 1 | | 1 | 00:00:01 | | 12 | TABLE ACCESS BY INDEX ROWID | D_ORG | 1 | 105 | 2 | 00:00:01 | | 13 | VIEW | | 95K | 16M | 108K | 00:22:06 | | 14 | UNION-ALL | | | | | | | 15 | HASH GROUP BY | | 13K | 757K | 91K | 00:19:42 | | 16 | TABLE ACCESS STORAGE FULL | DMA_XSHDL_BFZ_RPT | 14K | 797K | 91K | 00:19:42 | | 17 | HASH GROUP BY | | 81K | 1466K | 17K | 00:03:24 | | 18 | VIEW | | 81K | 1466K | 17K | 00:03:24 | | 19 | HASH UNIQUE | | 81K | 4804K | 17K | 00:03:24 | | 20 | TABLE ACCESS STORAGE FULL | DMA_MKT_MARKET_CUST_MEMBER| 81K | 4804K | 15K | 00:03:11 | | 21 | INDEX RANGE SCAN | IDX_AGENT_AGENTCODE | 1 | | 2 | 00:00:01 | | 22 | TABLE ACCESS BY INDEX ROWID | D_AGENT | 1 | 27 | 3 | 00:00:01 | -----------------------------------------------------------------------+-----------------------------------+ Predicate Information: ---------------------- 3 - access("T02"."SALES_CODE"="T"."AGENTCODE") 7 - filter("D"."CHANNEL_ID"='05') 8 - access("D"."TEAM_ID_GRP"='1411005026') 9 - filter("T02"."YEAR_MONTH"='202311') 10 - access("T02"."TEAM_ID"="D"."TEAM_ID" AND "T02"."CHANNEL_ID"='05') 11 - access("T02"."BRANCH_ID"="DO"."ORGCODE") 16 - access(("T"."DATEID">=TO_DATE(' 2023-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."DATEID"<=TO_DATE(' 2023-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) 16 - filter(("T"."DATEID">=TO_DATE(' 2023-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."DATEID"<=TO_DATE(' 2023-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) 20 - access(("T"."SIGN_TIME">=TO_DATE(' 2023-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."SIGN_TIME"<TO_DATE(' 2023-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) 20 - filter(("T"."SIGN_TIME">=TO_DATE(' 2023-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."SIGN_TIME"<TO_DATE(' 2023-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) 21 - access("T02"."SALES_CODE"="DA"."AGENTCODE") 22 - filter((("DA"."LEAVECOMPDATE" IS NULL OR "DA"."LEAVECOMPDATE">TO_DATE(' 2023-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) AND "DA"."ENTERCOMPDATE"<=TO_DATE(' 2023-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) Content of other_xml column =========================== db_version : 11.2.0.4 parse_schema : INTF_RPT_USER dynamic_sampling: 2 plan_hash : 2407633558 plan_hash_2 : 3749594226 Outline Data: /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$4") OUTLINE_LEAF(@"SEL$3") OUTLINE_LEAF(@"SET$1") OUTLINE_LEAF(@"SEL$79A905B1") MERGE(@"SEL$1A4CF335") OUTLINE(@"SEL$11") OUTLINE(@"SEL$1A4CF335") MERGE(@"SEL$CEFD41C7") OUTLINE(@"SEL$10") OUTLINE(@"SEL$CEFD41C7") MERGE(@"SEL$1") MERGE(@"SEL$AB668856") OUTLINE(@"SEL$5") OUTLINE(@"SEL$1") OUTLINE(@"SEL$AB668856") MERGE(@"SEL$E16E17DA") OUTLINE(@"SEL$9") OUTLINE(@"SEL$E16E17DA") MERGE(@"SEL$6DE49B00") OUTLINE(@"SEL$8") OUTLINE(@"SEL$6DE49B00") MERGE(@"SEL$6") OUTLINE(@"SEL$7") OUTLINE(@"SEL$6") INDEX_RS_ASC(@"SEL$79A905B1" "D"@"SEL$8" ("D_TEAMINFO_CHANNEL"."TEAM_ID_GRP")) INDEX_RS_ASC(@"SEL$79A905B1" "T02"@"SEL$6" ("T02SALESINFO_BACKUP"."TEAM_ID" "T02SALESINFO_BACKUP"."CHANNEL_ID")) INDEX(@"SEL$79A905B1" "DO"@"SEL$7" ("D_ORG"."ORGCODE")) NO_ACCESS(@"SEL$79A905B1" "T"@"SEL$1") INDEX(@"SEL$79A905B1" "DA"@"SEL$6" ("D_AGENT"."AGENTCODE")) LEADING(@"SEL$79A905B1" "D"@"SEL$8" "T02"@"SEL$6" "DO"@"SEL$7" "T"@"SEL$1" "DA"@"SEL$6") USE_NL(@"SEL$79A905B1" "T02"@"SEL$6") USE_NL(@"SEL$79A905B1" "DO"@"SEL$7") NLJ_BATCHING(@"SEL$79A905B1" "DO"@"SEL$7") USE_HASH(@"SEL$79A905B1" "T"@"SEL$1") USE_NL(@"SEL$79A905B1" "DA"@"SEL$6") NLJ_BATCHING(@"SEL$79A905B1" "DA"@"SEL$6") NO_ACCESS(@"SEL$3" "T"@"SEL$3") USE_HASH_AGGREGATION(@"SEL$3") FULL(@"SEL$2" "T"@"SEL$2") USE_HASH_AGGREGATION(@"SEL$2") FULL(@"SEL$4" "T"@"SEL$4") USE_HASH_AGGREGATION(@"SEL$4") END_OUTLINE_DATA */ |
同事的主要问题就是分析UNION ALL视图为什么没有做连接谓词推入。
通过10053发现：
|
1 |
JPPD: JPPD bypassed: View is a set query block. |
JPPD被优化器拒绝的原因居然是这是一个集合查询块，这是明显不可能的。因为做sql优化很多的人肯定知道，对于UNION ALL视图肯定是可以做连接谓词推入的。测试去掉UNOIN ALL里面的group by就可以推入，加上就不行。这非常不科学。
进一步分析，模拟sql结构测试，我的环境只有19c的，结果发现19c是可以的，而11g不行。
19c:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
SQL> explain plan for select t1.object_id,t1.object_name from test.t1, 2 3 (select object_id,count(*) 4 from test.t group by object_id 5 6 union all 7 select object_id,count(*) 8 from test.t 9 group by object_id) t 10 where t1.owner='SYS' and t.object_id(+)=t1.object_id; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 4122290605 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 5843 | 285K| 17927 (1)| 00:00:01 | | 1 | NESTED LOOPS OUTER | | 5843 | 285K| 17927 (1)| 00:00:01 | |* 2 | TABLE ACCESS FULL | T1 | 2921 | 128K| 396 (1)| 00:00:01 | | 3 | VIEW | | 1 | 5 | 6 (0)| 00:00:01 | | 4 | UNION ALL PUSHED PREDICATE | | | | | | | 5 | SORT GROUP BY | | 1 | 5 | 3 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | IDX_T_OBJECTID | 32 | 160 | 3 (0)| 00:00:01 | | 7 | SORT GROUP BY | | 1 | 5 | 3 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | IDX_T_OBJECTID | 32 | 160 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T1"."OWNER"='SYS') 6 - access("OBJECT_ID"="T1"."OBJECT_ID") 8 - access("OBJECT_ID"="T1"."OBJECT_ID") |
11g：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
SQL> alter session set optimizer_features_enable='11.2.0.4'; Session altered. SQL> explain plan for 2 select t1.object_id,t1.object_name from test.t1, 3 (select object_id,count(*) 4 from test.t 5 group by object_id 6 union all 7 select object_id,count(*) 8 from test.t 9 group by object_id) t 10 where t1.owner='SYS' and t.object_id(+)=t1.object_id; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1399071787 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5843 | 330K| | 30692 (1)| 00:00:02 | |* 1 | HASH JOIN OUTER | | 5843 | 330K| | 30692 (1)| 00:00:02 | |* 2 | TABLE ACCESS FULL | T1 | 2921 | 128K| | 396 (1)| 00:00:01 | | 3 | VIEW | | 148K| 1880K| | 30296 (1)| 00:00:02 | | 4 | UNION-ALL | | | | | | | | 5 | HASH GROUP BY | | 74064 | 361K| 26M| 15148 (1)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 6 | TABLE ACCESS FULL| T | 2337K| 11M| | 12442 (1)| 00:00:01 | | 7 | HASH GROUP BY | | 74064 | 361K| 26M| 15148 (1)| 00:00:01 | | 8 | TABLE ACCESS FULL| T | 2337K| 11M| | 12442 (1)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T"."OBJECT_ID"(+)="T1"."OBJECT_ID") 2 - filter("T1"."OWNER"='SYS') |
这很有可能是bug了，由于正好我的版本比较高19c，可以通过遍历fix control去排查，这里其实还有一个技巧，就是如果明确了是哪个功能出现了bug的话，可以直接去fix control里面查询看看11g之后修复了哪个bug从而修复了这个问题。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SQL> select bugno,DESCRIPTION,OPTIMIZER_FEATURE_ENABLE from V$SYSTEM_FIX_CONTROL where upper(DESCRIPTION) like '%JPPD%' and OPTIMIZER_FEATURE_ENABLE>'12'; BUGNO DESCRIPTION OPTIMIZER_FEATURE_ENABLE ---------- ---------------------------------------------------------------- ------------------------- 7524366 enable JPPD for insert statements 8.0.0 14085520 Reset xpl annotations if OJPPD rejected 12.1.0.2 18365267 Allow JPPD into SPJ view with NO_MERGE hint 12.1.0.2 18415557 allow FPD with non-JPPD correlation into fixed view 12.2.0.1 18798414 Unnest distinct view in IN/ANY subquery if candidate for JPPD 12.2.0.1 18969167 enhance JPPD index heuristic for set query blocks 12.2.0.1 19046459 no OJPPD rejection on non-pushable predicate when valid exists 12.2.0.1 19025959 clean stats unconditionally if OJPPD not done 12.2.0.1 19803410 enable access heuristics for cost-based JPPD 8.0.0 19503668 allow JPPD into multiple views with only conflicting predicates 12.2.0.1 18776755 allow JPPD into view with constant expression in HAVING clause 12.2.0.1 20506136 allow JPPD if table expression does not refer plsql operator 12.2.0.1 20340595 Skip in-memory costing if no in-memory table in legacy JPPD view 12.2.0.1 21476032 prorate view's base cardinality while trying JPPD in FKR mode 12.2.0.1 22090662 allow JPPD if view is on left of anti/semi join & cannot be merg 12.2.0.1 21099502 Enable extended JPPD for UNION[ALL] views having group by 12.2.0.1 24926999 follow materialized WITH references in additional phase of JPPD 18.1.0 19956351 include access by rowid in JPPD index heuristics 18.1.0 23473108 partial JPPD with cartesian join in parent query block 18.1.0 27343844 allow JPPD on query blocks with Key Vector Use operators 19.1.0 26733841 enable multi-level SET query block JPPD 19.1.0 22582700 adjust the cardinality for a OJPPD view. 19.1.0 |
很明显命中了bug 21099502，描述为Enable extended JPPD for UNION[ALL] views having group by，在12.2修复的。
查询mos，Bug 21099502 Join Predicates not pushed into UNION ALL view having group by and aggregates，非常匹配。该bug在12.2修复。
|
1 2 3 4 5 6 |
Description JPPD not happening into union all view having group by Rediscovery Notes JPPD not happening in UNION ALL view having group by and aggregates |
应该是CBO在JPPD查询转换时，在11g版本之前漏掉了UNION ALL内嵌视图中包含group by的情况，在12.2版本做了修复。