转--一次HASH JOIN 临时表空间不足的分析和优化思路

最新推荐文章于 2023-06-28 22:44:56 发布
最新推荐文章于 2023-06-28 22:44:56 发布
阅读量328 收藏 1
点赞数
分类专栏： Oracle 文章标签： 大数据
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/juji10/article/details/84220656
版权

最近遇到一个语句, 只要一执行这个语句就会出现报错临时表空间不足，回想一下在语句中用到临时表空间无非是大量的SORT和HASH，然后通过执行计划查看如下：
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3959216560

-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
| 1 | HASH UNIQUE | | 1 | 4 | 0 |00:00:00.01 | | | |
| 2 | NESTED LOOPS OUTER | | 1 | 4 | 0 |00:00:00.01 | | | |
|* 3 | HASH JOIN | | 1 | 4 | 0 |00:00:00.01 | 703K| 703K| |
| 4 | NESTED LOOPS OUTER | | 1 | 4 | 2524K|00:02:17.15 | | | |
| 5 | NESTED LOOPS | | 1 | 4 | 2524K|00:01:34.23 | | | |
|* 6 | HASH JOIN | | 1 | 4 | 2524K|00:00:53.84 | 2047M| 29M| 55M (1)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 7 | HASH JOIN | | 1 | 4 | 11M|00:01:00.03 | 2797K| 1148K| 3144K (0)|
|* 8 | TABLE ACCESS BY INDEX ROWID | T_COMMISSION_FEE | 1 | 25517 | 31948 |00:00:00.16 | | | |
|* 9 | INDEX RANGE SCAN | PK_T_COMMISSION_FEE | 1 | 25520 | 31948 |00:00:00.03 | | | |
|* 10 | HASH JOIN | | 1 | 33714 | 11M|00:00:12.24 | 1299K| 1299K| 1925K (0)|
|* 11 | TABLE ACCESS FULL | T_GL_BIZ_INTERFACE | 1 | 7889 | 12414 |00:00:00.10 | | | |
|* 12 | TABLE ACCESS FULL | T_BIZ_ACCOUNTING_INFO | 1 | 32696 | 63896 |00:00:00.27 | | | |
| 13 | VIEW | VW_NSO_1 | 1 | 3 | 2 |00:00:00.01 | | | |
|* 14 | FILTER | | 1 | | 2 |00:00:00.01 | | | |
|* 15 | CONNECT BY WITH FILTERING | | 1 | | 2 |00:00:00.01 | 9216 | 9216 | 8192 (0)|
| 16 | TABLE ACCESS BY INDEX ROWID| test| 1 | | 1 |00:00:00.01 | | | |
|* 17 | INDEX FULL SCAN | tes123| 1 | 1 | 1 |00:00:00.01 | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 18 | NESTED LOOPS | | 2 | | 1 |00:00:00.01 | | | |
| 19 | BUFFER SORT | | 2 | | 2 |00:00:00.01 | 9216 | 9216 | 8192 (0)|
| 20 | CONNECT BY PUMP | | 2 | | 2 |00:00:00.01 | | | |
|* 21 | INDEX RANGE SCAN | Ttest123| 2 | 3 | 1 |00:00:00.01 | | | |
| 22 | TABLE ACCESS FULL | test| 0 | 3 | 0 |00:00:00.01 | | | |
| 23 | TABLE ACCESS BY INDEX ROWID | test| 2524K| 1 | 2524K|00:00:30.72 | | | |
|* 24 | INDEX UNIQUE SCAN | test123| 2524K| 1 | 2524K|00:00:12.32 | | | |
| 25 | TABLE ACCESS BY INDEX ROWID | Ttt| 2524K| 1 | 2524K|00:00:31.35 | | | |
|* 26 | INDEX UNIQUE SCAN | tet1| 2524K| 1 | 2524K|00:00:12.00 | | | |
| 27 | TABLE ACCESS FULL | test31| 0 | 84 | 0 |00:00:00.01 | | | |
| 28 | TABLE ACCESS BY INDEX ROWID | tes1234| 0 | 1 | 0 |00:00:00.01 | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 29 | INDEX UNIQUE SCAN | PK_T_CONTRACT_MASTER | 0 | 1 | 0 |00:00:00.01 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------

 

