Undo Related Queries Part 2

List of All active Transactions

SELECT sid,
username,
t.used_ublk,
t.used_urec
FROM   v$transaction t,
v$session s
WHERE  t.addr = s.taddr;

 

To list all Datafile of UndoTablespace

 

SELECT tablespace_name,
file_name,
file_id,
autoextensible,
bytes / 1048576    Mbytes,
maxbytes / 1048576 maxMbytes
FROM   dba_data_files
WHERE  tablespace_name LIKE ‘%UNDO%’
OR tablespace_name LIKE ‘%RBS%’
ORDER  BY tablespace_name,
file_name;

 

SELECT tablespace_name,
file_name,
file_id,
autoextensible,
bytes / 1048576    Mbytes,
maxbytes / 1048576 maxMbytes
FROM   dba_data_files
WHERE  tablespace_name LIKE ‘%UNDOTBS2%’
ORDER  BY tablespace_name,
file_name;

 

col file_name FOR a40
set pagesize 100
SELECT tablespace_name,
file_name,
file_id,
autoextensible,
bytes / 1048576    Mbytes,
maxbytes / 1048576 maxMbytes
FROM   dba_data_files
WHERE  tablespace_name LIKE ‘%APPS_UNDOTS1%’
ORDER  BY tablespace_name,
file_name;

 

SELECT file_name,
tablespace_name,
bytes / 1024 / 1024,
maxbytes / 1024 / 1024,
autoextensible
FROM   dba_data_files
WHERE  file_name LIKE ‘%undo%’
ORDER  BY file_name;

 

To check when a table is last analysed

 

SELECT owner,
table_name,
tablespace_name,
status,
last_analyzed,
partitioned,
dependencies,
dropped
FROM   dba_tables
WHERE  table_name LIKE ‘MLC_PICK_LOCKS_DETAIL’;

 

SELECT owner,
table_name,
tablespace_name,
last_analyzed,
partitioned,
dependencies,
dropped
FROM   dba_tables
WHERE  table_name LIKE ‘APPS.XLA_AEL_GL_V’;

 

To list all Undo datafiles with status and size

 

show parameter undo
show parameter db_block_size
col tablespace_name FORM a20
col file_name FORM a60
set lines 120
SELECT tablespace_name,
file_name,
status,
bytes / 1024 / 1024
FROM   dba_data_files
WHERE  tablespace_name = (SELECT tablespace_name
FROM   dba_tablespaces
WHERE  CONTENTS = ‘UNDO’);

 

Total undo space

SELECT SUM(bytes) / 1024 / 1024 / 1024 GB
FROM   dba_data_files
WHERE  tablespace_name = ‘&Undo_TB_Name’;

 

Undo Tablespace

 

SELECT tablespace_name
FROM   dba_tablespaces
WHERE  tablespace_name LIKE ‘%UNDO%’;

 

To find MaxQueryLength from stats$undostat

SELECT Max(maxquerylen)
FROM   stats$undostat;

SELECT Max(maxquerylen)
FROM   v$undostat;

SELECT begin_date,
u.maxquerylen
FROM   (SELECT To_char(begin_time, ‘DD-MON-YYYY:HH24-MI-SS’) begin_date,
maxquerylen
FROM   v$undostat
ORDER  BY maxquerylen DESC) u
WHERE  ROWNUM < 11;

SELECT begin_date,
u.maxquerylen
FROM   (SELECT maxquerylen,
To_char(begin_time, ‘DD-MON-YYYY:HH24-MI-SS’) begin_date
FROM   v$undostat
ORDER  BY maxquerylen DESC) u
WHERE  ROWNUM < 26
ORDER  BY begin_date ASC,
maxquerylen DESC;

SELECT begin_date,
u.maxquerylen
FROM   (SELECT maxquerylen,
To_char(begin_time, ‘DD-MON-YYYY:HH24-MI-SS’) begin_date
FROM   v$undostat
ORDER  BY maxquerylen DESC) u
WHERE  ROWNUM < 26
ORDER  BY maxquerylen DESC;

