RE: v$rollstat rssize vs v$transaction used_ublk

From: Chitale, Hemant K <Hemant-K.Chitale_at_sc.com>
Date: Mon, 4 Aug 2014 12:11:28 +0800
Message-ID: <0BDF2A25A09ADD40908745EEFC0A0FB60217EF50_at_HKJUMXMB103B.zone1.scb.net>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sundar Mahadevan Sent: Friday, August 01, 2014 9:08 PM
To: Riyaj Shamsudeen
Cc: Oracle-L_at_freelists.org
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_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 <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_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

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.
--

http://www.freelists.org/webpage/oracle-l Received on Mon Aug 04 2014 - 06:11:28 CEST

This message: [ Message body ]
Next message: Jeff C: "EM12c Disk Alerts - Name Disk"
Previous message: Veerabasaiah C: "Re: Query Performing slow after upgrade to 11g"
In reply to: Sundar Mahadevan: "Re: v$rollstat rssize vs v$transaction used_ublk"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message