第6步太吓人了，我这个语句没有运行完因为会报错，这个状态应该是在故障点的，可以看到需要的HASH构造区域为2G，仔细分析下这个执行计划。
其实它是一个HASH JION和一个VIEW做的HASH JION，观察一下行数，就是11M（11*1024*1024）行和2行进行的一个HANSH JION，但是很奇怪的是执行
计划选择了大数据集为构造输入，构造输入在PGA的工作区的HASH_AREA_SIZE中建立一个HASH表，如果内存不够把HASH表存储在TMEP表空间里面，
而选择了小的数据集来作为探测输入，探测输入会通过连接条件通过HASH函数和HASH表进行比对，如果存在则输出，不存在则丢弃。下面我们通过
一个小小的试验来说明：
建立表
SQL> desc test;
Name Type Nullable Default Comments
----- ------------ -------- ------- --------
员工代码 NUMBER(20) Y
员工中文名 VARCHAR2(50) Y
员工英文名 VARCHAR2(50) Y
归属机构 VARCHAR2(50) Y
岗位名称 VARCHAR2(50) Y
SQL> select count(*) from test;

COUNT(*)
----------
3399680
这个是大数据集
建立表
SQL> desc test2
Name Type Nullable Default Comments
---- ---------- -------- ------- --------
员工代码 NUMBER(20) Y
工资 NUMBER(10) Y
SQL> select count(*) from test2;

COUNT(*)
----------
3
现在试验开始
首先执行语句
SQL> select count(*) from test a,test2 b where a.员工代码=b.员工代码;

COUNT(*)
----------
15360

已用时间: 00: 00: 09.12

执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=6098 Card=1 Bytes=
14)

1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=6098 Card=13197 Bytes=184758)
3 2 TABLE ACCESS (FULL) OF 'TEST2' (TABLE) (Cost=3 Card=3
Bytes=21)

4 2 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=6063 Card=
3409202 Bytes=23864414)


可以看到这个时候小数据集作TEST2为了构造输入，在执行期间通过语句
select operation_id,operation_type,actual_mem_used,tempseg_size,tablespace
from v$session s,v$sql_workarea_active w
where s.sid=w.sid
and S.SID=151;
得出的结果如下：
OPERATION_ID OPERATION_TYPE ACTUAL_MEM_USED TEMPSEG_SIZE TABLESPACE
------------ ---------------------------------------- --------------- ------------ -------------------------------
2 HASH-JOIN 183296
HASH JION用于构造HASH表使用内存183K没有使用临时表空间。

现在我们通过HINT来改变大数据集和小数据集的顺序，执行语句如下：
SQL> select /*+ leading(a) use_hash(a b) */ count(*) from test a,test2 b where a.员工代码=b.员工代码;

COUNT(*)
----------
15360

已用时间: 00: 00: 13.82

执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=10636 Card=1 Bytes
=14)

1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=10636 Card=13197 Bytes=184758)
3 2 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=6063 Card=
3409202 Bytes=23864414)

4 2 TABLE ACCESS (FULL) OF 'TEST2' (TABLE) (Cost=3 Card=3
Bytes=21)
这个时候TEST大数据集是构造输入，同样在执行期间通过语句得出结果
OPERATION_ID OPERATION_TYPE ACTUAL_MEM_USED TEMPSEG_SIZE TABLESPACE
------------ ---------------------------------------- --------------- ------------ -------------------------------
2 HASH-JOIN 1205248 18874368 TEMP
可以看到结果不同了，使用1.2M内存，使用临时表空间近19M。
同时如果我们关注下以下信息：
SQL> select * from v$sysstat where NAME LIKE '%workarea executions%';

STATISTIC# NAME CLASS VALUE STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
294 workarea executions - optimal 64 3525 3211650785
295 workarea executions - onepass 64 4 798730793
296 workarea executions - multipass 64 0 3804491469
现在我们运行
SQL> select /*+ leading(a) use_hash(a b) */ count(*) from test a,test2 b where a.员工代码=b.员工代码;

