我们的文章会在微信公众号Oracle恢复实录和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
在日常的数据库运维和开发工作中，性能调优一直是让人头疼又不得不面对的话题。尤其是在处理JPPD（Join Predicate Push-Down）相关的性能问题时，很多同学会遇到“明明加了索引，SQL还是慢”、“连接条件推不下去”等让人困惑的现象。其实，这背后不仅仅是SQL写法的问题，更涉及到数据库优化器的工作原理和一些容易被忽视的细节。
本文将通过一个利用多个版本在JPPD中不同的行为，带你一步步分析JPPD连接谓词推入失败的BUG定位方法，并结合实际案例，帮助你快速定位和解决类似的性能瓶颈。无论你是数据库新手，还是有经验的DBA，相信都能从中获得实用的调优思路和技巧。
本案例也是来自于朋友案例的分享，同事咨询我的一个sql案例，数据库版本为11.2.0.4，经过同事的分析发现，sql性能差的原因是没有做连接谓词推入，但是没有找到原因，具体的SQL语句如下：
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
看看执行计划：
============
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，在100053中关于JPPD有这么一样。
JPPD: JPPD bypassed: View is a set query block.
JPPD被优化器拒绝的原因居然是这是一个集合查询块，这是明显不可能的。因为做sql优化很多的人肯定知道，对于UNION ALL视图肯定是可以做连接谓词推入的。测试去掉UNOIN ALL里面的group by就可以推入，加上就不行。这非常不科学。
进一步分析，模拟sql结构测试，我的环境只有19c的，结果发现19c是可以的，而11g不行。
19c:
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：
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从而修复了这个问题。
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修复。
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查询转换时，在12.2版本之前漏掉了UNION ALL内嵌视图中包含group by的情况，在12.2版本做了修复。
——————作者介绍———————–
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
性能调优：JPPD（连接谓词推入）在不同版本之间的差异：等您坐沙发呢！