oralce之 10046对Hash Join分析

最新推荐文章于 2025-06-25 13:54:02 发布
最新推荐文章于 2025-06-25 13:54:02 发布
阅读量36 收藏
点赞数
CC 4.0 BY-SA版权
文章标签： 数据结构与算法
原文链接：http://www.cnblogs.com/andy6/p/7502059.html
通过将OR条件替换为UNION，解决了因哈希表桶中行数过多导致的SQL执行效率低下问题，执行时间从几小时缩短到16分钟。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

前两天解决了一个优化SQL的case，SQL语句如下，big_table为150G大小，small_table很小，9000多条记录，不到1M大小，hash_area_size, sort_area_size均设置足够大，可以进行optimal hash join和memory sort。

1
2
3
4
5
6
select /*+ leading(b) use_hash(a b) */ distinct a.ID
from BIG_TABLE a, SMALL_TABLE b
where (a.category  = b.from_cat or
       a.category2 = b.from_cat) and
       a.site_id  = b.site_id and
       a.sale_end >= sysdate;

执行计划如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--------------------------------------------------------------------------
| Id  | Operation            |  Name        | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |     2 |   174 |    18  (17)|
|   1 |  SORT UNIQUE         |              |     2 |   174 |    18  (17)|
|*  2 |   HASH JOIN          |              |     2 |   174 |    17  (12)|
|   3 |    TABLE ACCESS FULL | SMALL_TABLE  |  1879 | 48854 |    14   (8)|
|*  4 |    TABLE ACCESS FULL | BIG_TABLE    |     4 |   244 |     3  (34)|
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."SITE_ID"="B"."SITE_ID")
       filter("A"."CATEGORY"="B"."FROM_CAT" OR
              "A"."CATEGORY2"="B"."FROM_CAT")
   4 - filter("A"."SALE_END">=SYSDATE@!)

粗略来看，PLAN非常的完美，SQL HINT写的也很到位，小表在内build hash table,大表在外进行probe操作，根据经验来看，整个SQL执行的时间应该和FTS（Full Table Scan） BIG_TABLE的时间差不多。

但是FTS BIG_TABLE的时间大约是8分钟，而真个SQL执行的时间长达3~4小时。

那么问题究竟出在哪里?

FTS时间应该不会有太大变化，那么问题应该在hash join，设置event来trace一下hash join的过程：

1
alter session set events '10104 trace name context forever, level 2';
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
### Hash table ###
# NOTE: The calculated number of rows in non-empty buckets may be smaller
#       than the true number.
Number of buckets with   0 rows:      16373
Number of buckets with   1 rows:          0
Number of buckets with   2 rows:          0
Number of buckets with   3 rows:          1
Number of buckets with   4 rows:          0
Number of buckets with   5 rows:          0
Number of buckets with   6 rows:          0
Number of buckets with   7 rows:          1
Number of buckets with   8 rows:          0
Number of buckets with   9 rows:          0
Number of buckets with between  10 and  19 rows:          1
Number of buckets with between  20 and  29 rows:          1
Number of buckets with between  30 and  39 rows:          3
Number of buckets with between  40 and  49 rows:          0
Number of buckets with between  50 and  59 rows:          0
Number of buckets with between  60 and  69 rows:          0
Number of buckets with between  70 and  79 rows:          0
Number of buckets with between  80 and  89 rows:          0
Number of buckets with between  90 and  99 rows:          0
Number of buckets with 100 or more rows:          4
### Hash table overall statistics ###
Total buckets: 16384 Empty buckets: 16373 Non-empty buckets: 11
Total number of rows: 9232
Maximum number of rows in a bucket: 2531
Average number of rows in non-empty buckets: 839.272705

仔细看，在一个bucket中最多的行数竟然有2531行，因为bucket中是一个链表的结构，所以这几千行都是串在一个链表上。 
由这一点想到这个Hash Table所依赖的hash key的distinct value可能太少，重复值太多。否则不应该会有这么多行在同一个bucket里面。

