Home » RDBMS Server » Performance Tuning » Rollback segment too small error (oracle10g)
Rollback segment too small error [message #415525] Tue, 28 July 2009 03:25 Go to next message
deepbans
Messages: 32
Registered: February 2009
Member
HI,

I am running a SQL query which contains n number of join including 2-3 outer join.Almost all the tables in my query are transactional table.

At the time query executes,The records in the tables are updated and SCN number of the records is changed and its also not available in the rollback segments.Then,its throwing ORA-01555
rollback segment too small error.

We have tuned the query from our side and we cant modify anything from DBA side part

Can you please suggest something to overcome this error?

Thanks & Regards.

Report message to a moderator

Re: Rollback segment too small error [message #415532 is a reply to message #415525] Tue, 28 July 2009 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ Do not do a lot of stuff in a loop
2/ Load the result (of not to big) in a PL/SQL table
3/ Add an "order by" clause

Regards
Michel

Report message to a moderator

Re: Rollback segment too small error [message #415541 is a reply to message #415532] Tue, 28 July 2009 03:43 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
Thanks.

1.we are not putting anything in loop.
2.Sometime data is very huge.Can we opt for global temporary table.
But I think there will be chances of occurrence of error at the time of insertion in table. Is there anything through which we can skip that record or data block which is causing issue.
3.Can you pls explain little how order by clause going to help in this?


Regards.

Report message to a moderator

Re: Rollback segment too small error [message #415544 is a reply to message #415541] Tue, 28 July 2009 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1. The longest is the treatment in the loop the highest is the likelyhood to get ORA-1555

2. You can

3. "order by" is like a temporary table, you put the result in temp segment allocated to your session

Regards
Michel

Report message to a moderator

Re: Rollback segment too small error [message #415545 is a reply to message #415544] Tue, 28 July 2009 03:56 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
1.Can you please tell me how can we skip particular record in a query and directly fetch the record succeeding that record.

2.our record size is very high sometime its more than 10 MB.
and "order by" will reduce the performance.


Regards,
Deepa

Report message to a moderator

Re: Rollback segment too small error [message #415550 is a reply to message #415545] Tue, 28 July 2009 04:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1. Don't select it in the first place.

Regards
Michel

Report message to a moderator

Re: Rollback segment too small error [message #415552 is a reply to message #415550] Tue, 28 July 2009 04:05 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
But we dont know which records are causing issue.

Report message to a moderator

Re: Rollback segment too small error [message #415558 is a reply to message #415552] Tue, 28 July 2009 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You want to skip the record that cause ORA-1555?
It is not possible to know which one it would be (even if ever it would be one).

Also do not commit inside the loop, if ever you do it.

Regards
Michel

[Edit: add missing word]

[Updated on: Tue, 28 July 2009 09:57]

Report message to a moderator

Re: Rollback segment too small error [message #415650 is a reply to message #415525] Tue, 28 July 2009 09:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>But we dont know which records are causing issue.
The SQL reporting the ORA-01555 error is the victim, not culprit.
The root cause of the problem is some other session is doing DML against same table as victim SQL & also doing COMMIT.

Possible solutions include
1) do not do DML while victim query runs
2) do not do COMMIT until after victim SQL completes
3) make RBS larger (no guarentee this always works)

visit http://asktom.oracle.com & do KEYWORD search on ORA-01555

[Updated on: Tue, 28 July 2009 09:24]

Report message to a moderator

Re: Rollback segment too small error [message #415716 is a reply to message #415650] Wed, 29 July 2009 00:13 Go to previous message
deepbans
Messages: 32
Registered: February 2009
Member
Our query run for a reporting tool which fetches the latest data only.
We can't Stop these DML operations happening on these tables.

Regards

[Updated on: Wed, 29 July 2009 00:39]

Report message to a moderator

Previous Topic: No parallel DML with IOTs
Next Topic: performance issue oracle 10g RAC
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Nov 22 13:02:44 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.