oracle 处理temp 表空间爆长的问题

首先看temp ts，以下操作会使用temp表空间：

- build index.

- ORDER BY or GROUP BY

- DISTINCT.

- UNION & INTERSECT & MINUS

- Sort-Merge joins.

- Analyze

正常来说，在完成Select语句、create index等一些使用TEMP表空间的排序操作后，Oracle是会自动释放掉临时段a的。

但有些有侯我们则会遇到临时段没有被释放，TEMP表空间几乎满的状况，甚至是我们重启了数据库仍没有解决问题

在v$sort_segment字典中，我们可能看到temp的详细的使用情况,

SQL> desc v$sort_segment

 名称                                                  是否为空? 类型

 --------------------------------------                            --------------- -------- ------------

 TABLESPACE_NAME                                                VARCHAR2(31)

 SEGMENT_FILE                                                   NUMBER

 SEGMENT_BLOCK                                                  NUMBER

 EXTENT_SIZE                                                    NUMBER

 CURRENT_USERS                                                  NUMBER

 TOTAL_EXTENTS                                                  NUMBER

 TOTAL_BLOCKS                                                   NUMBER

 USED_EXTENTS                                                   NUMBER

 USED_BLOCKS                                                    NUMBER

 FREE_EXTENTS                                                   NUMBER

 FREE_BLOCKS                                                    NUMBER

 ADDED_EXTENTS                                                  NUMBER

 EXTENT_HITS                                                    NUMBER

 FREED_EXTENTS                                                  NUMBER

 FREE_REQUESTS                                                  NUMBER

 MAX_SIZE                                                       NUMBER

 MAX_BLOCKS                                                     NUMBER

 MAX_USED_SIZE                                                  NUMBER

 MAX_USED_BLOCKS                                                NUMBER

 MAX_SORT_SIZE                                                  NUMBER

 MAX_SORT_BLOCKS                                                NUMBER

 RELATIVE_FNO                                                   NUMBER

 

而v$sort_usage将会提供目前操作的会话.

SQL> desc v$sort_usage;

 名称                                                  是否为空? 类型

 ----------------------------------------------------- -------- -------------

 USERNAME                                                       VARCHAR2(30)

 USER                                                           VARCHAR2(30)

 SESSION_ADDR                                                   RAW(4)

 SESSION_NUM                                                    NUMBER

 SQLADDR                                                        RAW(4)

 SQLHASH                                                        NUMBER

 TABLESPACE                                                     VARCHAR2(31)

 CONTENTS                                                       VARCHAR2(9)

 SEGTYPE                                                        VARCHAR2(9)

 SEGFILE#                                                       NUMBER

 SEGBLK#                                                        NUMBER

 EXTENTS                                                        NUMBER

 BLOCKS                                                         NUMBER

 SEGRFNO#                                                       NUMBER

 

sql>select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;

TABLESPACE_NAME   CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS

------------------------------ ------------- ------------     -----------           -----------

TEMP              1        13696       13440         256

 

SQL> /

TABLESPACE_NAME  CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS

------------------------------ ------------- ------------ ----------- -----------

TEMP              1        13696       13696           0

 

SQL> select username,session_addr,sqladdr,sqlhash,tablespace from v$sort_usage;

 

USERNAME   SESSION_ SQLADDR     SQLHASH TABLESPACE

---------- -------- -------- ---------- --------------------

SYS        682102D8 66BE52D8  919180126 TEMP

 

更多的信息如下：

 

col tablespace format a20

select se.username,se.sid,se.serial#,su.extents,su.blocks*to_number(rtrim(p.value)) as Space,tablespace,segtype,sql_text from v$sort_usage su,v$parameter p,v$session se,v$sql s

where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid;

 

USERNAME          SID    SERIAL#    EXTENTS      SPACE TABLESPACE           SEGTYPE

---------- ---------- ---------- ---------- ---------- -------------------- ---------

SQL_TEXT

----------------------------------------------------------------

SYS                 9          5        107  112197632 TEMP                 SORT

select * from arbor.server_lookup where rownum<1000000 order by

bill_lname_find

一般来说，排序操作都很快，由于本例是测试，所以temp表空间已经撑爆了，结果还没有出来，所以可以抓到相应的sql。

而这时候，在另一个会话中，我的查询语句已经结束了，但是没有返回任何结果！

 

 

metalink推荐下列一些方法：