因为Join条件里面有两个列from_cat和site_id,穷举法有三种情况：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> select site_id,from_cat,count(*) from SMALL_TABLE group by site_id,from_cat having count(*)>100;
 
no rows selected
 
2. Build hash table based on (from_cat):
 
SQL> select from_cat,count(*) from SMALL_TABLE group by from_cat having count(*)>100;
 
no rows selected
 
3. Build hash table based on (site_id):
 
SQL> select site_id,count(*) from SMALL_TABLE group by site_id having count(*)>100;
 
   SITE_ID   COUNT(*)
---------- ----------
         0       2531
         2       2527
       146       1490
       210       2526

到这里可以发现，基于site_id这种情况和trace file中这两行很相符：

1
2
Number of buckets with 100 or more rows: 4
Maximum number of rows in a bucket: 2531

注：这判断过程可以从执行计划的“Predicate Information”部分看出：

1
access("A"."SITE_ID"="B"."SITE_ID")

所以推断这个hash table是基于site_id而建的，而Big_Table中大量的行site_id=0，都落在这个linked list最长的bucket中，而大部分行都会扫描完整个链表而最后被丢弃掉,所以这个Hash Join的操作效率非常差，几乎变为了Nest Loop操作。

找到了根本原因，问题也就迎刃而解了。

理想状况下，hash table应当建立于(site_id,from_cat)上，那么问题肯定出在这个OR上，把OR用UNION改写：

1
2
3
4
5
6
7
8
9
10
11
select /*+ leading(b) use_hash(a b) */ distinct a.ID
from BIG_TABLE a, SMALL_TABLE b
where  a.category  = b.from_cat and
       a.site_id  = b.site_id and
       a.sale_end >= sysdate
UNION
select /*+ leading(b) use_hash(a b) */ distinct a.ID
from BIG_TABLE a, SMALL_TABLE b
where  a.category2 = b.from_cat and
       a.site_id  = b.site_id and
       a.sale_end >= sysdate;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--------------------------------------------------------------------------
| Id  | Operation            |  Name        | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |     2 |   148 |    36  (59)|
|   1 |  SORT UNIQUE         |              |     2 |   148 |    36  (59)|
|   2 |   UNION-ALL          |              |       |       |            |
|*  3 |    HASH JOIN         |              |     1 |    74 |    17  (12)|
|   4 |     TABLE ACCESS FULL| SMALL_TABLE  |  1879 | 48854 |    14   (8)|
|*  5 |     TABLE ACCESS FULL| BIG_TABLE    |     4 |   192 |     3  (34)|
|*  6 |    HASH JOIN         |              |     1 |    74 |    17  (12)|
|   7 |     TABLE ACCESS FULL| SMALL_TABLE  |  1879 | 48854 |    14   (8)|
|*  8 |     TABLE ACCESS FULL| BIG_TABLE    |     4 |   192 |     3  (34)|
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."CATEGORY"="B"."FROM_CAT" AND
              "A"."SITE_ID"="B"."SITE_ID")
   5 - filter("A"."SALE_END">=SYSDATE@!)
   6 - access("A"."CATEGORY2"="B"."FROM_CAT" AND
              "A"."SITE_ID"="B"."SITE_ID")
   8 - filter("A"."SALE_END">=SYSDATE@!)

初看这个PLAN好像不如第一个PLAN，因为执行了两次BIG_TABLE的FTS，但是让我们在来看看HASH TABLE的结构

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
### Hash table ###
# NOTE: The calculated number of rows in non-empty buckets may be smaller
#       than the true number.
Number of buckets with   0 rows:       9306
Number of buckets with   1 rows:       5310
Number of buckets with   2 rows:       1436
Number of buckets with   3 rows:        285
Number of buckets with   4 rows:         43
Number of buckets with   5 rows:          4
Number of buckets with   6 rows:          0
Number of buckets with   7 rows:          0
Number of buckets with   8 rows:          0
Number of buckets with   9 rows:          0
Number of buckets with between  10 and  19 rows:          0
Number of buckets with between  20 and  29 rows:          0
Number of buckets with between  30 and  39 rows:          0
Number of buckets with between  40 and  49 rows:          0
Number of buckets with between  50 and  59 rows:          0
Number of buckets with between  60 and  69 rows:          0
Number of buckets with between  70 and  79 rows:          0
Number of buckets with between  80 and  89 rows:          0
Number of buckets with between  90 and  99 rows:          0
Number of buckets with 100 or more rows:          0
### Hash table overall statistics ###
Total buckets: 16384 Empty buckets: 9306 Non-empty buckets: 7078
Total number of rows: 9232
Maximum number of rows in a bucket: 5
Average number of rows in non-empty buckets: 1.304323

