笔者接到一个做开发上线的兄弟电话，说正在试运行的系统存储过程突然变慢，而且偶然发现数据库的Temp表空间突然增加到20多G。这位兄弟不知道是不是与存储过程突然变慢有关，而且应该如何处理。
1、从Temporary Tablespace谈起
表空间（Tablespace）、段对象（Segment）、分区（Extent）和数据块（Block）是Oracle逻辑层面上最重要的几个概念。其中，表空间是各个逻辑层面顶层概念，也是与Oracle物理结构文件可以建立关系的重要环节。
SQL> select tablespace_name, contents, logging from dba_tablespaces;
TABLESPACE_NAME CONTENTS LOGGING
------------------------------ --------- ---------
SYSTEM PERMANENT LOGGING
UNDOTBS1 UNDO LOGGING
SYSAUX PERMANENT LOGGING
TEMP TEMPORARY NOLOGGING
USERS PERMANENT LOGGING
EXAMPLE PERMANENT NOLOGGING
6 rows selected
表空间从类型上有若干分类的方式，一种是按照“文件file”的大小，区别为small file tablespace和big file tablespace。另一种是按照表空间用途而定的。此种分类方法可以将表空间划分为持久化表空间（Permanent Tablespace）、Undo表空间和临时表空间（Temporary Tablespace）。
Temporary临时表空间是Oracle一种内部空间调控的产物。根据Oracle官方文档中的介绍，Temporary表空间主要是针对session会话自身的操作使用的。
当一个会话通过SQL或者PL/SQL将数据集合获取，进行大面积的sort或者group by操作时，会话会严重的消耗PGA资源。PGA是针对会话自身特有信息的一块内存区域，用于保存会话自身特有、无法与其他会话共享的信息。如果数据集很大，这样对内存PGA的资源消耗就会很大。Oracle在这个时候，就会采用类似操作系统虚拟内存技术的方法，从硬盘上借一块空间给PGA置换使用，缓解PGA的不足。
在使用Temporary表空间的时候，是系统自动完成的临时段segment对象创建和销毁。这个过程对用户会话而言是透明的。
临时表空间对应的文件就是临时文件temp file，在Oracle中可以使用dba_temp_files视图进行查询。
SQL> col file_name for a50;
SQL> select file_name, bytes/1024/1024, AUTOEXTENSIBLE from dba_temp_files;
FILE_NAME BYTES/1024/1024 AUTOEXTENSIBLE
-------------------------------------------------- ---------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF 123YES
通常，在系统创建的时候，Oracle会创建临时表空间Temp作为系统默认临时表空间Temporary Tablespace。而且，通常Temp表空间都是设置为支持自动拓展的。
2、问题分析
回到那位兄弟的问题，简单归纳出来就是“系统中出现批作业缓慢的现象，发现临时表空间暴增”。
从上面我们对于Temp表空间的分析介绍，我们可以初步认为临时表空间的暴增是一个结果，而不是一个原因。或者说，至少是某个原因的结果。
什么意思呢？首先，Oracle文件有自动膨胀autoextend的功能，但是却没有自动缩减的能力。临时表空间容纳的对象，都是系统创建、管理的临时段对象。当会话结束、SQL命令结果返回的时候，临时表空间的内容是可以回收的。所以，Temp表空间的自动膨胀大小结果不是一个累积的效果，而是一个峰值效果。
Temp表空间增加到20G，说明在系统运行的某个阶段，由于会话并发或者其他的原因，引起系统内部排序、分组等PGA高消耗操作剧烈增加，瞬时超过了Temp原有的大小。从而引发Oracle进行Temp表空间的自动拓展，拓展到当前的大小。之后会话操作结束，临时段segment对象被回收，但是文件大小不会重新回收。所以导致了Temp表空间膨胀的事实。
那么，有什么样的原因可能导致出现瞬时Temp空间消耗高峰呢？几种可能的情况如下：
ü 系统本身特点。我们说最大使用Temp表空间的就是sort和group by操作。对一些OLAP、DSS系统而言，无论是进行报表生成还是数据整理汇总析取，都伴随着海量数据的sort或者group by。一般情况下的PGA设置是无法满足如此巨大的空间需求的，必然要消耗相当的Temp表空间。根据笔者的了解，一些数据仓库性质的系统对Temp的消耗达到几十上百G是很常见的；
ü 应用本身对SQL处理量失控。SQL是一种描述性语言，修改一条记录和修改一千万条记录的语句结构可能都是相同的。但是，对系统而言，两者的差异是天壤之别。比如，当我们尝试将获取到数据集合bulk collect到一个数组时候，要关注到可能的数据量规模。如果数据集合较小，一切都好说。但是如果数据集合达到百万级以上，那么瞬时百万条数据全部请求存放在PGA中，进而引起Temp的过量使用；
ü 第三方工具的使用。很多第三方工具，特别是带有数据分析处理的软件，通过专门的业务实体层封装SQL语句。一些时候，这种封装SQL进行连接、汇总时也会带来大量的Temp消耗；
3、问题解决
经过沟通，确认系统发生的变更情况。在最近一段时间，系统安装了BO相关组件，多用户在反复同时生成报表的时候，可能会出现Temp消耗高峰的情况。
同时，观察应用系统的部分代码，存在SQL处理量未受控的情况，建议开发组根据工作进度情况进行重构处理。
至于说特定SQL执行时间过长，通过诊断（或者AWR、ASH）确定了问题SQL，进行执行计划修正后问题解决。
最后说说对当前系统临时表空间的处理。在使用BO的前提下，临时表空间胀大的情况也许是不可避免的。所以建议开发组从几个方面着手：
ü 借助AWR报告，确定究竟是BO造成的临时表空间使用量过高还是应用本身问题；
ü 如果确定是BO本身问题，可以确定一个基本的峰值。将临时表空间文件设置一个增长上限，不要关闭autoextend开关；
ü 如果是应用本身的问题，也不要轻易关闭autoextend开关。因为如果Temp表空间需要资源而无法分配，那么前端应用会产生异常报错；
ü 不断诊断和重构应用代码，加入处理量控制代码，力图做到每次处理的数据量存在可控上限。防止出现瞬时处理量胀大的情况；
4、结论
这个案例，告诉我们两点思考之处：
ü 分清主次因果；数据库诊断调优是一项综合性强的技术。我们看到的大多是问题的表象，甚至是很奇怪的表象。这时候，我们就需要分析问题的根源，理清主次矛盾、孰因孰果，定位到问题的核心；
ü 分清轻重远近；很多性能问题的根源不是某个或者某几个SQL造成的，而是和开发过程中一些习惯和细节问题日积月累起来。这种情况下，修改重构是一个过程，不可能一蹴而就。所以，调优要从远近轻重的角度制定方案。首先让应用跑起来，支持生产。之后才是将问题一点点的解决。Oracle推出的outline等技术也就是这个含义。
本文转载自：http://space.itpub.net/17203031/viewspace-704440