查看排序sql

很多情况下,DBA需要查看SQL排序的执行情况
一：查出当前正在排序的SQL使用情况
SQL> select a.tablespace, b.sid, b.serial#, a.blocks
2 ,c.sql_text
3 from v$sort_usage a
4 ,v$session b
5 ,v$sqltext c
6 where a.session_addr = b.saddr
7 and b.sql_address = c.address
8 order by a.tablespace,b.sid,b.serial#,c.address, c.piece;

TABLESPACE SID SERIAL# BLOCKS
------------------------------- ---------- ---------- ----------
SQL_TEXT
----------------------------------------------------------------
TEMP 302 7727 128
select temp_seq_28149.nextval, opcode, afn, dba, block_count fro

4864
select temp_seq_28149.nextval, opcode, afn, dba, block_count fro

128
m temp_view_28149


TABLESPACE SID SERIAL# BLOCKS
------------------------------- ---------- ---------- ----------
SQL_TEXT
----------------------------------------------------------------
TEMP 302 7727 4864
m temp_view_28149

二：查出当前SQL占用TEMP表空间使用率
SQL> SELECT b.tablespace,
2 ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
3 a.sid||','||a.serial# SID_SERIAL,
4 a.username,
5 a.program
6 FROM sys.v_$session a,
7 sys.v_$sort_usage b,
8 sys.v_$parameter p
9 WHERE p.name = 'db_block_size'
10 AND a.saddr = b.session_addr
11 ORDER BY b.tablespace, b.blocks;

TABLESPACE SIZE
------------------------------- -----------------------------------------
SID_SERIAL
--------------------------------------------------------------------------------
USERNAME PROGRAM
------------------------------ ------------------------------------------------
TEMP 1M
302,7727
MCBACKUP ruby@backup (TNS V1-V3)

38M
302,7727
MCBACKUP ruby@backup (TNS V1-V3)

TABLESPACE SIZE
------------------------------- -----------------------------------------
SID_SERIAL
--------------------------------------------------------------------------------
USERNAME PROGRAM
------------------------------ ------------------------------------------------
三：根据SID可以查出PGA的使用情况

SQL> select PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM ,PGA_MAX_MEM from V$PROCESS whee pid=302;