Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Q: rollback sizing pt 2
Bear in mind that , for any given transaction, the # of rollback segments is not relevant: Only 1 will be used so it needs to
be big enough to handle the entire transaction - you can commit more often, or create a very large rollback segment and
specify it with the 'SET TRANSACTION USE ROLLBACK SEGMENT MY_NEW_BIG_RBS' ( your name will vary) as the FIRST statement
after connecting to run the big batch...( I keep one offline and only bring it online when I want to use it, that way no
other transaction gets it)
hth
Vince Laurent <eAddict_at_yahoo.com> wrote:
>My PSAPROLL table is currently 5.4G. I have 20 identical rollback
>segements. I still get A LOT of 1555 errors - esp on batch jobs
>running at night. Here is what I am seeing right now:
>
>Name Extends Gets HWMSize Wraps Avg Actv Waits
>----- ------- ------ --------- ---- --------- ----
>PRS_0 4 88240 19161088 120 2414587 0
>PRS_1 14 93020 29810688 136 2336032 0
>PRS_2 20 99058 29810688 147 2332290 9
>PRS_3 16 98388 31940608 145 2583112 16
>PRS_4 144 155675 178905088 284 10352191 67
>PRS_5 9 100595 23420928 144 2261469 4
>PRS_6 10 95611 21291008 138 5616607 2
>PRS_7 46 115365 97968128 167 2179500 46
>PRS_8 10 98889 21291008 145 2913379 15
>PRS_9 16 99217 27680768 138 3380851 8
>PRS_10 18 595968 38330368 150 10095050 2
>PRS_11 15 87944 31940608 125 2195350 2
>PRS_12 14 103694 29810688 146 2362446 15
>PRS_13 12 95758 27680768 132 2159040 2
>PRS_14 15 99031 21291008 149 4712310 2
>PRS_15 11 97991 21291008 150 4222024 6
>PRS_16 0 85402 10641408 117 2590132 0
>PRS_17 8 90411 27680768 126 2541240 2
>PRS_18 8 92401 27680768 125 2319735 2
>PRS_19 9 89203 29810688 110 2555406 3
>
>Total Waits: 203
>Total Gets: 2484902
>Ratio of Waits to Gets: 0.00817%
>
>Avg Actv: Avg # of bytes in active extents in the rbs
> measured over time
>(the above chart was generated by the FREE tool "orastat")
>
>In order to make the chart fit, I took some columns out. All segments
>have 505 max extents, inital and next of 10640408, all are ONLINE, and
>all have an Optimal size of 10485760. (all set up as SAP defaults)
>
>According to SAP Note 185822, the hwmsize should be smaller than
>initial+(max*extents). That is the case (as that number would be
>1,061,158,912).
>
>According to SAP Note 3807, having a initial of 10M and a next of 10M
>each should be able to grow to 10*505 or 5G...
>
>My question is: Would it be better to
>(a) add another datafile to the PSAPROLL table and make it bigger than
> 5.4G?,
>(b) add more rollback segements,
>(c) reduce the number of MAX_EXTENTS,
>(d) increase the size of the individual rollbacks,
>
>or a combination of above.
>
>Rollbacks are confusing.... thanks for any help!
>Vince
>--------------------------------------
>Come race with us!
>www.mgpmrc.org
Received on Thu Aug 14 2003 - 10:56:50 CDT