--重启实例

 重启实例重启时，smon进程对临时段释放,但是对于生产库，我们就不能用这种方法了。。。。

如果重启后sort段没有被释放呢？

--修改参数(仅适用于8i及8i以下版本)

 SQL>alter tablespace temp increase 1;

 SQL>alter tablespace temp increase 0;

--合并碎片（仅适用于字典管理的表空间）

1、首先看谁在用临时段

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;     

 

USERNAME          SID    SERIAL# SQL_ADDR MACHINE              PROGRAM                               TABLESPACE

---------- ---------- ---------- -------- -------------------- ---------------------------------------- ----------

SYS                 9          3 66CDF6D0 ASPIRE\GZ-ZHAOLEHUAN sqlplus.exe                           TEMP

 

 

2、然后将那些会话Kill掉

Alter system kill session 'sid,serial#';

SQL> alter system kill session '9,3';

系统已更改。

3、整理下表空间

Alter tablespace TEMP coalesce

*

ERROR 位于第 1 行:

ORA-03217: 变更 TEMPORARY TABLESPACE 无效的选项

而事实上，这处方法只能针对字典管理的表空间。对于本地管理的表空间，不是需要整理的。

 

--诊断事件

select ts#, name from sys.ts$ ;

 

       TS# NAME

---------- --------------------

         0 SYSTEM

         1 UNDOTBS1

         2 TEMP

         3 CWMLITE

         4 DRSYS

         5 EXAMPLE

         6 INDX

         7 ODM

         8 TOOLS

         9 USERS

        10 XDB

        11 CAT_CUST

        12 CAT_CUST_IDX

        13 zlh_test

        14 ZLH_TEST

        15 TEMP1

已选择16行。

 SQL>alter session set events 'immediate trace name DROP_SEGMENTS level 4'

 说明：level=TS#+ 1

--重建temp

1.创建中转临时表空间

create temporary tablespace  TEMP1 TEMPFILE 'E:\ORACLE\ORADATA\ORCL9\temp02.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT  1M MAXSIZE UNLIMITED;  

2.改变缺省临时表空间为刚刚创建的新临时表空间temp1

alter database default  temporary tablespace  temp1;

3.删除原来临时表空间

drop tablespace temp including contents and datafiles;

4.重新创建临时表空间

create temporary tablespace  TEMP TEMPFILE 'E:\ORACLE\ORADATA\ORCL9\temp01.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT  1M MAXSIZE UNLIMITED;  

5.重置缺省临时表空间为新建的temp表空间

alter database default  temporary tablespace  temp;

6.删除中转用临时表空间

drop tablespace temp1 including contents and datafiles;

7.如果有必要，那么重新指定用户表空间为重建的临时表空间

alter user arbor temporary tablespace  temp; 

 

 

当然，以上方法，是因为表空间撑爆了，我们才去采用解决办法，但最根本的原因还是在于disk的sort过多，而disk sort过多也可能是因为

sort area的设置有问题。不过，从memory和disk的空间上来考虑，如果因为disk sort而导致temp的空间增长太快，最应该注意的是到底是什么样的sql语句要使用这么多的temp空间？

 

对于sort area设置，不在本文讨论的范围，转帖网上别人总结的如下：

http://space.itpub.net/?uid-8720638-action-viewspace-itemid-364724

原始出处已不可考。。。。。

 

排序区域的分配

-专用服务器分配sort area.

 排序区域在PGA.

-共享服务器分配sort area.

 排序区域在UGA. (UGA在shared pool中分配).

 

在9i以前的版本,由sort_area_size决定sort area的分配,在9i及以后的版本,

当workarea_size_policy等auto时,由pga_aggregate_target参数决定sort

area的大于,这时的sort area应该是pga总内存的5%.当workarea_size_policy

等manual时,sort area的大小还是于sort_area_size决定.

 

无论是那个版本,如果sort area开得过小,In-memory Sort率较低,那temp表空间

肯定会增长得很快,如果开得较高,在C/S结构中将会导致内存消耗严重(长连接较多).

 

由于smon进程每隔5分钟都要对不再使用的sort segment进行回收,如果你不想让

smon回收sort segment的话,可以使用以下两个event写入初始化参数文件,然后

重启实例,这样如果你的磁盘排序较多,很快就会涨暴磁盘......

 

event="10061 trace name context forever, level 10"  //禁止加收