COUNT(*)
----------
15360
在运行
SQL> select * from v$sysstat where NAME LIKE '%workarea executions%';

STATISTIC# NAME CLASS VALUE STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
294 workarea executions - optimal 64 3525 3211650785
295 workarea executions - onepass 64 5 798730793
296 workarea executions - multipass 64 0 3804491469

可以看到workarea executions - onepass 增加了1说明我们进行了一次物理交换才完成了探测（还好没有多次）。

有了上面的试验，我的语句应该就可以通过HINT来改变小数据集为构造输入，而大数据集为探测输入来改变临时表空间不足的问题，同时提高性能。

juji10
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
一次HASH JOIN 临时表空间不足的分析和优化思路
cri5768的博客
02-15 415
（原创转载请注明出处） 最近遇到一个语句, 只要一执行这个语句就会出现报错临时表空间不足，回想一下在语句中用到临时表空间无非是大量的SORT和HASH，然后通过执行计划查看如下： PLAN_TABLE_OUTPUT-...
HIVE 在执行大量数据JOIN的时候，容易产生内存不足的情况
weixin_30536513的博客
05-24 935
情况 很多时间遇到"Caused by: java.lang.OutOfMemoryError: Java heap space" Caused by: java.lang.OutOfMemoryError: Java heap space 可能会有下面错误"Reducer tasks of hive job fails with Out Of Memory erro...
Hash join semi带来的temp tablespace使用过度问题-Part1
cser3000的博客
09-25 249
前日，Production DB N突然反应变慢(RHEL4UP4,9208,64bit,MTS mode)用top –d 1 察看，发现CPU usage大约在40%~70%间浮动，IOWAIT大约在4%~9%左右。Sys使用...
oracle报内存不足,oracle 内存不足处理
weixin_31528001的博客
04-07 2633
alter日志TNS-12535: TNS:operation timed outns secondary err code: 12606nt main err code: 0nt secondary err code: 0nt OS err code: 0Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.93.33)(PORT=52614))WA...
Oracle临时表空间不足和批处理缓慢问题探讨.pdf
10-09
6. Hash Join 的作用：Hash Join 可以提高 SQL 语句的执行效率，但其也可能会消耗大量的临时表空间。 7. Oracle 数据库的性能优化：Oracle 数据库的性能优化需要从多方面入手，包括服务器硬件性能、操作系统设置、...
oracle报错（ORA-00600）问题处理
09-10
例如，如果错误是由于哈希连接（Hash Join）导致的，可以使用以下命令临时禁用哈希连接： ```sql ALTER SESSION SET "_hash_join_enabled"=false; ``` 类似地，如果错误是由于哈希分组（Hash Group By）造成的，...
ORACLE-排序优化[归纳].pdf
10-11
4. 创造并监测临时表空间：合理分配和管理临时表空间，避免空间不足导致的排序问题。 了解在内存中执行排序的数量有助于优化资源分配，通过监控 PGA 的使用情况，可以发现排序操作对内存的需求，进而调整相关参数。...
Oracle 表三种连接方式使用介绍(sql优化)
09-10
Hash Join是针对大数据集连接的常见选择，尤其在处理两个大型表或一个大型表和一个小表的连接时。它首先将小表加载到内存中的哈希表中，然后扫描大表，对每一行执行哈希运算并查找匹配项。如果小表无法完全放入内存...
无法为oracle分配临时,ORACLE临时段表空间不足,修改表空间大小
weixin_39810196的博客
04-09 429
备份Java代码1. 问题一ORA-01652:unable to extend temp segment by num in tablespace name2. ORA-01652:unable to extend temp segment by num in tablespace name3. 产生原因：ORACLE临时段表空间不足，因为ORACLE总是尽量分配连续空间，一但没有足够的可分配空...
达梦数据库：“超出全局hash join空间”问题处理
龙山溪笔记
12-25 6074
--项目中经常出现HJ_BUF_GLOBAL_SIZE设置不当造成应用报错的问题，详细信息如下： dm.jdbc.driver.DMException: 超出全局hash join空间,适当增加HJ_BUF_GLOBAL_SIZE at dm.jdbc.driver.DBError.throwException(DBError.java:657) at dm.jdbc.a.b.p.H(MSG.java:582) at dm.jdbc.a.b.p.E(MSG.java:542) ......
oracle 临时表空间使用情况
weixin_34273481的博客
07-02 396
今天用户那边执行一个很简单的SQL，输出结果集也才几万条，涉及三表，最大也才100万数据量，结果却报了表空间不足的情况，理论来说，这样的SQL怎么也不应该吃这么多临时表空间。 查询临时表空间的使用情况： select USERNAME,SESSION_NUM,SQL_ID,TABLESPACE,SEGTYPE,BLOCKS*8192/1024/1024||' MB' as MB from v$s...
达梦8通过多个left join on 查询往表里查数据报超出全局hash join 空间，适当增加hj_buf_global_size的错误
qq_33674508的博客
08-27 2605
达梦8通过多个left join on 查询往表里查数据报超出全局hash join 空间，适当增加hj_buf_global_size的错误 解决方法:数据库里执行sp_set_para_value(1，‘hj_buf_global_size’，1000)
达梦数据库超出全局hash join空间（超出全局hash join空间,适当增加HJ_BUF_GLOBAL_SIZE的错误）
qq_41512902的博客
09-28 3659
达梦数据库超出全局hash join空间（超出全局hash join空间,适当增加HJ_BUF_GLOBAL_SIZE的错误）
一种特殊场景下的HASH JOIN的优化为NEST LOOP.
weixin_33700350的博客
02-16 323
应用场景： 有如下的SQL: select t.*, t1.ownerfrom t, t1where t.id=t1.id; 表t ,t1的数据量比较大，比如200W行.但是两张表能关联的行数却很少，比如不到50条. T1表的行比较宽,且在id列上有单列索引. 这里限制t1的索引为单列索引是为了让访问t1表数据的时候要通过rowid回表.因为在实际应用中,我们通常会需要t1表的几个列，但是...
使用hash join 优化 sql
如果有一天
04-12 216
低效： [code="sql"]SELECT a.*, b.object_name FROM TEST_OBJECT a, TEST_OBJ1 b WHERE SUBSTR(a.object_name, 1, LENGTH(b.object_name)) = b.object_name;[/code] 実行計画 -----------------...
Oracle HASH JOIN 引起的TEMP爆满分析总结
tworain
06-28 491
一次不合理的left join 导致的TEMP爆满的经历
29 超大表与超小表HASH JOIN优化方法--优化主题系列
leo0805的博客
01-11 2426
select * from a,b where a.id=b.id; a表100GB b表50MB 如何优化？？   select *,(select * from b wherea.id=b.id) from a;  --标量子查询 A表100GB 是不是几十亿的数据？？如果是标量子查询 A返回一条 B就会扫描1次 相当于B被扫描几十亿次 即使B走索引 走索引至少读3个块 然后回表一个
哈希连接（Hash Join（HJ））-3
hayata的专栏
01-10 854
今天这章主要讨论下Hash Join的性能和驱动表这两方便的内容。先讨论下这个Hash Join性能的问题吧。上截图了！！！！                                        通过上面的例子可以看到，这个优化
oracle临时表空间
最新发布
10-20
Oracle临时表空间是用来管理数据库排序操作以及存储临时表、中间排序结果等临时对象的空间。当Oracle需要进行排序操作时，如果PGA中sort_area_size大小不够，就会将数据放入临时表空间里进行排序。临时表空间在数据库中的一些操作中会被使用，例如CREATE INDEX、ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、UNION ALL、INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等。当操作完成后，系统会自动清理临时表空间中的临时对象，但实际上占用的磁盘空间并没有真正释放，只是被标记为空闲，可以重用。为了释放没有使用的空间，可以通过收缩临时表空间来实现。在Oracle 11g中，新增了收缩临时表空间的功能。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值