前段时间有个问题是关于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占用的空间 若想缩小物理大小 还得手工 |