一种基于ORACLE TEMP空间的优化及实践
（1.230601 安徽财贸职业学院 安徽 合肥）（2.100124 中国农业银行软件开发中心 北京)
【摘 要】Oracle数据库作为全球最具影响力的企业级数据库之一，在我国各个信息系统中得到了广泛的应用。由于数据库的性能直接影响信息系统的性能，Oracle数据库的性能优化方法成为了开发人员研究的热点。本文基于某银行系统的性能调优的案例，说明了排除SQL的写法和执行计划后，如何分析Oracle TEMP资源对SQL性能的影响，阐述了一种Oracle性能问题的解决方案。
【关键词】ORACLE；优化；TEMP；性能
一、引言
当前软件研发人员，数据库是必须掌握的知识点。而在数据库中，了解TEMP空间的基本原理，并基于其对问题进行排查、优化是每个软件研发人员必须掌握的技能。
二、案例现象
某银行系统在计算到账户级数据时，数据量及批量时间呈现出逐月、逐年增长的趋势。并且在2018年年末的时候，该系统中计算账户级余额的节点一共运行了24个小时，处理了超过10亿的数据，出现运行时间、处理数据量爆发式增加的现象。该节点的SQL形表示为：Insert into XXX；Select* from A ......Left join M Group by ...
三、案例分析
从上述SQL可以看出，该SQL关联的表非常多，随着各个子表数据量的增长，该SQL所处理的数据量的笛卡尔积也会越来越大。所以需要仔细分析数据库运行时的情况，并找出运行缓慢的原因。
检查该语句运行时的状态。语句如下：
select * from (select sysdate sample_time,s.inst_id,s.sid,s.serial#,
s.status status,s.event, r.sql_textfrom
gv$session s, gv$tempseg_usage b, gv$sql rwhere s.inst_id=b.inst_id and s.inst_id=r.inst_id and s.saddr = b.session_addr
and s.sql_address=r.address and s.sql_hash_value=r.hash_value
order by b.tablespace, b.blocks desc) wherer.sql_text like ‘Insert into XXX%’;
通 过 查 询 ， 发 现 该 语 句 出 现 了 “ e n q : S S -contention”的事件。该事件为数据库等待事件。该事件的出现主要是由于某个实例上的TEMP空间不足，导致该实例向其他实例的DBWR进程申请空间。如果此时其他实例上的DBWR进程繁忙，则会造成TEMP空间释放缓慢，进而会造成批量时间大幅提升。
（二）检查该语句运行时，系统中TEMP资源的使用情况通过上述分析，我们发现该等待事件是由于TEMP资源不足造成的，所以我们需要分析下该语句运行时，系统中TEMP资源的使用情况。下面是查询系统中TEMP资源情况的SQL语句。
set heading off; select '<temp_monitor temp_used>' from dual; --TEMP
set heading on;set numwidth 20;set timing on;withtemp_allocated as(select owner,sum(BYTES) AS TEMP_ALLOCATED from v$temp_extent_map group by owner),temp_used as(select INST_ID,tablespace,
sum(blocks)*8192 AS TEMP_USED from gv$tempseg_usage where 'TEMPORARY'=CONTENTSgroup by INST_ID,tablespace)
select A.owner As Inst_ID, round(A.TEMP_
ALLOCATED/1024/1024/1024,4) As TEMP_ALLOCATED_GB,round(B.TEMP_USED/1024/1024/1024,4) As TEMP_USED_GB,
round(100*B.TEMP_USED/A.TEMP_ALLOCATED,4) AsPercent from temp_allocated A
inner join temp_used B on A.owner=B.INST_ID orderby A.owner;set timing off; set heading off; select'</temp_monitor temp_used>' from dual;
（三）分析系统TEMP资源使用情况
如图1所示，为该语句运行时，系统中TEMP资源的使用情况。
图1 TEMP资源使用情况
从图1中可以发现，系统中TEMP资源的使用率非常高。
此外，由于实例1中的T E M P空间不足，从而向其他实例申请释放空间，导致出现了等待事件。出现“enq: SS -contention”等待事件会导致批量运行时间大幅增加。
四、解决方法
该语句属于INSERT...SELECT...GROUP BY的格式，并且处理的数据量特别大。而GROUP BY语句会占用大量的TEMP空间，所以当该节点运行时会消耗掉系统中大量的TEMP资源。这样极易出现“enq: SS - contention”的数据库等待事件。所以通过上述分析，我们认为可以通过采用减少表关联、INSERT小事务的方式来解决该问题。减少表关联可以减少脚本运行过程中产生的中间结果集，可以减少TEMP空间的使用，降低等待事件发生的概率,可以使节点运行的更加稳定。所以最后对该结点按照客户类型进行了拆分。拆分为对公、对私及其他客户。图2是修改后的伪代码。
图2 修改后的伪代码
五、总结
当一个含有INSERT、GROUP BY或ORDER BY的语句处理的数据量特别大时，会消耗大量的TEMP空间。当一个实例上的语句消耗大量TEMP空间时，可
能会造成本实例上的TEMP空间不
足，从而去向其他实例申请空
间。这样就会出现“enq: SS -
contention”等待事件，使脚本
的运行时间无法预测，从而严重
影响脚本的运行速度。所以对于
这种长交易、大事务的INSERT、
GROUP BY语句，可以对语句进行
拆分，将一个长交易、大事务修
改成几个短交易、小事务。这样
可以减少每个实例上的TEMP空间
的消耗。降低等待事件的出现概
率，使批量时间更加可控。
参考资料：
[1] Oracle编程艺术
[2] Oracle9i Application
Developer’s Guide
作者简介：
葛文龙（1981.--），男，汉族，安徽肥东人，本科，讲师，研究方向：计算机网络，数据库，数据分析。
