三通IT学院 门户 技术频道 数据库 查看内容

2014-11-28 18:17
temp 表空间暴涨问题

摘要 : 前段时间有个问题是关于temp表空间一直暴涨的问题,下面做个实验进行测试一下 首先要明白temp表空间中放的什么东西,还有什么操作会是temp表空间增长。 在select查询中有以下条件会发生数据的排序,分组,在P ...

时光与梦2014-11-28 18:17208120
原作者: 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占用的空间
    若想缩小物理大小 还得手工

2

鲜花

握手

雷人

路过

鸡蛋

刚表态过的朋友 (2 人)

收藏 分享 邀请
发表评论

最新评论

引用 yegaopeng 2014-11-29 06:03
@点点滴滴的
引用 artsgroup 2014-11-29 12:21
微软,Linux,操作系统技术尽在www.santongit.com
引用 abc1928374 2014-12-4 11:27

每天来学习,跟三通一起成长!
引用 tgq5862 2015-1-1 17:46
这些都噱头
引用 ericisgood 2015-3-23 13:17
原來如此,長知識了!
引用 ReturnDream 2015-4-27 11:37
长见识。。
引用 dì┽6天魔王 2015-5-16 19:03
清理电脑垃圾
引用 baifuwa 2015-6-21 21:40
长知识了 ,谢谢分享
引用 gxb553064939 2015-6-23 09:11

非常不错的内容,很好。
引用 sheng5240 2015-7-25 01:55
锄禾日当午,发帖真辛苦。谁知坛中餐,帖帖皆辛苦!
引用 dong_xinrui 2017-2-23 08:29
现在还有几个不要金币的帖子
引用 daxi 2017-2-24 08:20
多谢分享           
引用 bdmc 2017-2-24 08:34
Thanks for your share
引用 dong_xinrui 2017-3-27 08:40
谢谢楼主分享
引用 bdmc 2017-3-27 08:45
这个可以的,学习一下。
引用 dong_xinrui 2017-3-28 11:18
谢谢楼主分享

查看全部评论(20)

返回顶部