temp 表空间暴涨问题

Liuxuebo

前段时间有个问题是关于temp表空间一直暴涨的问题，下面做个实验进行测试一下
首先要明白temp表空间中放的什么东西，还有什么操作会是temp表空间增长。

在select查询中有以下条件会发生数据的排序，分组，在PGA不够的情况下会用到temp
- build index.
- ORDER BY or GROUP BY
- DISTINCT.
- UNION & INTERSECT & MINUS
- Sort-Merge joins.
- Analyze
temp表空间中存放的是临时性数据，就是 假如session A 执行了一条SQL   数据量很大而且用到了排序，因为PGA的空间不够
此时oracle就会在temp表空间中给session着一块空闲的空间，当session A的SQL执行完毕后，或者还没有之行完 session A主动断开。此时
oracle会把给A的那块空间中的数据清除，把空间释放，可以给别的Session 使用。
oracle 把temp表空间中的数据清除了，temp表空间变成了空闲的，但是temp表空间数据文件占用系统的物理大小并没有变化
oracle 不会智能的自动的把temp数据文件缩小必须手工 。下面咱们做一个实验进行测试一下
实验
使用scott 用户创建表 object_test进行测试
[oracle@Master ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 27 16:48:35 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table object_test as select * from dba_objects;
Table created.
SQL> insert into object_test select * from object_test;
/
2942240 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from object_test;
  COUNT(*)
----------
   5884480
object_test 表有5884480 条数据
下面查看 temp表空间数据文件是否允许自动扩展
SQL> desc dba_temp_files;
Name        Null?    Type
----------------------------------------- -------- ----------------------------
FILE_NAME         VARCHAR2(513)
FILE_ID         NUMBER
TABLESPACE_NAME      NOT NULL VARCHAR2(30)
BYTES          NUMBER
BLOCKS          NUMBER
STATUS          VARCHAR2(7)
RELATIVE_FNO         NUMBER
AUTOEXTENSIBLE         VARCHAR2(3)
MAXBYTES         NUMBER
MAXBLOCKS         NUMBER
INCREMENT_BY         NUMBER
USER_BYTES         NUMBER
USER_BLOCKS         NUMBER
SQL> select  AUTOEXTENSIBLE from dba_temp_files;
AUT
---
YES
AUTOEXTENSIBLE的值为YES 表明temp数据文件是自动扩展的。

为了实验的效果明显 首先把temp表空间数据文件改成2M
SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 2M;
Database altered.
[oracle@Master orcl]$ du -sh  /u01/app/oracle/oradata/orcl/temp01.dbf
2.0M     temp01.dbf
现在查看是否有session在使用临时表空间
SQL> SELECT se.username,sid,serial#,sql_address,machine,program,tablespace,segtype,contents
FROM v$session se,v$sort_usage su
WHERE se.saddr=su.session_addr;   2    3  
no rows selected
结果为空没有session 使用
下面我们进行测试 同时使用SCOTT 用户连接4个session
执行下面的语句
SQL>select * from object_test order by object_id;
此时在查询
SQL>SELECT se.username,
sid,serial#,sql_address,machine,program,tablespace,segtype,contents
FROM v$session se,v$sort_usage su
WHERE se.saddr=su.session_addr;      
SCOTT 18   693   00000000D4DB0308  Master.Hadoop sqlplus@Master.Hadoop (TNS V1-V3) TEMP SORT TEMPORARY
SCOTT 401   6593 00000000D4DB0308 Master.Hadoop sqlplus@Master.Hadoop (TNS V1-V3) TEMP SORT TEMPORARY
SCOTT 793   2353 00000000D4DB0308 Master.Hadoop sqlplus@Master.Hadoop (TNS V1-V3) TEMP SORT TEMPORARY
SCOTT 1187 1215 00000000D4DB0308 Master.Hadoop sqlplus@Master.Hadoop (TNS V1-V3) TEMP SORT TEMPORARY
有4个Session 在使用temp表空间
这是再查看temp 表空间数据库文件大小
[root@Master ~]# du -sh  /u01/app/oracle/oradata/orcl/temp01.dbf
5.2G /u01/app/oracle/oradata/orcl/temp01.dbf
此时已变成5.2G  这时把所有的session断开再查询temp的使用
SQL> SELECT se.username,sid,serial#,sql_address,machine,program,tablespace,segtype,contents
FROM v$session se,v$sort_usage su
WHERE se.saddr=su.session_addr;   2    3  
no rows selected
已经没有session在使用temp表空间
这时再查看temp表空间数据文件大小
[root@Master ~]# du -sh  /u01/app/oracle/oradata/orcl/temp01.dbf
5.2G     /u01/app/oracle/oradata/orcl/temp01.dbf
此时还是5.2G 并没有变小
到这里也就说明 oracle 在sql执行完毕后会把sql占用的临时表空间中的数据清除但不会智能的缩小temp数据文件物理大小这是只能手工对temp数据文件resize 改变其物理大小。
也有人会认为不temp数据文件自动扩展，就不会造成数据表空间暴涨的问题了
下面看看
先把temp表空间文件修改成非自动扩展的
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' autoextend off;
Database altered.
SQL> select  AUTOEXTENSIBLE from dba_temp_files;
AUT
---
NO
查看此时的 temp数据文件大小
[root@Master ~]# du -sh /u01/app/oracle/oradata/orcl/temp01.dbf
2.1M /u01/app/oracle/oradata/orcl/temp01.dbf
此时再执行  
SQL>select * from object_test order by object_id;
看看会报什么错
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
此时因为temp数据文件不能扩展，oracle 报出ORA-01652 错误 并且自动把查询停止了
所以在生产库中 还是要temp表空间自动扩展。
而我们常说的 temp表空间暴涨 和 不会收缩是两码事。
1：oracle temp 自动暴涨一定是有大的查询
2：temp自动收缩并不是oracle 自动的缩小temp数据文件占用的物理大小 而是清楚已经查询完的sql占用的空间
    若想缩小物理大小 还得手工

yegaopeng

@点点滴滴的

artsgroup

微软,Linux,操作系统技术尽在www.santongit.com！

abc1928374

每天来学习，跟三通一起成长！

tgq5862

这些都噱头

ericisgood

原來如此，長知識了!

ReturnDream

长见识。。

dì┽6天魔王

清理电脑垃圾

baifuwa

长知识了 ，谢谢分享

gxb553064939

非常不错的内容，很好。