Oracle 11g and Expert Systems Technology

Oracle 11g - The V$ views for Rollback Segments

The V$ Views for rollback segments are use primarily to see the activity within the rollback segments and if the sizing for the rollback segments is correct, both for the number of rollback segments and the size of each rollback segment.

Create the owner and assign the required privileges as shown below. Execute the following statement, where DBUSER is the name of the user to create, and tsname is the tablespace for UIM. Rollstat® mats are available for both carpeted and hard flooring. We recommend our Series 63 for parquet, stone or felt flooring in gray with black dots. Series 54 in gray is preferred for heavily stressed area or deep pile carpets.

There are only two views that display rollback segments v$rollname and v$rollstat. By themselves, they provide relatively little information about the transactions within the rollback segments.

SQL> select * from v$rollname;

USN NAME
---------- ------------------------------
0 SYSTEM
1 R01
2 R02

1* select * from v$rollstat
SQL> /

USN EXTENTS RSSIZE WRITES XACTS GETS WAITS
---------- ---------- ---------- ---------- ---------- ---------- ----------
OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE
---------- ---------- ---------- ---------- ---------- ---------- ----------
STATUS CUREXT CURBLK
--------------- ---------- ----------
0 6 364544 83054 0 2762 0
364544 0 2 0 0 11673
ONLINE 4 5

1 2 62910464 180560312 0 136120 2
62910464 0 6 0 0 14221502
ONLINE 0 2174

2 7 115339264 312589954 0 252856 1
115339264 0 18 5 0 15815730
ONLINE 0 6879

Now, the real value of the v$ views is gained when we join into the v$lock and v$process views. When we do this, we can formulate a query that shows the transactions attached to each rollback segment.

Roll Stats 5e

select
r.name, l.Sid, p.spid,
NVL(p.username, 'no transaction') 'Transaction',
p.terminal 'Terminal'
from
v$lock l,
v$process p,
v$rollname r
where
l.Sid = p.pid (+)
and TRUNC(l.id1(+) / 65536) = r.usn
and l.type(+) = 'TX'
and l.lmode(+) = 6
order by r.name;
spool off

set pages 22 lines 80 verify on feedback on

clear columns

ttitle off

SQL> @tx_rbs

Madeline stunt cars 2gamerate. Rollback Segment Nam SID Sys PID Transaction Terminal
-------------------- ---------- --------- --------------- ----------
R02 no transaction
R03 no transaction
R04 42 no transaction
R05 no transaction
R06 no transaction
R07 no transaction
R08 no transaction
R09 no transaction
R10 no transaction
SYSTEM no transaction

prompt
prompt **********************************************************
prompt Rollback Segment Section
prompt **********************************************************
prompt if any count below is > 1% of the total number of requests for data
prompt then more rollback segments are needed
prompt if free list > 1% then increase FREELIST in init.ora

--column count format 999,999,999
select class, count
from v$waitstat
where class in ('free list','system undo header','system undo block',
'undo header','undo block')
group by class,count;

column 'Tot # of Requests for Data' format 999,999,999,999
select sum(value) 'Tot # of Requests for Data' from v$sysstat where
name in ('db block gets', 'consistent gets');

prompt
prompt
prompt ROLLBACK SEGMENT CONTENTION
prompt
prompt
prompt If any ratio is > .01 then more rollback segments are
needed

column 'Ratio' format 99.99999
select name, waits, gets, waits/gets 'Ratio'
from v$rollstat a, v$rollname b
where a.usn = b.usn;

SQL> @r

**********************************************************
Rollback Segment Section
**********************************************************
if any count below is > 1% of the total number of requests for data
then more rollback segments are needed
if free list > 1% then increase FREELIST in init.ora

CLASS COUNT
------------------ ----------
free list 0
system undo block 0
system undo header 0
undo block 4
undo header 15200

5 rows selected.

Tot # of Requests for Data
--------------------------
1,181,227,581

Rollstate

1 row selected.

ROLLBACK SEGMENT CONTENTION

Roll Stats 5e Roll20

If any ratio is > .01 then more rollback segments are needed

NAME WAITS GETS Ratio
------------------------------ ---------- ---------- ---------
SYSTEM 0 1543 .00000
R02 7 15244 .00046
R03 41 36377 .00113
R04 12 42878 .00028
R05 2 35338 .00006
R06 6 38273 .00016
R07 10 745617 .00001
R08 6 23638 .00025
R09 10 727022 .00001
R10 5 29006 .00017

Allstate

Well, we made it! Let’s wrap-up the major concepts in this module.

Roll States

Roll Statistics