Re: v$rollstat rssize vs v$transaction used_ublk

Would appreciate if you or anyone else could clarify what is significance of v$rollstat rssize and when it must be used. The documentation does not clearly explain this.

http://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_2143.htm

RSSIZE NUMBER Size (in bytes) of the rollback segment. This value differs by the number of bytes in one database block from the value of the BYTES column of the *_SEGMENTS view.

See Also: Oracle Database Administrator's Guide <http://docs.oracle.com/cd/B28359_01/server.111/b28310/toc.htm>

On Thu, Jul 31, 2014 at 5:44 PM, Riyaj Shamsudeen < riyaj.shamsudeen_at_gmail.com> wrote:

> Hi Sundar
> Yes, used_ublk is the correct column to query to identify session's undo
> usage.
>
> Cheers
>
> Riyaj Shamsudeen
> Principal DBA,
> Ora!nternals - http://www.orainternals.com - Specialists in Performance,
> RAC and EBS
> Blog: http://orainternals.wordpress.com/
> Oracle ACE Director and OakTable member <http://www.oaktable.com/>
>
> Co-author of the books: Expert Oracle Practices
> <http://tinyurl.com/book-expert-oracle-practices/>, Pro Oracle SQL,
> <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert RAC
> Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices
> <http://tinyurl.com/book-expert-plsql-practices>
>
> <http://tinyurl.com/book-expert-plsql-practices>
>
>
>
> On Thu, Jul 31, 2014 at 5:45 AM, Sundar Mahadevan <
> sundarmahadevan82_at_gmail.com> wrote:
>
>> 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