Who's the big transaction?

Everyone:

I've straightened out a bunch of serious rbs problems (and other stuff, like insufficient memory and swap, some transactions that needed to be broken up, etc.), now I'm getting into the more subtle things.

I decided to shrink the rbs's down to their initial 10M sizes (I had changed a lot of other stuff, removed optimal, didn't want to mess too much with this before things stabilized, intended to consider Mr. Rogers excellent posts very seriously when I did adjust them), and see what happens, figuring they'd all grow to some fairly constant size if there is a repetitive large transaction. After a month+, this is how things look:

SQL> select usn, extents,hwmsize, shrinks, wraps, extends from v$rollstat;

       USN EXTENTS HWMSIZE SHRINKS WRAPS EXTENDS ---------- ---------- ---------- ---------- ---------- ----------

         0          9     745472          0          0          0
        26          5   10641408          0          9          0
         1          5   10641408          0         11          0
         2          8   17031168          0         15          0
         3          5   10641408          0          9          0
         4         13   27680768          0          8          0
         5          5   10641408          0         10          0
         6          6   12771328          0          7          0
         7          5   10641408          0         10          0
         8         45   95838208          0         57         40
         9          5   10641408          0          6          0
        10         16   34070528          0         16          0
        11         38   80928768          0         10          0
        12          8   17031168          0          6          0
        13          5   10641408          0          8          0
        14          5   10641408          0         11          0

16 rows selected.

I was kind of expecting a smoother distribution across the rbs's. So question number 1: Does Oracle consider the rbs size when allocating to a transaction? I was under the distinct impression it did not. I guess it is possible that I did a better job than I expected breaking up the periodic huge updates... if the answer is "no," then Question Number 2 becomes, how do you catch an infrequent huge transaction?

usn 11 and usn 4 grew to their size within a couple of days of the shrinkage. usn 8 did very little until a few days ago.

There had been no ORA-1555's until this morning, when I was running a huge report (usn 2 is the one that blew up...). I hadn't been worrying too much about huge reports since I hope to be putting a reporting instance on another box. But until that actually happens...

I am writing a cron that would periodically query v$rollstat, and if it sees a change, run the script from metalink Note:1039126.6 to see who's on what rollback, the idea being a big transaction should be around for at least a few minutes...

Anyone have another solution? I'm guessing http://www.ixora.com.au/scripts/rbs.htm#rollback_reuse_time and such are not useful, since the undo is so inconsistent, although I haven't tried it. I see this as the converse problem.

At some point, I've read most all the stuff out there on the subject, but most of it is buried deep within the folds of my brain, haven't refreshed recently. Pointers gladly accepted.

8.1.7 on 4 processor K570, hp-ux 11.0, some kinda RAID which may be changing shortly. Generally processor bound, will be converting to a bigger and faster box. Packaged "Thin client" finance/manufacturing system with some customization.

open_cursors = 7000
db_block_buffers = 73728
shared_pool_size = 78643200
large_pool_size = 614400
java_pool_size = 0
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 300
db_block_size = 8192

No other strange init.ora's set. Yes, open_cursors must be that.

TIA jg

--
@home is bogus.
http://www.garry.to