Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    23

    Unanswered: Rollback Segment Alerts/warnings

    We have two databases (on separate servers) with the same application serving different users in different countries. One is live and the other is in test mode. I have OEM alerts and the capacity planner set up to monitor both databases with exactly the same parameters set up.

    For the rollback segment monitoring, I used alert setting:
    No. of Occurances = 3 (default)
    Alert threshold (%) = 1 (default)
    Warning threshold (%) = .1 (default was 0)

    With the live database, we get no alerts (critical) and very few warnings. On our test database, we get no alerts (critical) and lots of warnings, almost 1 every 10-15 minutes. I am trying to determine why we get so many warnings on test.

    Initially, the rollback segments were defined identically on both databases. I recently increased the size and number of rollback segments on the test database but this does not seem to have any affect on the number of alerts. Looking at the V$ROLLSTAT data (See below), There is very little to go on for the test database. No shrinks or extends, and very few wraps (1).

    Does anyone have some insight to what is occurring here? Is there any downside (other than space) to sizing rollback segments larger than may be needed? Are my alerts/warning set up at too low of a threshold?

    THANKS


    LIVE: (very few warnings)
    USN EXTENTS RSSIZE WRITES XACTS GETS WAITS OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE STATUS CUREXT CURBLK
    0.00 281.00 18,489,344.00 4,280.00 0.00 1,288.00 0.00 18,489,344.00 0.00 0.00 0.00 0.00 0.00 ONLINE 41.00 2.00
    1.00 2.00 8,380,416.00 8,090,314.00 0.00 24,374.00 0.00 8,388,608.00 8,380,416.00 0.00 2.00 0.00 0.00 796,180.00 ONLINE 0.00 170.00
    2.00 2.00 8,380,416.00 21,853,020.00 0.00 27,164.00 0.00 8,388,608.00 20,963,328.00 1.00 4.00 3.00 12,582,912.00 3,794,827.00 ONLINE 0.00 458.00
    4.00 2.00 8,380,416.00 8,222,414.00 0.00 25,010.00 1.00 8,388,608.00 8,380,416.00 0.00 2.00 0.00 0.00 96,180.00 ONLINE 0.00 493.00
    5.00 3.00 12,574,720.00 19,749,788.00 0.00 26,602.00 0.00 8,388,608.00 12,574,720.00 0.00 5.00 1.00 0.00 2,093,894.00 ONLINE 0.00 142.00
    6.00 2.00 8,380,416.00 9,335,470.00 0.00 25,706.00 0.00 8,388,608.00 8,380,416.00 0.00 3.00 0.00 0.00 1,135,173.00 ONLINE 1.00 87.00
    7.00 2.00 8,380,416.00 9,337,088.00 0.00 24,979.00 0.00 8,388,608.00 8,380,416.00 0.00 2.00 0.00 0.00 796,098.00 ONLINE 1.00 160.00
    8.00 2.00 8,380,416.00 28,986,534.00 0.00 27,762.00 0.00 8,388,608.00 25,157,632.00 1.00 7.00 4.00 16,777,216.00 5,603,579.00 ONLINE 0.00 425.00
    9.00 2.00 8,380,416.00 17,364,890.00 0.00 26,197.00 0.00 8,388,608.00 8,380,416.00 0.00 4.00 0.00 0.00 1,440,937.00 ONLINE 1.00 220.00
    10.00 2.00 8,380,416.00 11,080,674.00 0.00 26,189.00 0.00 8,388,608.00 8,380,416.00 0.00 3.00 0.00 0.00 1,135,173.00 ONLINE 1.00 41.00
    3.00 2.00 8,380,416.00 9,969,022.00 0.00 25,100.00 0.00 8,388,608.00 8,380,416.00 0.00 3.00 0.00 0.00 ,135,173.00 ONLINE 1.00 185.00


    TEST: (many warning)
    USN EXTENTS RSSIZE WRITES XACTS GETS WAITS OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE STATUS CUREXT CURBLK
    0.00 5.00 401,408.00 6,460.00 0.00 1,318.00 0.00 401,408.00 0.00 0.00 0.00 0.00 0.00 ONLINE 0.00 4.00
    1.00 5.00 14,671,872.00 492,004.00 0.00 4,265.00 0.00 26,214,400.00 14,671,872.00 0.00 0.00 0.00 0.00 0.00 ONLINE 0.00 686.00
    2.00 11.00 20,963,328.00 491,990.00 0.00 4,264.00 0.00 26,214,400.00 20,963,328.00 0.00 0.00 0.00 0.00 0.00 ONLINE 0.00 627.00
    3.00 5.00 14,671,872.00 550,584.00 0.00 7,537.00 0.00 26,214,400.00 14,671,872.00 0.00 0.00 0.00 0.00 0.00 ONLINE 0.00 412.00
    4.00 5.00 14,671,872.00 490,126.00 0.00 4,263.00 0.00 26,214,400.00 14,671,872.00 0.00 1.00 0.00 0.00 104,857.00 ONLINE 3.00 5.00
    5.00 5.00 14,671,872.00 505,364.00 0.00 4,262.00 0.00 26,214,400.00 14,671,872.00 0.00 0.00 0.00 0.00 0.00 ONLINE 0.00 1,196.00
    6.00 5.00 14,671,872.00 491,304.00 0.00 4,260.00 0.00 26,214,400.00 14,671,872.00 0.00 0.00 0.00 0.00 0.00 ONLINE 0.00 1,009.00
    7.00 5.00 14,671,872.00 488,142.00 0.00 4,260.00 0.00 26,214,400.00 14,671,872.00 0.00 0.00 0.00 0.00 0.00 ONLINE 0.00 632.00
    8.00 5.00 14,671,872.00 489,458.00 0.00 4,260.00 0.00 26,214,400.00 14,671,872.00 0.00 0.00 0.00 0.00 0.00 ONLINE 0.00 1,201.00
    9.00 5.00 14,671,872.00 491,570.00 0.00 4,260.00 0.00 26,214,400.00 14,671,872.00 0.00 0.00 0.00 0.00 0.00 ONLINE 0.00 418.00
    10.00 5.00 14,671,872.00 491,306.00 0.00 4,260.00 0.00 26,214,400.00 14,671,872.00 0.00 0.00 0.00 0.00 0.00 ONLINE 0.00 675.00
    11.00 8.00 17,817,600.00 487,294.00 0.00 4,258.00 0.00 26,214,400.00 17,817,600.00 0.00 1.00 0.00 0.00 104,857.00 ONLINE 7.00 50.00
    12.00 5.00 14,671,872.00 489,722.00 0.00 4,256.00 0.00 26,214,400.00 14,671,872.00 0.00 0.00 0.00 0.00 0.00 ONLINE 0.00 587.00
    13.00 5.00 14,671,872.00 491,712.00 0.00 4,256.00 0.00 26,214,400.00 14,671,872.00 0.00 0.00 0.00 0.00 0.00 ONLINE 0.00 721.00
    14.00 5.00 14,671,872.00 488,932.00 0.00 4,256.00 0.00 26,214,400.00 14,671,872.00 0.00 0.00 0.00 0.00 0.00 ONLINE 0.00 764.00
    15.00 5.00 14,671,872.00 486,942.00 0.00 4,256.00 0.00 26,214,400.00 14,671,872.00 0.00 0.00 0.00 0.00 0.00 ONLINE 0.00 598.00

  2. #2
    Join Date
    Aug 2003
    Posts
    23
    Crap. That V$ROLLSTAT output did not stay tab-delimited. How can I get that to format nicely here in the post?

    Alternatively, you can cut/paste it into notepad and it looks a little better - space delimited.

    THANKS

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    What is being done on the testdatabase that doesnot happen on the production? Is there heavy traffic or some heavy batchjobs running ?
    What is the testdatabase being used for ?

    Greetz
    Edwin van Hattem
    OCP DBA / System analyst

  4. #4
    Join Date
    Aug 2003
    Posts
    23
    The test database is actually in "pre-live" mode. So it's being used in much the same way as the live db, just by a different set of users. Perhaps there are a few differences between the two site's workflows but the db usage is basically the same. I have a query to the on-site manager to find out if there are any unusual bulk loads or high data entry going on, but have yet to hear back. Will post again when more info is available.

    Do you think the alerts/warnings thesholds are appropriate? I only receive warning and the values are in the .13 to .73 range. If there is no reported performance problems for this range of warnings, why even report them? The only reason I have them is they are (near) the oracle defaults.

    THANKS

  5. #5
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    Hi,

    the alerts and warnings threshold Oracle provided are kinf of universal. Some say they fit everybody, I say they fit nobody.
    It's kind a hard for Oracle to provide defaults that work for everybody. So yoiu'll just have to try and find the threshold which suits your needs. All those things depend on the need of availability, the kind of database (test or live) and all kinds of individual things.
    Just trial and error i guess.

    I'll check out later when you have heard about the possibility of unusual bulk loads.
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

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