查看排序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;