临时表空间使用率过高

zyclove201305

使用的是10.2.0.4.0的数据库，操作系统用的是AIX
以前临时表空间大小为32G，结果昨天报ORA-01652（临时表空间不够），
我用查了一下临时表空间的使用情况，发如上SQL：
1、查看使用率
select TABLESPACE_NAME, BYTES_USED/1024/1024 used_MB,BYTES_FREE/1024/1024 free_MB
from V$TEMP_SPACE_HEADER;
--这个查出来，看起来快使用完了

2、查看哪些SQL在使用：
select * from (
select sess.SID,  segtype, blocks*8/1000 "MB" ,sql_text
from v$sort_usage sort, v$session sess ,v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sort.SQLADDR (+)
order by blocks desc
) a
where rownum<10
--这个查出来，看起来没使用多少，我就没搞懂了。

请教！！

Maclean Liu(刘相兵

1、是否为RAC?
2、给出v$sort_usage的内容
3、给出

   SELECT d.tablespace_name "Name",
                TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
                TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999')  "HWM (M)",
                TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,
                TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)",
                TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"
           FROM sys.dba_tablespaces d,
                (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
                (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
          WHERE d.tablespace_name = a.tablespace_name(+)
            AND d.tablespace_name = t.tablespace_name(+)
            AND d.extent_management like 'LOCAL'
            AND d.contents like 'TEMPORARY'
/

zyclove201305

能让刘先生在百忙之中回复我的贴子，我感到很荣幸。
是RAC环境。
这几天出差，明天回公司，再把您需要的信息贴出来。

zyclove201305

Maclean Liu(刘相兵 发表于 2014-6-18 15:15
1、是否为RAC?
2、给出v$sort_usage的内容
3、给出

1、是RAC环境
2、select * from v$sort_usage
没有数据。

3、
SELECT d.tablespace_name "Name",
                 TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
                 TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999')  "HWM (M)",
                 TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,
                 TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)",
                 TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"
            FROM sys.dba_tablespaces d,
                 (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
                 (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
           WHERE d.tablespace_name = a.tablespace_name(+)
             AND d.tablespace_name = t.tablespace_name(+)
             AND d.extent_management like 'LOCAL'
             AND d.contents like 'TEMPORARY'
/
结果如下:
Name       Size (M)             HWM (M)         HWM %          Using (M)       Using %
---------- -------------------- --------------- -------------- --------------- --------------
TEMP            40,954.984           2365.000      5.77                 .000      0.00

4、
select TABLESPACE_NAME, BYTES_USED/1024/1024 used_MB,BYTES_FREE/1024/1024 free_MB
from V$TEMP_SPACE_HEADER;
结果如下：
TABLESPACE    USED_MB    FREE_MB
---------- ---------- ----------
TEMP       6919.99219       1272
TEMP             8190          0
TEMP             8190          0
TEMP             8191          0
TEMP       8191.99219          0

Maclean Liu(刘相兵

因为是RAC 所以需要关注2个节点上的信息，对于RAC上的temp 存在例如【案例分享】RAC中可能由于节点cache temp space导致ora-1652错误 这样的bug http://www.askmaclean.com/archiv ... segment-in-rac.html

zyclove201305

嗯，明白了，谢谢
还有一个问题：
V$TEMP_SPACE_HEADER 视图中 BYTES_USED 字段表示什么，如果表示正在使用的空间，那么跟v$sort_usage 中的信息完全不附合。如果表示高水位，它又时常在变小。

Maclean Liu(刘相兵

GV$TEMP_SPACE_HEADER
select /*+ ordered use_nl(hc) */ hc.inst_id, ts.name, hc.ktfthctfno, (hc.ktfthcsz - hc.ktfthcfree)*ts.blocksize, (hc.ktfthcsz - hc.ktfthcfre
e), hc.ktfthcfree*ts.blocksize, hc.ktfthcfree, hc.ktfthcfno from ts$ ts, x$ktfthc hc where ts.contents$ = 1 and ts.bitmapped <> 0 and ts.onl
ine$ = 1 and ts.ts# = hc.ktfthctsn and hc.ktfthccval = 0




SQL> desc       GV$TEMP_SPACE_HEADER
Name                                                                          Null?    Type
----------------------------------------------------------------------------- -------- ----------------------------------------------------
INST_ID                                                                                NUMBER
TABLESPACE_NAME                                                               NOT NULL VARCHAR2(30)
FILE_ID                                                                                NUMBER
BYTES_USED                                                                             NUMBER
BLOCKS_USED                                                                            NUMBER
BYTES_FREE                                                                             NUMBER
BLOCKS_FREE                                                                            NUMBER
RELATIVE_FNO                                                                           NUMBER

Maclean Liu(刘相兵

ktfthcfree How many blocks are free
hc.ktfthcsz - hc.ktfthcfree = blocks_used How many blocks are in use

zyclove201305

thank you Maclean Liu.