【Oracle九大性能视图】之8.v$sort_usage_temp表空间的使用情况

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:1130

【Oracle九大性能视图】之8.v$sort_usage_temp表空间的使用情况

1、表结构
SQL> desc v$sort_usage
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(30)
USER VARCHAR2(30)
SESSION_ADDR RAW(8)
SESSION_NUM NUMBER
SQLADDR RAW(8)
SQLHASH NUMBER
SQL_ID VARCHAR2(13)
TABLESPACE VARCHAR2(31)
CONTENTS VARCHAR2(9)
SEGTYPE VARCHAR2(9)
SEGFILE# NUMBER
SEGBLK# NUMBER
EXTENTS NUMBER
BLOCKS NUMBER
SEGRFNO# NUMBER

2、SQL语句

temp表空间的使用情况,当temp表空间变得巨大的时候,根据session_addr可以得到session id,根据sqladdr和sqlhash可以得到正在执行的sql:

select se.username,
se.sid,
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 EXTENTS SPACE TABLESPACE SEGTYPE SQL_TEXT
1 SYS 145 1 1048576 TEMP DATA select value from v$sesstat where sid = :sid order by statistic#
2 SYS 145 1 1048576 TEMP INDEX select value from v$sesstat where sid = :sid order by statistic#
3 SYS 145 1 1048576 TEMP LOB_DATA select value from v$sesstat where sid = :sid order by statistic#

3、查临时文件情况
SELECT tf.inst_id, tf.tfnum, TO_NUMBER (tf.tfcrc_scn), TO_DATE (tf.tfcrc_tim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), tf.tftsn, tf.tfrfn, DECODE (BITAND (tf.tfsta, 2), 0, 'OFFLINE', 2, 'ONLINE', 'UNKNOWN'), DECODE (BITAND (tf.tfsta, 12), 0, 'DISABLED', 4, 'READ ONLY', 12, 'READ WRITE', 'UNKNOWN' ), fh.fhtmpfsz * tf.tfbsz, fh.fhtmpfsz, tf.tfcsz * tf.tfbsz, tf.tfbsz, fn.fnnam FROM x$kcctf tf, x$kccfn fn, x$kcvfhtmp fh WHERE fn.fnfno = tf.tfnum AND fn.fnfno = fh.htmpxfil AND tf.tffnh = fn.fnnum AND tf.tfdup != 0 AND fn.fntyp = 7 AND fn.fnnam IS NOT NULL

结果如下:
INST_ID TFNUM TO_NUMBER(TF.TFCRC_SCN) TO_DATE(TF.TFCRC_TIM,'MM/DD/RR TFTSN TFRFN DECODE(BITAND(TF.TFSTA,2),0,'O DECODE(BITAND(TF.TFSTA,12),0,' FH.FHTMPFSZ*TF.TFBSZ FHTMPFSZ TF.TFCSZ*TF.TFBSZ TFBSZ FNNAM
1 1 1 519177 2010-4-9 12:51:42 3 1 ONLINE READ WRITE 33554432 4096 20971520 8192 D:\ORACLE64\PRODUCT\10.2.0\ORADATA\CSDB\TEMP01.DBF

4、查看谁在用临时表空间
SELECT se.username,se.sid,
  se.serial#,
  se.sql_address,
  se.machine,
  se.program,
  su.tablespace,
  su.segtype,
  su.contents
  FROM v$session se,
  v$sort_usage su
  WHERE se.saddr=su.session_addr;

结果如下:
USERNAME SID SERIAL# SQL_ADDRESS MACHINE PROGRAM TABLESPACE SEGTYPE CONTENTS
1 SYS 145 15 00 WORKGROUP\ZHOULINLING plsqldev.exe TEMP DATA TEMPORARY
2 SYS 145 15 00 WORKGROUP\ZHOULINLING plsqldev.exe TEMP LOB_DATA TEMPORARY
3 SYS 145 15 00 WORKGROUP\ZHOULINLING plsqldev.exe TEMP INDEX TEMPORARY

5、查看临时表空间temp空闲情况
select TABLESPACE_NAME,file_id,bytes_used/1024/1024,bytes_free/1024/1024 from v$TEMP_SPACE_HEADER;

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】