UNDO Usage

How many bytes needs to be added in undotbs to increase the performance
==================================================================

UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)


SELECT (SUM(undoblks)) / SUM( ((end_time - begin_time) * 86400))
FROM v$undostat;
---

SELECT DISTINCT STATUS, SUM(BYTES/1024/1024) MB, COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

COLUMN sid_serial FORMAT A20
COLUMN username FORMAT A20
COLUMN program FORMAT A30
COLUMN undoseg FORMAT A20
COLUMN undo FORMAT A20
SET LINESIZE 120

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) AS sid_serial,
       NVL(s.username, '(oracle)') AS username,
       s.program,
       s.osuser,
       r.name undoseg,
       t.used_ublk * TO_NUMBER(x.value)/1024/1024||'M' AS undo
FROM   v$rollname    r,
       v$session     s,
       v$transaction t,
       v$parameter   x
WHERE  s.taddr = t.addr
AND    r.usn   = t.xidusn(+)
AND    x.name  = 'db_block_size';


SELECT
  tablespace_name "Tablespace",
  TO_CHAR((a.bytes / 1048576),'99,999,990.900') "Total MB",
  TO_CHAR(((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576),'99,999,990.900') "Used MB",
  TO_CHAR((((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576)*100) / (a.bytes / 1048576),'999,999.9') "% Used"
FROM
  (sys.dba_tablespaces d JOIN sys.sm$ts_avail a USING (tablespace_name))
  LEFT OUTER JOIN sys.sm$ts_free f USING (tablespace_name)
  where tablespace_name in ('UNDOTBS1')
ORDER BY 4;

select sum(bytes/1024/1024/1024) from dba_free_space where tablespace_name like 'UNDO%';

select sysdate, rn.name, rs.extents,
       ds.max_extents, rs.rssize,
       rs.optsize, du.username,
       se.sid, se.status,
       se.machine, sq.sql_text
  from v$transaction tr,
       v$rollstat rs,
       v$sql sq,
       v$session se,
       v$rollname rn,
       dba_users du,
       dba_segments ds
 where tr.xidusn = rn.usn
   and sq.PARSING_USER_ID = du.user_id
   and se.sql_address = sq.address
   and rs.usn = rn.usn
   and se.username = du.username
   and rn.name = ds.segment_name
   and tr.addr = se.taddr
   and sq.parse_calls = (select max(parse_calls)
                           from v$sql sq2
                          where sq2.parsing_user_id = sq.parsing_user_id
                            and sq2.address = sq.address);


select substr(V$rollname.NAME,1,20) "Rollback_Name",
substr(V$rollstat.EXTENTS,1,6) "EXTENT",
v$rollstat.RSSIZE, v$rollstat.WRITES,
substr(v$rollstat.XACTS,1,6) "XACTS",
v$rollstat.GETS,
substr(v$rollstat.WAITS,1,6) "WAITS",
v$rollstat.HWMSIZE, v$rollstat.SHRINKS,
substr(v$rollstat.WRAPS,1,6) "WRAPS",
substr(v$rollstat.EXTENDS,1,6) "EXTEND",
v$rollstat.AVESHRINK,
v$rollstat.AVEACTIVE
from v$rollname, v$rollstat
where v$rollname.USN = v$rollstat.USN
order by v$rollname.USN;

To determine whether any active transactions exists in UNDO segment use the following query:
==================================================================== 
SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR
 FROM v$parameter
WHERE name = 'undo_retention'),
 (SELECT (SUM(undoblks)/SUM(((end_time -
begin_time)*86400))) AS UPS
 FROM v$undostat),
 (SELECT value AS DBS
 FROM v$parameter
 WHERE name = 'db_block_size');

 SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
 FROM (SELECT value AS UR
 FROM v$parameter
 WHERE name = 'undo_retention'),
 (SELECT (SUM(undoblks)/SUM(((end_time -
begin_time)*86400))) AS UPS
 FROM v$undostat),
 (SELECT value AS DBS
 FROM v$parameter
 WHERE name = 'db_block_size');

Displays the undo space currently in use by users.
=================================================

COLUMN sid_serial FORMAT A20
COLUMN username FORMAT A20
COLUMN program FORMAT A30
COLUMN undoseg FORMAT A20
COLUMN undo FORMAT A20
SET LINESIZE 120

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) AS sid_serial,
       NVL(s.username, '(oracle)') AS username,
       s.program,
       s.osuser,
       r.name undoseg,
       t.used_ublk * TO_NUMBER(x.value)/1024/1024||'M' AS undo
FROM   v$rollname    r,
       v$session     s,
       v$transaction t,
       v$parameter   x
WHERE  s.taddr = t.addr
AND    r.usn   = t.xidusn(+)
AND    x.name  = 'db_block_size';

SELECT TO_CHAR(BEGIN_TIME, 'mm-dd-yyyy hh24:mi') BEGIN_TIME,
         TO_CHAR(END_TIME, 'mm-dd-yyyy hh24:mi') END_TIME,
         UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON"
         FROM v$UNDOSTAT WHERE rownum <= 144;


select USED_UBLK, USED_UREC, START_SCNB
    from v$session a, v$transaction b
    where rawtohex(a.saddr) = rawtohex(b.ses_addr)
    and a.audsid = sys_context('userenv','sessionid');