这就是我们所需要的Hash Table，最长的链表只有五行数据。

整个SQL的执行时间从三四个小时缩短为16分钟，大大超出了developer的预期。

这个SQL单纯从PLAN上很难看出问题所在，需要了解Hash Join的机制，进行更深一步的分析。

source:http://www.itpub.net/thread-955209-1-1.html

转载于:https://www.cnblogs.com/andy6/p/7502059.html

全数字锁相环基本原理讲解
qq_43660232的博客
03-16 6626
全数字锁相环历史及其研究简介
锁相环原理（PLL）
王敏
04-07 9077
锁相环原理（PLL） 文章目录锁相环原理（PLL）1、锁相环电路原理2、锁相环基本构成3、锁相环理论计算4、matlab实现QPSK锁相环解调 1、锁相环电路原理 在通信机等所使用的振荡电路，其所要求的频率范围要广，且频率的稳定度要高。无论多好的LC振荡电路，其频率的稳定度，都无法与晶体振荡电路比较。但是，晶体振荡器除了可以使用数字电路分频以外，其频率几乎无法改变。如果采用PLL(PhaseLoc...
PLL
云，风扬起的地方
08-03 1299
锁相环路（记为PLL）是一个自动相位控制系统（又称APC），它能使受控震荡器的频率和相位均与输出信号保持确定的关系。目前锁相环路在在许多技术领域获得了广泛的应用，在模拟与均与输出信号保持确定的关系。 锁相环路是由鉴相器(简称 PD)、环路滤波器（简称 LPF或LF）和压控振荡器（简
手把手教你打电赛（开源）--单相锁相环原理与仿真验证
qq_37690283的博客
06-25 1637
本文研究了基于二阶广义积分器（SOGI）的单相锁相环设计。针对单相系统缺乏正交分量的问题，采用SOGI作为正交信号发生器来构造虚拟正交信号。文章详细分析了SOGI的结构特性、传递函数及其离散化实现方法，并通过MATLAB仿真验证了其鲁棒性。结果表明，SOGI-QSG能够有效滤除谐波干扰，保持输入与输出信号的正交关系。仿真测试证实该锁相环具有快速准确的锁相性能。文中给出了关键参数整定方法和代码实现细节，并提供了开源仿真文件供读者参考。
锁相环原理和应用
热门推荐
u010585135的专栏
11-26 2万+
1、锁相环名称是锁相，而锁相的前提是锁频，因为频率不同的信号，是无法保持恒定的相位差的。 2、此外，锁相环是一个闭环跟踪系统，如果将输入信号M分频，输出信号就跟随分频后的信号，即M分频输出；如果将反馈信号N分频，输出信号就N倍频。利用这一点，可以产生分频信号、倍频信号及分数倍频信号。 以下摘自：http://blog.sina.com.cn/s/blog_4a248863010005mc.ht
锁相环（PLL）的原理
weixin_53126442的博客
04-28 7255
压控振荡器能够根据环路滤波器输出电压的控制，将振荡器的频率向输入信号的频率靠拢，直到两者的频率相同，使得VCO的输出信号和输入信号的相位保持一定，从而达到相位锁定。在实际电路中，压控元件一般由变容二极管构成，环路滤波器输出的电压加到压控振荡器回路上的变容二极管上，使得变容二极管的结电容发生改变，从而使振荡器的频率发生改变。锁相环在工作过程中，当输出信号的频率与输入信号的频率相等时，输出电压与输入电压保持固定的相位差值，即输出电压与输入电压相位被锁定，此即为锁相环名称的由来。
锁相环（PLL）基本原理
nanxiqingyu的博客
07-09 1万+
偏移载波频率 fo 指定频率 fm 下1 Hz带宽内功率与频率 fo 振荡器信号功率之比（相对于载波功率的功率谱密度），单位：dBc/Hz。
锁相环的组成和工作原理
oyyj42的专栏
06-25 7339
转自： 1．锁相环的基本组成 许多电子设备要正常工作，通常需要外部的输入信号与内部的振荡信号同步，利用锁相环路就可以实现这个目的。 锁相环路是一种反馈控制电路，简称锁相环（PLL）。锁相环的特点是：利用外部输入的参考信号控制环路内部振荡信号的频率和相位。 因锁相环可以实现输出信号频率对输入信号频率的自动跟踪，所以锁相环通常用于闭环跟踪电路。锁相环在工作的过程中，当输出信
锁相环(PLL)的工作原理
胡图图
04-19 2万+
1．锁相环的基本组成许多电子设备要正常工作，通常需要外部的输入信号与内部的振荡信号同步，利用锁相环路就可以实现这个目的。 锁相环路是一种反馈控制电路，简称锁相环（PLL,Phase-Locked Loop）。锁相环的特点是：利用外部输入的参考信号控制环路内部振荡信号的频率和相位。 因锁相环可以实现输出信号频率对输入信号频率的自动跟踪，所以锁相环通常用于闭环跟踪电路。锁相环在工作的过程中，...
锁相环PLL的工作原理
qq_43418840的博客
11-26 4936
一、PLL和DLL的区别 （一）、PLL原理 PLL（Phase Locked Loop），生成时钟的核心部分是压控振荡器（Voltage-Controlled Oscillator，VCO）。它是可根据输入的电压调整输出频率的振荡器，如下图所示。 它的基本原理是通过负反馈形成闭...
锁相环PLL --原理浅析
weixin_46151516的博客
03-28 2万+
1.什么是锁相环 锁相环电路是使一个特殊系统跟踪另外一个系统，更确切的说是一种输出信号在频率和相位上能够与输入参考信号同步的电路，它是模拟及数模混合电路中的一个基本的而且是非常重要的模块。 2.锁相环的基本理论 2.1 锁相环的工作原理 锁相环作为一个系统，主要包含三个基本模块：鉴相器(Phase Detector：PD)、低通滤波器(LowPass Filter：LPF)，亦即环路滤波器(L00P Filter：LF)，和压控振荡器(Voltage Controlled Oscillator：VCO)。
锁相环（PLL）基本原理.pdf
05-25
锁相环(PLL)基本原理,ADI的重要技术文档。结构清晰，内容很经典。
PLL 锁相环基本原理
10-10
PLL 锁相环基本原理，ADI官方MT-086锁相环(PLL)基本原理，中文版，写的非常详细，有利于理解相关理论。
PLL原理讲义 一个典型的锁相环（PLL）系统
06-09
一个典型的锁相环（PLL）系统，是由鉴相器（PD），压控荡器（VCO）和低通滤波器（LPF）三个基本电路组成，如图1， 从表1可知，如果输入端A和B分别送 2π 入占空比为50%的信号波形，则当两者 存在相位差θ时，输出端F的波形的 占空比与θ有关，见图3。将F输出波 形通过积分器平滑，则积分器输出波形 的平均值，它同样与θ有关，这样，我 们就可以利用异或门来进行相位到电压 θ 的转换，构成相位检出电路。于是经积 图3 分器积分后的平均值（直流分量）为： U U = Vdd * θ/  (1)
pll.zip_PLL_pll原理_锁相环
09-20
PLL（Phase-Locked Loop，锁相环）是一种...综上所述，PLL锁相环技术是电子工程中的重要概念，其原理和应用涉及了信号处理、频率合成等多个领域。理解并掌握锁相环的工作机制对于从事相关工作的工程师来说至关重要。
电机控制领域凸极永磁同步电机高频注入技术及转子锁相环PLL仿真的解析与文献指导
最新发布
08-10
使用场景及目标：适用于希望深入了解凸极永磁同步电机高频注入技术和转子锁相环PLL原理的人群，旨在提升他们对该技术的理解和实际操作能力。 其他说明：文中还列举了一些有助于深入学习的相关书籍和学术论文，为读者...
电子工程领域锁相环PLL电路设计教程：800MHz1GHz输出的新手友好指南
07-29
文档详细讲解了PLL的基本原理及其各个组件的功能，特别是采用了Ring_VCO结构的电压控制振荡器(VCO)，并给出了具体的参数设置，如输入参考频率20MHz、分频器比例40-50、电荷泵电流20uA以及SMIC 55nm工艺。此外，还...
凸极永磁同步电机高频注入技术及转子锁相环PLL仿真的解析与文献指南
05-24
使用场景及目标：适用于希望深入了解凸极永磁同步电机高频注入技术和转子锁相环PLL原理的研究人员和技术人员。目标是提高对这些技术的理解，优化电机控制系统的设计和性能。 其他说明：本文不仅提供了理论知识，还...
锁相环(PLL)基本原理
cy413026的博客
10-11 6731
锁相环(PLL)电路存在于各种高频应用中，从简单的时钟净化电路到用于高性能无线电通信链路的本振(LO)，以及矢量网络分析仪(VNA)中的超快开关频率合成器。本文将参考上述各种应用来介绍PLL电路的一些构建模块，以指导器件选择和每种不同应用内部的权衡考虑，这对新手和PLL专家均有帮助。本文参考ADI公司的ADF4xxx和HMCxxx系列PLL和压控振荡器(VCO)，并使用ADIsimPLL（ADI公司内部PLL电路仿真器）来演示不同电路性能参数。
锁相环
03-09
### 锁相环原理 锁相环（PLL, Phase-Locked Loop）是一种反馈控制电路，其核心功能在于使输出信号的频率和相位与输入参考信号同步。该电路广泛应用于通信、电子设备以及计算机领域，在这些场景下起到至关重要的作用[^2]。 #### 构成部分及其工作方式 锁相环主要由三个组件构成： - **鉴相器**：负责检测两路信号之间的相位差异，并据此产生误差电压。 - **低通滤波器**：平滑来自鉴相器的脉冲序列，转换为连续变化的直流电平，从而去除高频噪声成分。 - **压控振荡器 (VCO)**：接收经过滤波后的电压作为调制信号，调整自身的震荡频率以匹配目标值。 当系统启动时，如果VCO初始频率\(W_o\)不同于期望的目标频率\(W_i\), 则会经历一个被称为“捕捉”的动态调节阶段；在此期间，通过不断修正直到两者趋于一致并最终达到稳定的锁定状态。此时，即使外界条件有所波动，只要变动幅度不大，PLL仍能保持良好的跟随特性，即所谓的跟踪性能[^5]。 ### 实现倍频的方法 为了实现倍频操作，可以在PLL架构内加入额外的功能单元——分频计数器。具体做法是在反馈路径中插入这样一个元件，使得送回至鉴相器端口处的实际比较对象成为原有时钟周期的一个整数倍版本。如此一来，尽管表面上看似乎只是改变了反馈比例关系而已，但实际上却巧妙地实现了对原始输入频率的有效放大效果[^1]。 ```cpp // C++伪代码展示简单的PLL倍频逻辑框架 class PLL { public: void setMultiplier(int multiplier); private: int _multiplier; // 设置倍频系数 double adjustFrequency(double inputFreq){ return inputFreq * _multiplier; } }; ``` ### 应用实例 除了上述提到的基础用途外，PLL还拥有众多其他应用场景，比如但不限于： - 提升本地振荡源稳定性； - 执行复杂的频率合成功能； - 支持高速数据传输链路上的数据恢复机制等。 值得注意的是，虽然大多数情况下PLL表现良好，但在某些极端条件下可能会出现失锁现象，这通常是因为输入信号质量太差或是设计参数不合理所致[^3]。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值