发现使用oracle临时表空间的进程/查询

Question

Oracle FAQ将临时表空间定义如下：

临时表空间用于管理数据库排序操作和存储全局临时表的空间。例如，如果连接两个大型表，而Oracle无法在内存中进行排序，则将在临时表空间中为执行排序操作分配空间。

这很好，但是我需要更多的细节来说明究竟什么是使用这个空间。由于应用程序设计的怪癖，大多数查询都会进行某种排序，因此我需要将其缩小到客户机可执行文件、目标表或SQL语句。

本质上，我在寻找线索，以便更准确地告诉我这个(相当大的应用程序)可能出了什么问题。任何一种线索都可能有用，只要它比“分类”更精确。

Answer 1

我不确定您需要确切地掌握哪些信息，但是使用下面的查询将指出哪些程序/用户/会话等当前正在使用临时空间。

SELECT   b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
       , a.SID
       , a.serial#
       , a.username
       , a.osuser
       , a.program
       , a.status
    FROM v$session a
       , v$sort_usage b
       , v$process c
       , v$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
ORDER BY b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

一旦您发现哪个会话正在造成损害，然后查看正在执行的SQL，您应该在正确的路径上。

Answer 2

感谢迈克尔OShea的回答，

但是如果您有Oracle RAC多个实例，那么您将需要这个.

SELECT   b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
       , a.inst_ID
       , a.SID
       , a.serial#
       , a.username
       , a.osuser
       , a.program
       , a.status
    FROM gv$session a
       , gv$sort_usage b
       , gv$process c
       , gv$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
     -- AND b.TABLESPACE='TEMP2'
ORDER BY a.inst_ID , b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

这是生成杀死语句的脚本:请回顾一下您要杀死的会话.

SELECT  b.TABLESPACE, a.username , a.osuser , a.program , a.status ,
       'ALTER SYSTEM KILL SESSION '''||a.SID||','||a.SERIAL#||',@'||a.inst_ID||''' IMMEDIATE;'
    FROM gv$session a
       , gv$sort_usage b
       , gv$process c
       , gv$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
     -- AND b.TABLESPACE='TEMP'
ORDER BY a.inst_ID , b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

Answer 3

经验法则之一是，几乎任何花费超过一秒钟的查询都可能使用一些临时空间，这些查询不仅涉及订单，而且还包括：

(obviously)

• Index

1. 组BYs ( 10.2之前的GROUPBY排序和10.2以后的HASH GROUPBY )
2. 哈希连接或合并连接
3. 全局临时表
4. 重新构建

有时候，Oracle不会释放临时表空间中使用的空间(bug/性情)，因此您需要手动从表空间中删除一个文件，将其从文件系统中删除并创建另一个文件。