Oracle RDBMS version: 19.16
Standalone DB in Archivelog mode
This is a question on rollback monitoring. Not actually a performance tuning question.
HRTB_RTS_DTL table is less than 2 GB in size (1.81 GB). I ran the below DELETE in a development environment and then I did a rollback.
It went for a full table scan as there is no index on CREATE_DT column.
The DELETE took 10 minutes, but the rollback took 47 minutes despite this DB being almost idle.
SQL> DELETE FROM HRTB_RTS_DTL WHERE (CREATE_DT < sysdate-5);
9561442 rows deleted.
Elapsed: 00:10:21.67
SQL> rollback;
Rollback complete.
Elapsed: 00:47:09.39
After the DELETE, I did a rollback. While the rollback was running, I ran the below query from another session to monitor the rollback.
So, when the rollback is running, USED_UBLK should be reducing as the rollback progresses (and it did)
The SIZEGB column should be reducing as well and it did as shown below. But, why is the UNDO used showing 10+ GB (SIZEGB column value below) when the entire table size is less than 2 GB ?
Or is the below query wrong ?
set lines 200
col sid for 99999
col username for a15
col name for a25
col machine for a20
select s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,rn.name,(t.used_ublk*8)/1024/1024 SizeGB
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;
SID SERIAL# USERNAME MACHINE USED_UBLK USED_UREC NAME SIZEGB
------ ---------- --------------- -------------------- ---------- ---------- ------------------------- ----------
4025 25829 SYS lxdb268.domn.net 1359162 97133569 _SYSSMU10_2262159254$ 10.3695831
SQL> /
SID SERIAL# USERNAME MACHINE USED_UBLK USED_UREC NAME SIZEGB
------ ---------- --------------- -------------------- ---------- ---------- ------------------------- ----------
4025 25829 SYS lxdb268.domn.net 1338579 95663459 _SYSSMU10_2262159254$ 10.2125473
-- After 40 minutes
SID SERIAL# USERNAME MACHINE USED_UBLK USED_UREC NAME SIZEGB
------ ---------- --------------- -------------------- ---------- ---------- ------------------------- ----------
4025 25829 SYS lxdb268.domn.net 167929 12018759 _SYSSMU10_2262159254$ 1.28119659
--- After 45 minutes
SID SERIAL# USERNAME MACHINE USED_UBLK USED_UREC NAME SIZEGB
------ ---------- --------------- -------------------- ---------- ---------- ------------------------- ----------
4025 25829 SYS lxdb268.domn.net 383 27259 _SYSSMU10_2262159254$ .002922058
SQL> /
no rows selected ------ Rollback completed in the other session
SQL>