SELECT SUM(u.maxquerylen) / 25 AvgUndoRetTime
FROM   (SELECT maxquerylen
FROM   v$undostat
ORDER  BY maxquerylen DESC) u
WHERE  ROWNUM < 26;

SELECT SUM(u.maxquerylen)
FROM   (SELECT maxquerylen
FROM   v$undostat
ORDER  BY maxquerylen DESC) u
WHERE  ROWNUM < 26;

 

DBA_UNDO_EXTENTS

set linesize 152
col tablespace_name FOR a20
col status FOR a10
SELECT tablespace_name,
status,
Count(extent_id)                    “Extent Count”,
SUM(blocks)                         “Total Blocks”,
SUM(bytes) / ( 1024 * 1024 * 1024 ) spaceInGB
FROM   dba_undo_extents
GROUP  BY tablespace_name,
status
ORDER  BY tablespace_name;

 

Mapping Undo Segments to usernames

SELECT s.sid,
s.serial#,
username,
s.machine,
t.used_ublk,
t.used_urec,
( rs.rssize ) / 1024 / 1024 MB,
rn.name
FROM   v$transaction t,
v$session s,
v$rollstat rs,
v$rollname rn
WHERE  t.addr = s.taddr
AND rs.usn = rn.usn
AND rs.usn = t.xidusn
AND rs.xacts > 0;

 

Total Undo Statistics

ALTER SESSION SET nls_date_format=‘dd-mon-yy hh24:mi’;

set lines 120
set pages 2000
SELECT begin_time,
end_time,
undoblks,
txncount,
maxquerylen,
unxpstealcnt,
expstealcnt,
ssolderrcnt,
nospaceerrcnt
FROM   v$undostat;

 

Total Undo Statistics since specified year

 

SELECT ‘TOTAL STATISTICS SINCE Jan 01, 2005 – STATSPACK’
FROM   dual;

set head ON
set lines 152
column undotsn format 999 heading ‘Undo|TS#’;
column undob format 9, 999, 999, 999 heading ‘Undo|Blocks’;
column txcnt format 9, 999, 999, 999, 999 heading ‘Num|Trans’;
column maxq format 999, 999 heading ‘Max Qry|Len (s)’;
column maxc format 9, 999, 999 heading ‘Max Tx|Concurcy’;
column snol format 9, 999 heading ‘Snapshot|Too Old’;
column nosp format 9, 999 heading ‘Out of|Space’;
column blkst format a13 heading ‘uS/uR/uU/|eS/eR/eU’ wrap;
column unst format 9, 999 heading ‘Unexp|Stolen’ newline;
column unrl format 9, 999 heading ‘Unexp|Relesd’;
column unru format 9, 999 heading ‘Unexp|Reused’;
column exst format 9, 999 heading ‘Exp|Stolen’;
column exrl format 9, 999 heading ‘Exp|Releas’;
column exru format 9, 999 heading ‘Exp|Reused’;
SELECT undotsn,
SUM(undoblks)        undob,
SUM(txncount)        txcnt,
Max(maxquerylen)     maxq,
Max(maxconcurrency)  maxc,
SUM(ssolderrcnt)     snol,
SUM(nospaceerrcnt)   nosp,
SUM(unxpstealcnt)
||‘/’
|| SUM(unxpblkrelcnt)
||‘/’
|| SUM(unxpblkreucnt)
||‘/’
|| SUM(expstealcnt)
||‘/’
|| SUM(expblkrelcnt)
||‘/’
|| SUM(expblkreucnt) blkst
FROM   stats$undostat
WHERE  dbid IN (SELECT dbid
FROM   v$database)
AND instance_number IN (SELECT instance_number
FROM   v$instance)
AND end_time > To_date(‘01012005 00:00:00’, ‘DDMMYYYY HH24:MI:SS’)
AND begin_time < (SELECT SYSDATE
FROM   dual)
GROUP  BY undotsn;

 

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