Re: rollback questions

"Vince Laurent" <eAddict_at_yahoo.com> wrote in message news:lu570vcth18jsdc453dls1erggpeok9ct5_at_4ax.com...
> I am trying to understand rollbacks, at least understanding how SAP
> uses them and to tune them. We have been getting more ORA-01555s
> since our database crossed the 100G size. Right now the rollbacks are
> set to the default install. Here are the stats from a program called
> orastat (plug:GREAT TOOL!):
>
> sapprd:oraprd 3> ./orastat -ra
> 2002/12/20-16:19:35 orastat | oraver=8.1.7 oraver3=8.1 sqlcmd=sp
> 2002/12/20-16:19:35 orastat | ORACLE_SID=PRD
> ORACLE_HOME=/oracle/PRD/817_64
> 2002/12/20-16:19:35 orastat | Version=2.33
> orastat | Rollback Segments Statistics
>
> RSSize : Current rollback segment size in bytes
> -column removed, all 10641408 bytes (10M)
>
> A Xn's : Number of current transactions using the rollback segment
> -column removed, all currently 0
>
> HWMSize : High Water Mark size in bytes
> Wraps : Cumulative # of times a X'n continues writing from one
> extents to another existing extent
> Avg Actv: Avg # of bytes in active extents in the rbs
> measured over time
>
> Name Extends Gets HWMSize Wraps Avg Actv Waits
> ------ ------- ------ ---------- ------ --------- ------
> PRS_0 7 130478 25550848 176 2345674 12
> PRS_1 6 124295 19161088 162 2210725 2
> PRS_2 4 123095 19161088 161 2232942 0
> PRS_3 1 125297 12771328 163 2136828 1
> PRS_4 12 153932 36200448 154 17202515 3
> PRS_5 28 150456 63889408 178 2647096 5
> PRS_6 31 144235 42590208 210 8988732 15
> PRS_7 4 127866 19161088 172 2810635 1
> PRS_8 65 143432 138436608 232 95312420 2
> PRS_9 4 129341 19161088 175 2206473 2
> PRS_10 12 127924 31940608 174 2222152 3
> PRS_11 0 121028 10641408 143 2130736 0
> PRS_12 0 121148 10641408 146 2147608 0
> PRS_13 62 142333 142696448 222 2131274 1
> PRS_14 42 141261 91578368 223 6116854 3
> PRS_15 5 130087 19161088 179 2132704 4
> PRS_16 48 137197 108617728 210 2602272 2
> PRS_17 120 166012 191684608 310 2893642 2
> PRS_18 17 128125 38330368 175 4018525 1
> PRS_19 20 131124 53239808 186 15698495 0
>
> Questions:
> If the rollbacks are 10M each, how can the high-watermark be higher?
> Since SAP doesn't seem to use all of them equally, should a few
> smaller but LARGER ones work better?
>
> Thanks!
> Vince

Vince,

At first sight, they look way too small. But it depends:

How many concurrent transactions do you have? How many concurrent active sessions?
How frequently is the database restarted? Do you have overnight batch jobs running? What type of system is this (OLTP, DW, mixed)?

Sorry, I have no experience of SAP, so other responders may provide better pointers.

Regards,
Paul Received on Sat Dec 21 2002 - 11:30:44 CST