monitoring rollback segments
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: monitoring rollback segments

  1. #1
    Join Date
    Aug 2000
    Posts
    163
    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.

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Are the rollback segments created with an optimal storage parameter??

  3. #3
    Join Date
    Feb 2001
    Posts
    389
    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

  4. #4
    Join Date
    Aug 2000
    Posts
    163

    HWM an optimal

    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.

  5. #5
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    optimal and hwmsize are in v$rollstat

  6. #6
    Join Date
    Aug 2000
    Posts
    163
    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

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Aug 2000
    Posts
    163

    still need help

    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.

  9. #9
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    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;

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width