一种基于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.--），男，汉族，安徽肥东人，本科，讲师，研究方向：计算机网络，数据库，数据分析。