Oracle临时表空间不足和批处理缓慢问题探讨

谭红斌

（驻马店职业技术学院，河南 驻马店 463000）

Oracle临时表空间不足和批处理缓慢问题探讨

谭红斌

（驻马店职业技术学院，河南 驻马店 463000）

数据库系统出现问题时，会导致整个系统不稳定．文章分析了Orcale临时表空间不足和批量处理时运行速度缓慢的原因，并给出了解决办法．

SQL；hash join；临时表空间

1 问题的提出

笔者所在学院的学生管理系统后台使用的是Oracle数据库．在每天凌晨批处理期间，ALERT日志频繁报出临时表空间不足的告警信息，导致批量程序运行失败．在对临时表空间进行多次扩容后（从40 GB增加到 60 GB，再增加到 100 GB），问题仍然无法有效地解决．即使在批量执行成功的时候，也需要执行很长时间才能完成．为了解决此问题，急需对该问题进行全面排查，以找到解决办法．

2 问题的分析和解决

根据以往的经验，笔者分析认为造成这一问题的原因可能是服务器硬件存在性能问题、操作系统设置有问题或者是应用本身的 SQL语句编写有问题．为此，笔者衡量服务器的运算能力以及该数据库的负载，使用测试工具测试，并未发现服务器及Oracle本身的瓶颈问题．因此，把问题定位在应用逻辑方面，并做了如下详细的测试．

2.1 编写记录使用临时表空间SQL语句的脚本

查看数据库 AWR报告，发现该报告未能准确体现出占用临时表空间最高的SQL语句．为此，笔者编写了一个记录使用临时表空间SQL语句的shell脚本，用来记录使用临时表空间最高的SQL语句，并将其保存在特定的表中．该脚本的部分SQL语句编写如下：

2.2 抓取最消耗临时表空间的SQL语句

在临时表空间已增加到 100 GB的情况下，重新进行批量运行，出现临时表空间不足错误时，笔者根据编写的监控脚本，顺利抓取到了最消耗临时表空间的两个语句．其中，语句1消耗约40 GB临时表空间，语句2消耗约60 GB临时表空间．由于语句1和语句2的语法结构完全一致，只是批量处理的数据不一样，故这两个语句可用同一语句描述，具体如下：

2.3 分析执行计划

分析语句发现，执行计划使用了hash join连接技术，当驱动表无法在内存中装载时，驱动表一次或多次被置换到临时表空间，发生one-pass或muti-pass的现象，导致临时表空间的消耗和执行效率的低下．由于语句1和2结构完全一致，因此对语句1分析和优化即可，根据监控脚本所抓取到的SQL语句及其id，我们得到了如图1所示的执行计划信息．由图1可以看出，该执行计划的问题在于出现了“merge join cartesian”合并连接笛卡尔乘积，而其乘积结果作哈希连接的驱动表．由于PGA（程序全局区）中的hash area无法完全装载该表，导致出现 one-pass、muti-pass，大量占用临时表空间，程序运行效率低下．该合并连接笛卡尔乘积将产生约1 700万条记录，而PGA大小为 1 628 M，hash area最多能使用 1628*5% = 80 M，显然80 M的hash area无法装载更多记录的运算结果，继而大量占用临时表空间，导致程序运行效率低下．

SQL优化器CBO之所以选择合并连接笛卡尔乘积，原因在于CBO评估合并连接笛卡尔乘积返回的结果为1行，检查设置该语句的三个分区表的统计信息，发现统计信息为0行．至此，问题已经清楚．由于批量的流程为数据加载、运算、数据全删除．因此系统自动统计信息时往往在批量数据全部删除后进行，导致采集的统计信息的为0行，继而CBO选择了合并连接笛卡尔乘积，导致了错误的执行计划、大量的临时表空间占用和性能低下的执行计划．

2.4 重写SQL后进行测试

根据每个系统每日数据并不大的情况，理想的执行计划应为：子查询中的D表和E表做hash join，所产生的结果集做为另外一个hash join的probe表去探测A表．将复合记录的结果返回．由此，子查询不应该做view merge．我们将语句改写，限制数据库CBO优化器做出错误的执行计划选择．

语句重写后，在很短时间内即可完成，同时，未出现对临时表空间的消耗现象．

以下为修改后语句的部分内容，其执行计划为笔者所预期的理想路径，执行计划信息如图2所示．

图2 修改后的语句执行计划信息

2.5 问题的解决

语句在修改前在执行时间超过 1个小时仍未结束，而经过语句重写后，测试显示不到1分钟即可完成，同时也消除了对临时表空间的过量消耗的问题．

3 结语

根据以上分析，笔者认为要提高SQL语句的编写效率，简化SQL的处理逻辑，在应用编写时应考虑到表的增量以及相应的执行计划，在关键SQL语句中使用显式执行计划．系统关键SQL语句应考虑：

(1) 使用 hint稳定执行计划，通过在子查询中加入no_merge的hint防止CBO优化器做view merge．同时，由于子查询内部的谓词中已有分区键，且CBO可有效使用分区去除，故通过加入no_index的hint可使对单个分区扫描的索引范围更加有效．

(2) 基于批量处理的流程，可在调用批量程序运行的shell脚本，数据加载完毕，并对相关的三张表进行统计信息的收集之后，再开始做merge的批量操作．

[1] 滕永昌．Oracle数据库系统管理[M]．北京：清华大学出版社，2003：80―96．

[2] 郑阿奇．ORACLE实用教程[M]．北京：电子工业出版社，2009：120―160．

[3] 文平．ORACLE大型数据库系统在AIXUNIX上的实战详解[M]．北京：电子工业出版社，2010：66．

[4] 王彬．Oracle IIg基础与提高[M]．北京：电子工业出版社，2009：230―261．

[5] 肖平．基于Oracle应用服务器的Web开发技术[M]．北京：清华大学出版社，2010：90．

TP392

A

1006-5261(2012)02-0036-03

2012-03-29

谭红斌（1975―），男，河南西平人，讲师．

〔责任编辑 牛建兵〕