monitoring rollback segments

Hi there,
I'd like to monitor how my rollback tablespace runs out of space all the time. I don't want to extend it just yet. I'd like to see if someone is creating some silly joins first.
Please help.

Are the rollback segments created with an optimal storage parameter??

Check if any one for the rollback segments HWM is much-much higher than all the others, check for the optimal storage parameter.
From v$rollstat , check for extends and aveactive size , if high , increase rollback segment size and tablespace.

From v$sysstat , check consistent gets and db block gets and from v$waitstat check system undo block wait , if ratio is higher than 1% increase rollback segment size and tablespace and rollback segments.

thanks
GP

How do I chekc HWM and optimal for my rollback segs?
I checked dba_segments and dba_rollback_segs. I didn't see anything related to HWM and optimal there.

optimal and hwmsize are in v$rollstat

never mind., I found it.
there is another question: how do I know which rollback seg is which? It doesn't have a ROLLBACK_NAME column. Another words: how do I link this view with dba_rollback_segs?
desc v$rollstat;
------------------------
Name Null? Type
------------------------------- -------- ----
USN NUMBER
EXTENTS NUMBER
RSSIZE NUMBER
WRITES NUMBER
XACTS NUMBER
GETS NUMBER
WAITS NUMBER
OPTSIZE NUMBER
HWMSIZE NUMBER
SHRINKS NUMBER
WRAPS NUMBER
EXTENDS NUMBER
AVESHRINK NUMBER
AVEACTIVE NUMBER
STATUS VARCHAR2(15)
CUREXT NUMBER
CURBLK NUMBER

Mary,

"silly joins" can't fill your rollback tablespace, don't look at that dirrection! Only transactions (that is updates, inserts, deletes) are recorded in rollback. Joins take place in selects, which are not DMLs, so they could not influence the usage of your rollbacks. "Silly joins" can fill up only your temporary tablespace if they require extensive sorting (like in merge joins).

So you better look for "silly DMLs". Here is an example of such a silly DML, where developers often don't think what is going on backstage, they only wan't to get the required result. Lets say you have 10 millions of rows in table TAB1. Incolumn COL1 95% of rows have value of 1, 1% have value of 0 and the rest 4% have some other values. A developer gets the task to set COL1=1 in all rows except in the rows where COL1=0.

So he simply translates this bussiness requirenment in the following UPDATE:

UPDATE tab1 SET col1 = 1 WHERE col1 != 0;

With this update he gets the correct result, but at what cost? He updated 9,900,000 rows, and all those updates gets recorded in rollback segment.

If he adds just a simple additional condition not to update those rows which allreday have the desired value in COL1, like

UPDATE tab1 SET col1 = 1 WHERE col1 != 0 AND col1 != 1;

he would get the same result, but he would actually update only 40,000 rows! That means about 25 times less undo information written in rollback segment (and about the same reduction in generated redos)!

HTH,

THANK YOU. You're absolutely right. Selects are not recorded in rollback segs.
I am still a little lost.
Anyone can walk me through the process of evaluating of space usage by my rollback segments. I am looking for as detail instructions as possible. Or may be there is documentation available I can look at. I was not able to follow instructions above.
Thank you everyone.

This query maps the rbs info with the rbs name:
select a.col1, a.col2, ..., b.name from v$rollstat a, v$rollaname b where a.usn=b.usn;

Marry,

Sorry, while I was writing my previous post you informed us you've found out the reason for your RB growth. So ju can simply ignore what I have written.

Now to your second question:

The first column in v$rollstat is USN which stands for Undo Segment Number, in other words rollback segment number. You can get the name of the particular segment number by looking at DBA_ROLLBACK_SEGS (columns SEGMENT_NAME and SEGMENT_ID).

HTH,

Mr.Halo,

Another question,
Is USN in v$rollstat , the same as segment_id in
dba_rollback_segs?

Badrinath

thank you Jmodic.. You're the best!