RE: v$rollstat rssize vs v$transaction used_ublk
- From: "Chitale, Hemant K" <Hemant-K.Chitale@xxxxxx>
- To: <sundarmahadevan82@xxxxxxxxx>
- Date: Mon, 4 Aug 2014 12:11:28 +0800
A Rollback / Undo segment can hold undo for *multiple* transactions.
Therefore, you should not use RSSIZE if you want to know the undo space for a
transaction (which is the same as looking at current undo by a session since a
session can be running only 1 transaction at any time {unless you use
autonomous transactions which I would consider an “exception” scenario}.
Hemant K Chitale
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Sundar Mahadevan
Sent: Friday, August 01, 2014 9:08 PM
To: Riyaj Shamsudeen
Cc: Oracle-L@xxxxxxxxxxxxx
Subject: Re: v$rollstat rssize vs v$transaction used_ublk
Thanks wvry much Riyaj.
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@xxxxxxxxx>
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 <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> Expert RAC Practices 12c.
<http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices
<http://tinyurl.com/book-expert-plsql-practices>
On Thu, Jul 31, 2014 at 5:45 AM, Sundar Mahadevan <sundarmahadevan82@xxxxxxxxx>
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
This email and any attachments are confidential and may also be privileged. If
you are not the intended recipient, please delete all copies and notify the
sender immediately. You may wish to refer to the incorporation details of
Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at
https://www.sc.com/en/incorporation-details.html.
Other related posts:
