性能优化：oracle partial join eval
本文通过真实案例分析了Oracle SQL性能优化中的partial join eval（PJE）技术。文章指出，在高频值JOIN关联大量数据时，JOIN操作本身的时间消耗往往超过逻辑读/物理读的消耗。通过对比11g和23ai版本的执行计划，展示了PJE技术在12c及以上版本的优化效果：将JOIN转换为SEMI JOIN，大幅缩短执行时间（从16秒降至0.05秒）。文章还介绍了PJE的触发条件（多
我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
本文转自朋友的真实案例分享。
性能优化：oracle partial join eval
在oracle sql优化中，往往只关注逻辑读的消耗，而忽略了join消耗的时间，特别是高频值进行join关联出大量rows的场景。往往join消耗的时间会比扫描对象消耗的逻辑读物理读更耗时。
例如下面这个sql(11g)：
SQL Monitoring Report
SQL Text
------------------------------
SELECT /*+monitor test111*/ distinct t.OBJECT_ID, t.DATA_OBJECT_ID FROM test.t t, test.t1 t1 WHERE t.object_type = t1.object_type and t.DATA_OBJECT_ID is not null ORDER BY t.DATA_OBJECT_ID
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SYS (41:31)
SQL ID : dc02gzj9a9ukr
SQL Execution ID : 16777216
Execution Started : 04/21/2025 13:54:55
First Refresh Time : 04/21/2025 13:54:55
Last Refresh Time : 04/21/2025 13:55:11
Duration : 16s
Module/Action : sqlplus@ora19c-node1 (TNS V1-V3)/-
Service : SYS$USERS
Program : sqlplus@ora19c-node1 (TNS V1-V3)
Fetch Calls : 601
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 16 | 16 | 0.09 | 601 | 2489 |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=1001474438)
===================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
===================================================================================================================================
| 0 | SELECT STATEMENT | | | | 15 | +2 | 1 | 8995 | | | |
| 1 | SORT UNIQUE | | 18M | 136K | 16 | +1 | 1 | 8995 | 499K | 100.00 | Cpu (15) |
| 2 | HASH JOIN | | 18M | 740 | 15 | +2 | 1 | 32M | 2M | | |
| 3 | TABLE ACCESS FULL | T | 8995 | 346 | 1 | +2 | 1 | 8995 | | | |
| 4 | TABLE ACCESS FULL | T1 | 86949 | 346 | 15 | +2 | 1 | 86730 | | | |
===================================================================================================================================
可以看到该sql在11g中，逻辑读仅仅2489，但是却消耗了16s时间，这就是因为高频值的join消耗了大量的时间。
oracle在12c版本中，对于该sql进行了逻辑改写的查询转换，partial join eval(PJE)，属于一种启发式查询转换，也就是基于规则的查询转换，当满足规则时就会进行转换改写。
PJE的规则满足条件如下：
多表连接
多表连接查询sql的select list的字段全部来自同一个表
select list需要带有distinct去重操作
PJE的改写逻辑：
会把JOIN转换成SEMI JOIN，巧妙的规避了高频值JOIN返回大量rows，类似JOIN等价改为子查询。
PJE的参数控制和hint：
由隐藏参数_optimizer_partial_join_eval控制，默认为true。hint为PARTIAL_JOIN/NO_PARTIAL_JOIN。
23ai中重新跑一次测试的sql：
SQL Monitoring Report
SQL Text
------------------------------
SELECT /*+monitor test111*/ distinct t.OBJECT_ID, t.DATA_OBJECT_ID FROM test.t t, test.t1 t1 WHERE t.object_type = t1.object_type and t.DATA_OBJECT_ID is not null ORDER BY t.DATA_OBJECT_ID
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SYS (282:43874)
SQL ID : dc02gzj9a9ukr
SQL Execution ID : 16777216
Execution Started : 04/21/2025 14:02:44
First Refresh Time : 04/21/2025 14:02:44
Last Refresh Time : 04/21/2025 14:02:44
Duration : .052885s
Module/Action : sqlplus@db (TNS V1-V3)/-
Service : vastdata
Program : sqlplus@db (TNS V1-V3)
Fetch Calls : 633
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.05 | 0.05 | 0.00 | 633 | 1845 |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=634468599)
===================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
===================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 9478 | . | | |
| 1 | SORT UNIQUE | | 9478 | 881 | 1 | +0 | 1 | 9478 | 550KB | | |
| 2 | HASH JOIN SEMI | | 9478 | 880 | 1 | +0 | 1 | 9478 | 2MB | | |
| 3 | TABLE ACCESS FULL | T | 9478 | 440 | 1 | +0 | 1 | 9478 | . | | |
| 4 | TABLE ACCESS FULL | T1 | 73257 | 440 | 1 | +0 | 1 | 13225 | . | | |
===================================================================================================================================
由于SEMI JOIN不需要返回所有JOIN上的行，只需要检查是否匹配上，所以JOIN消耗的时间会大幅度降低。
10053：
OPTIMIZER STATISTICS AND COMPUTATIONS
PJE: Checking validity of partial join eval on query block SEL$1 (#1)
PJE: Passed validity of partial join eval by query block SEL$1 (#1)
number: [0]
PJE: Partial join eval conversion for query block SEL$1 (#1).
PJE: Table marked for partial join eval: T1[T1]#1
outline会出现PARTIAL_JOIN(@”SEL$1″ “T1″@”SEL$1”)。
该特性在国产数据库中，几乎没有厂商支持。
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
有“AI”的1024 = 2048，欢迎大家加入2048 AI社区
更多推荐
4
0- 0
已为社区贡献10条内容
所有评论(0)