Temp segment 使用的一些总结

Temp segment 使用的一些总结。

---

1. 关于temp tablespace的说明，截取自：

http://database.51cto.com/art/201107/273018_all.htm

---

临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。当oracle里需要用到sort的时候，PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序，同时如果有异常情况的话，也会被放入临时表空间,正常来说，在完成Select语句、create index等一些使用TEMP表空间的排序操作后，Oracle是会自动释放掉临时段的。但有些有侯我们则会遇到临时段没有被释放，TEMP表空间几乎满的状况，甚至是我们重启了数据库仍没有解决问题。

排序是很耗资源的，Temp表空间满了,关键是优化你的语句，尽量使排序减少才是上策.

---

2 mos文档

How Do You Find Who And What SQL Is Using Temp Segments (Doc ID
317441.1)

中记载的，
使用v$session和v$sqlarea,v$tempseg_usage组合取得的block用sum计算总量是不准确的。
因为他们只能取到library cache中还存在的sql的block，而非总量。

---

3 v$tempseg_usage 和 v$sort_usage是相同的。

select synonym_name,table_name from dba_synonyms where table_name in ('V_$SORT_USAGE');

SYNONYM_NAME                   TABLE_NAME
------------------------------ ------------------------------
V$TEMPSEG_USAGE                V_$SORT_USAGE
V$SORT_USAGE                   V_$SORT_USAGE

---

4.
通过v$sort_segment可以看到是temporary类型的表空间的sortsegment总体的情报。

---

5.
摘自

http://www.dba-oracle.com/t_v_tempseg_usage.htm

Question: How do I use the v$tempseg_usage view to see temporary segment within my TEMP tablespace?

Answer: The v$tempseg_usage view display all temp tablespace usage (sorting, hash joins, LOB data storage, global temporary table data, etc.) that have spilled out of PGA RAM and are being stored in the TEMP tablespace.

select * from v$tempseg_usage;

The following script will display all TEMP space usage for objects involved with global temporary tables (GTT);

select
   username,
   contents,
   segtype,
   extents,
   blocks
from
   v$tempseg_usage
where
   segtype = 'DATA'
and
   contents = 'TEMPORARY'
order by
   username;

The following script will display all contents of the temp tablespace using v$tempseg_usage:

set lines 200
col username format a20

select
   username,
   session_addr,
   session_num,
   sqladdr,
   sqlhash,
   sql_id,
   contents,
   segtype,
   extents,
   blocks
from
   v$tempseg_usage
order by
   username;

– ***********************************

col hash_value for a40 
col tablespace for a10 
col username for a15 
set lines 132 
set pages 1000

select 
   s.sid, 
   s.username, 
   u.tablespace, 
   s.sql_hash_value||'/'||u.sqlhash hash_value, 
   u.segtype, u.contents, 
   u.blocks 
from
   v$session s, 
   v$tempseg_usage u 
where 
   s.saddr=u.session_addr 
order by 
    u.blocks;

This query will display all “current” SQL (still in the library cache, and the TEMP space used by the SQL:

col hash_value format a40
col tablespace format a10
col username   format a15
set linesize 132 pagesize 1000

SELECT 
    s.sid, 
   s.username, 
   u.tablespace, 
   s.sql_hash_value||'/'||u.sqlhash hash_value, 
   u.segtype, 
   u.contents, 
   u.blocks
FROM 
    v$session s, 
   v$tempseg_usage u
WHERE 
   s.saddr=u.session_addr
order by 
   u.blocks;

---

6.
以下都摘自

http://www.cnblogs.com/kerrycode/p/4006840.html

查询临时表空间文件大小，最大大小，状态等。

SET LINESIZE 1200
COL NAME FOR A60
SELECT FILE#                        AS FILE_NUMBER
    ,NAME                           AS NAME
    ,CREATION_TIME                  AS CREATION_TIME
    ,BLOCK_SIZE                     AS BLOCK_SIZE
    ,BYTES/1024/1024/1024           AS "FILE_SIZE(G)"
    ,CREATE_BYTES/1024/1024/1024    AS "INIT_SIZE(G)"
    ,STATUS                         AS STATUS
    ,ENABLED                        AS ENABLED
FROM V$TEMPFILE;

FILE_NUMBER NAME                                                         CREATION_ BLOCK_SIZE FILE_SIZE(G) INIT_SIZE(G) STATUS  ENABLED
----------- ------------------------------------------------------------ --------- ---------- ------------ ------------ ------- ----------
          1 /home/oracle/app/oracle/oradata/orcl/temp01.dbf              12-JUL-16       8192   .204101563    .01953125 ONLINE  READ WRITE

SET LINESIZE 1200
    COL TABLESPACE_NAME FOR A30
   COL FILE_NAME FOR A60
SELECT TABLESPACE_NAME                 AS TABLESPACE_NAME
        ,FILE_NAME                     AS FILE_NAME
        ,BLOCKS                        AS BLOCKS
        ,STATUS                        AS STATUS
        ,AUTOEXTENSIBLE                AS AUTOEXTENSIBLE
        ,BYTES/1024/1024/1024          AS "FILE_SIZE(G)"
        ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024,
                          MAXBYTES/1024/1024/1024)
                                       AS "MAX_SIZE(G)"
        ,INCREMENT_BY                  AS "INCREMENT_BY"
        ,USER_BYTES/1024/1024/1024     AS "USEFUL_SIZE"
FROM DBA_TEMP_FILES;

TABLESPACE_NAME                FILE_NAME                                                        BLOCKS STATUS  AUT FILE_SIZE(G) MAX_SIZE(G) INCREMENT_BY USEFUL_SIZE
------------------------------ ------------------------------------------------------------ ---------- ------- --- ------------ ----------- ------------ -----------
TEMP                           /home/oracle/app/oracle/oradata/orcl/temp01.dbf                   26752 ONLINE  YES   .204101563  31.9999847            80     .203125

查询那些对象使用了哪个临时表空间
SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;