v$rollstat rssize vs v$transaction used_ublk
- From: Sundar Mahadevan <sundarmahadevan82@xxxxxxxxx>
- To: "Oracle-L@xxxxxxxxxxxxx" <Oracle-L@xxxxxxxxxxxxx>
- Date: Thu, 31 Jul 2014 08:45:00 -0400
Hi All,
I am trying to find out which session is using the undo tablespace. I
notice 72GB of active undo being used. But I am not able to come to any
conclusion as to which one should be used to map undo usage to a session?
Is it rssize from v$rollstat in bytes or is it used_ublk from v$transaction
in number of blocks. From the numbers below, I am inclined to think that
used_ublk from v$transaction is closer to 72GB active undo size. Please
clarify. Thanks.
select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB from
dba_undo_extents group by tablespace_name, status;
TABLESPACE_NAME STATUS GB
------------------------------ --------- --------------------
UNDOTBS1 UNEXPIRED 24.17645263671875
UNDOTBS1 EXPIRED .00128173828125
UNDOTBS1 ACTIVE 72.9659423828125
3 rows selected.
SQL Statement which produced this data:
select 'ROLLBIG' rollbig,a.sid, a.username,a.osuser, a.machine, b.xidusn,
b.used_urec, b.used_ublk, b.used_ublk * 8192/1024/1024 UNDO_USED_IN_MB ,
b.used_ublk *8192/1024/1024/1024 UNDO_USED_IN_GB ,
d.name,c.extents,c.rssize/1024/1024
RSsize_in_MB, c.rssize/1024/1024/1024 RSsize_in_GB
from v$session a, v$transaction b,v$rollstat c,v$rollname d
where a.saddr=b.ses_addr
and b.xidusn=c.usn
and b.xidusn=d.usn
ROLLBIG|SID|USERNAME|OSUSER|MACHINE|XIDUSN|USED_UREC|USED_UBLK|
*UNDO_USED_IN_MB|UNDO_USED_IN_GB*|NAME|EXTENTS|*RSSIZE_IN_MB|RSSIZE_IN_GB*
ROLLBIG|861|ANALYTICAL|ABCD|11QL1W1-DER|11|51045872|9571102|
*74774.234375|73.0217132568359*|_SYSSMU11_941321016$|1447|
*1053.0859375|1.02840423583984*
SQL Statement which produced this data:
select * from v$transaction
ADDR|XIDUSN|XIDSLOT|XIDSQN|UBAFIL|UBABLK|UBASQN|UBAREC|STATUS|START_TIME|START_SCNB|START_SCNW|START_UEXT|START_UBAFIL|START_UBABLK|START_UBASQN|START_UBAREC|SES_ADDR|FLAG|SPACE|RECURSIVE|NOUNDO|PTX|NAME|PRV_XIDUSN|PRV_XIDSLT|PRV_XIDSQN|PTX_XIDUSN|PTX_XIDSLT|PTX_XIDSQN|DSCN-B|DSCN-W|USED_UBLK|USED_UREC|LOG_IO|PHY_IO|CR_GET|CR_CHANGE|START_DATE|DSCN_BASE|DSCN_WRAP|START_SCN|DEPENDENT_SCN|XID|PRV_XID|PTX_XID
00000049B0922F28|11|9|19495922|689|2956218|20397|3|ACTIVE|07/29/14
17:20:59|674640805|1253|1022|763|174002|18950|12|0000004A01E0CC50|3587|NO|NO|NO|NO|null|0|0|0|0|0|0|0|0|9573651|51059468|204093255|4708220|104290716|45873068|29-Jul-2014
5:20:59
PM|0|0|5382268662693|0|0B000900F27B2901|0000000000000000|0000000000000000
Other related posts:
