Undo Related Queries

https://dbissues.blogspot.com/2017/11/undo-related-queries.html

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;