Temp segment 使用的一些总结

来源：互联网发布：数据库中% 编辑：程序博客网时间：2024/05/02 02:41

Temp segment 使用的一些总结。

关于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)

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

3 vtempsegusage和vsort_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_USAGEV$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,   blocksfrom   v$tempseg_usagewhere   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 200col username format a20select   username,   session_addr,   session_num,   sqladdr,   sqlhash,   sql_id,   contents,   segtype,   extents,   blocksfrom   v$tempseg_usageorder by   username;

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

col hash_value for a40 col tablespace for a10 col username for a15 set lines 132 set pages 1000select    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 a40col tablespace format a10col username   format a15set linesize 132 pagesize 1000SELECT     s.sid,    s.username,    u.tablespace,    s.sql_hash_value||'/'||u.sqlhash hash_value,    u.segtype,    u.contents,    u.blocksFROM     v$session s,    v$tempseg_usage uWHERE    s.saddr=u.session_addrorder by    u.blocks;

6.
以下都摘自

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

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

SET LINESIZE 1200COL NAME FOR A60SELECT 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 ENABLEDFROM 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 A60SELECT 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;

0 0