v$rollstat rssize vs v$transaction used_ublk

From: Sundar Mahadevan <sundarmahadevan82_at_gmail.com>
Date: Thu, 31 Jul 2014 08:45:00 -0400
Message-ID: <CADmQEr6ZdC=SQ0p205pgzEauHRqexjqXu9paJBo+LC=xt0izjA_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jul 31 2014 - 14:45:00 CEST