event="10269 trace name context forever, level 10"  //禁止合并碎片

 

通过合理地设置pga或sort_area_size,可以消除大部分的dist sort,那其它的disk

sort该如何处理呢？从sort引起的原因来看,索引/分析/异常引起的disk sort应该是

很少的一部分,其它的应该是select中的distinct/union/group by/order by以及

merge sort join啦,那我们如何捕获这些操作呢？

通常如何有磁盘排序的SQL,它的逻辑读/物理读/排序/执行时间等都是比较大的,所以我

们可以对v$sqlarea或v$sql字典进行过滤,经过长期地监控数据库,相信可以把这些害

群之马找出来.即然找出这些引起disk sort的SQL后怎么办呢？当然是对SQL进行分析,

尽而优化之。

 

#!/bin/sh

ORACLE_SID=miat1cat;

export ORACLE_SID

sqlplus /nolog <<EOF

conn /as sysdba

col sql_text format a81

col disk_reads format 999999.99

col bgets_per format 99999999.99

col "ELAPSD_TIME(s)" format 9999.99

col "cpu_time(s)" format 9999.99

set long 99999999999

set pagesize 9999

select address,hash_value,disk_reads/executions disk_reads,elapsed_time/1000000/executions as "ELAPSD_TIME(s)",

       buffer_gets/executions bgets_per,executions,first_load_time as first_time,sql_text

 from v$sql

where executions > 0 and (disk_reads/executions > 500 or buffer_gets/executions > 20000) and command_type = 3

 order by 3,4;

 

--select s.disk_reads,s.buffer_gets/s.executions bgets_per,first_load_time,st.sql_text

-- from v$sql s,v$sqltext_with_newlines st

--where s.address=st.address and s.hash_value=st.hash_value

-- and s.disk_reads > 1000 or (s.executions > 0 and s.buffer_gets/s.executions > 50000)

--order by st.piece;

exit

EOF

 

用以下语句查看表空间的使用情况：

也可以用如下的语句：

 

 

------查看所有表空间大小及使用情况

SELECT d.status "Status",

       d.tablespace_name "Name",

       d.contents "Type",

       d.extent_management "Extent Management",

       to_char(nvl(a.bytes / 1024 / 1024, 0), '99999999.999') "Total Size (M)",

       to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024,

               '99999999.999') "Used (M)",

       to_char(nvl(nvl(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') "Free (M)",

       to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),

               '990.00') "Used %"

  FROM sys.dba_tablespaces d,

       (SELECT tablespace_name, SUM(bytes) bytes

          FROM dba_data_files

         GROUP BY tablespace_name) a,

       (SELECT tablespace_name, SUM(bytes) bytes

          FROM dba_free_space

         GROUP BY tablespace_name) f

 WHERE d.tablespace_name = a.tablespace_name(+)

   AND d.tablespace_name = f.tablespace_name(+);

 

 

Status    Name     Type   Extent Man Total Size (M Used (M)      Free (M)   Used %

--------- ------------------------------ --------- ---------- ------------- ------------- ------------- -------

ONLINE    BMIS_DATA                      PERMANENT LOCAL           2046.000         1.000      2045.000    0.05

ONLINE    CPAS_DATA                      PERMANENT LOCAL           1023.000       227.500       795.500   22.24

ONLINE    INDX     PERMANENT LOCAL                        255.000          .063       254.938    0.02

ONLINE    MOMAX_DATA                     PERMANENT LOCAL          13296.000     10785.500      2510.500   81.12

ONLINE    MOPORTAL_DATA                  PERMANENT LOCAL           2046.000       733.063      1312.938   35.83

ONLINE    MPAS_DATA                      PERMANENT LOCAL           3069.000       105.000      2964.000    3.42

ONLINE    SMAIL_DATA                     PERMANENT LOCAL           1023.000         7.688      1015.313    0.75

ONLINE    SYSTEM                         PERMANENT LOCAL           1023.000       347.375       675.625   33.96

ONLINE    TOOLS                          PERMANENT LOCAL           1271.000       123.063      1147.938    9.68

ONLINE    UNDOTBS1                       UNDO      LOCAL           9207.000      2355.313      6851.688   25.58

ONLINE    USERS                          PERMANENT LOCAL            255.000        15.625       239.375    6.13

ONLINE    TEMP                           TEMPORARY LOCAL               .000          .000          .000    0.00

11 rows selected.