Queries to identify the sessions identifying Undo,Temp & Redo Segments

1. Query to identify the sessions consuming undo

set lines 500

col ORAUSER for a20

col PROGRAM for a30

col SID_SERIAL for a15

col Undo for a10

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,

           NVL(s.username, 'None') orauser,

           s.program,

           r.name undoseg,

           t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"

      FROM sys.v_$rollname    r,

           sys.v_$session     s,

           sys.v_$transaction t,

           sys.v_$parameter   x

    WHERE s.taddr = t.addr

      AND r.usn   = t.xidusn(+)

      AND x.name  = 'db_block_size';

---

   

select s.sid, 

       s.username,

       sum(ss.value) / 1024 / 1024 as undo_size_mb

from  v$sesstat ss

  join v$session s on s.sid = ss.sid

  join v$statname stat on stat.statistic# = ss.statistic#

where stat.name = 'undo change vector size'

and s.type <> 'BACKGROUND'

and s.username IS NOT NULL

group by s.sid, s.username;

=================================================

2. Query to identify the sessions consuming TEMP

select b.Total_MB,

       b.Total_MB - round(a.used_blocks*8/1024) Current_Free_MB,

       round(used_blocks*8/1024)                Current_Used_MB,

      round(max_used_blocks*8/1024)             Max_used_MB

from v$sort_segment a,

 (select round(sum(bytes)/1024/1024) Total_MB from dba_temp_files ) b;

----

 SELECT a.username, a.SID, a.serial#, a.osuser, b.TABLESPACE, b.blocks,

c.sql_text

FROM v$session a, v$tempseg_usage b, v$sqlarea c

WHERE a.saddr = b.session_addr

AND c.address = a.sql_address

AND c.hash_value = a.sql_hash_value

ORDER BY b.TABLESPACE, b.blocks;

=================================================

3. Query to identify the sessions Generating More Redo

select sid, serial#, value from v$session join v$sesstat using(sid) 

where statistic# = (SELECT statistic# FROM v$statname WHERE name = 'redo size');

 ----

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text

from gv$sqltext_with_newlines t,gV$SESSION s

where t.address =s.sql_address

and t.hash_value = s.sql_hash_value

and s.status = 'ACTIVE'

and s.username = 'LOSSPREVNETL'

order by s.sid,t.piece

/

----- 

select * from v$sgastat

where pool = 'shared pool'

and name = 'free memory'

/