| Massive Rollback Segment [message #54461] |
Tue, 19 November 2002 07:17  |
denni50
Messages: 18
Registered: November 2002
|
Junior Member |
|
|
Hello
Hoping someone can shed some light on the following situation.
I have created a Massive Rollback Segment that I keep
offline. It is only used for extremely large updates.
I have a table with approx 2.5m records(Donor table)..
the donors are split between two different clients(client A,client B....approx 1.25m each client).
When I need to update the records by client I switch
on the Massive RS and switch off the regular RS(s).
I have even included in my update script a counter:
If ctr = 35000 Then
COMMIT;
ctr:=0;
End If;
That counts the number of records going through the
loop and commits the updated records when the recordcount reaches 35,000.
When I update the records for client A,no problems.
Then I begin updating the records for client B..and
the dreaded "RS Snapshot too old..." error appears.
The exact thing I was trying to avoid using the massive
Rollback Segment with unlimited maximum number.
Why is this still happening?
thanks in advance
denni50
|
|
|
|
| Re: Massive Rollback Segment [message #54462 is a reply to message #54461] |
Tue, 19 November 2002 07:29  |
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
|
Senior Member |
|
|
1. Is anyone else using the system while this task is being performed ??
2. I presume that you are explicitely setting the BIG rbs. After commit, did you again explicitely assign the BIG rbs ? After any DDL, set the rbs to the desired one (BIG one in this case).
|
|
|
|
| Re: Massive Rollback Segment [message #54467 is a reply to message #54461] |
Tue, 19 November 2002 07:44 |
denni50
Messages: 18
Registered: November 2002
|
Junior Member |
|
|
Sanjay
thanks for reply..
I run the update late at night when no one else
is on the system.
As I explained...I then switch ON the BIG_RS and
switch OFF the REGULAR_RS(s).
I get through the 1st update with ClientA and have
no problems.
When I begin updating CientB that is when it happens.
you quote:
"After commit, did you again explicitely assign the BIG rbs ? "
could you explain what you mean by this.
thx..denni50
|
|
|
|
| Re: Massive Rollback Segment [message #54471 is a reply to message #54467] |
Tue, 19 November 2002 08:21 |
B
Messages: 327
Registered: August 1999
|
Senior Member |
|
|
after each DDL and commit transaction are done ..
so u should explicitly set RBS.
May be U should monitor ur RBS .. I'm thinking that Ur batch don't use only ur BIG RBS.
When U get snapshot too old .. there is a RBS name .. is ur big RBS ???
|
|
|
|
| Re: Massive Rollback Segment [message #54473 is a reply to message #54467] |
Tue, 19 November 2002 08:29 |
denni50
Messages: 18
Registered: November 2002
|
Junior Member |
|
|
Sanjay
thanks for your clarification. I even printed out
your response.
So, if I understand you correctly, should I remove
the commit statement in my loop(commit after every
35,000 records)and do one FINAL COMMIT after the
loop is completed. Remember we are updating 1.25m
records for each client.
From what I understand of your response..I need
to explicitely switch back ON BIG_RS after every
commit. If I am committing after every 35000 records
for ClientB then I would have to explicitely force(switch ON) BIG_RS 35 times during the update...
this would definitely be redundant and not efficient.
I thought I could just simply switch ON BIG_RS once..update both clients (one after the other)..then
switch BIG_RS OFF and switch back ON the regular Rollback Segments.
Specs:
Rollback Tablespace Size=4,000.000 Used=700.023
BIG_RS:
initial size = 102400 kb
next = 102400 kb
min number = 2
max number value = 32765
again thanks,
denni50
|
|
|
|
| Re: Massive Rollback Segment [message #54475 is a reply to message #54473] |
Tue, 19 November 2002 09:07 |
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
|
Senior Member |
|
|
Couple of more details needed:
1. how many big_rbs do you have ? Looks like you have only 1.
2. When you said used=700.023, you are using ONLY 7 extents (of the big_rbs) ? right
3. During the batch job, how many rbs do you have ONLINE? Are all of them big ? I am presumming that you take ALL the regular rbs (small ones) ofline and have ONLY the big ones online. You can check the 'snapshot too..' error and see which rbs had the problem.
As for the COMMIT part, that is a big trial and error thing. I would say commit after the 1.25 mill mark. Sounds funny, but it is true.
We have scripts with commit at 1.5 mill with the biggest load at ~ 20 mill rows. RBS tablespace is 5.5 GB. rbs01 is init=10M next=10M pctincrease=0 minextents=5 with 5 of those (rbs01-05). These are the only rbs segments online.
Good luck.
|
|
|
|
| Re: Massive Rollback Segment [message #54487 is a reply to message #54473] |
Tue, 19 November 2002 13:45 |
Kossaku Nakombi
Messages: 12
Registered: November 2002
|
Junior Member |
|
|
ORA-01555 is due to read consistency issues. After the end of a transaction, the information stored in RBS are not cleaned immediately, and this can be used to reconstruct the consistent image of your data. The reference to the RBS is cleaned when u make the first access to the modified record after the commit.
You can avoid the overlap of this data (in RBS) using RBS with more extents. I recomend the use of RBS with at least 20 minextents and you can try with higher values. Another way is to force the RBS cleanup by issuing FTS before the start of your transaction (not possible in many situations).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
