性能调优：JPPD（连接谓词推入）在不同版本之间的差别

发表于 5 天前

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要登录才可以下载或查看，没有账号？立即注册

×

我们的文章会在微信公众号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
*/

复制代码

这很有可能是bug了，由于正好我的版本比较高19c，可以通过遍历fix control去排查，这里其实还有一个本领，就是假如明确了是哪个功能出现了bug的话，可以直接去fix control里面查询看看11g之后修复了哪个bug从而修复了这个题目。

JPPD: JPPD bypassed: View is a set query block.

复制代码

很显着命中了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修复。

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')

复制代码

应该是CBO在JPPD查询转换时，在12.2版本之前漏掉了UNION ALL内嵌视图中包含group by的情况，在12.2版本做了修复。
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

性能调优：JPPD（连接谓词推入）在不同版本之间的差别

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

曹旭辉