Re: Rollback Segment Trouble

Long running queries can indeed cause problems -- however, queries never cause rollbacks to extend. Only DML (insert, update, delete) write into the rollbacks.

Arthur Merar wrote:

> Well, many times the code or software is written by another company.
> As in our case, we are running software not written by us. We make
> post modifications to fit our shop, but the bulk of the code is not
> written by us. In this case, it is hard to know if it is the code
> that is causing the problems........
>
> Also, I figure developers with long running queries can cause problems
> too.......
>
> On Mon, 08 Mar 1999 16:48:45 -0600, "John P. Higgins"
> <jh33378_at_deere.com> wrote:
>
> >Mark,
> >
> >I agree that bad code is most often the cause of rollback problems.
> >
> >However, programmers are not always free to insert commits into their
> >processes. The business requirements might call for true transaction handling:
> >all updates committed or all updates rolled back. Intermediate commits would
> >prevent a total rollback. So, sometimes, you just have to make the rollback
> >bigger!
> >
> >Yes, there are other ways to handle this. If your business transaction must
> >span multiple database transactions, you must provide application rollback
> >code and application locking code on top of the database features.
> >
> >Mark Gumbs wrote:
> >
> >> I'm not a DBA or the greatest knowledge on rollback segments. However,
> >> experiences at my last job proved that everytime rollback segmentts were
> >> eaten up in this fashion, it was due to badly written or untuned code. If
> >> the code was updating lots of rows in a loop and not committing often, the
> >> error came up as well.
> >>
> >> Mark.
> >>
> >> Arthur Merar wrote in message <36e0d0b6.6002768_at_news.chaven.com>...
> >> >
> >> >Hello,
> >> >
> >> >I have a couple of questions revolving around rollback segments. Any
> >> >help would be appreciated.
> >> >
> >> >First, on Thursday I received a call that users were getting messages
> >> >of follback segments not being able to get any more extents. So, I
> >> >first extended all the rollback segments by 15 extents. That did not
> >> >help, so I extended the tablespace by 15, and that did not help
> >> >either. So, finally I shrunk all the rollback segments to optimal and
> >> >that worked.
> >> >
> >> >I used Desktop DBA to do some reverse engineering on the rollback
> >> >segment to see how it was built. Interesting.....although the Optimal
> >> >parameter was specified, there was no value. And, the Optimal column
> >> >in v$rollstat is NULL. Why is this?
> >> >
> >> >What is a good size for the optimal on a rollback segment? These are
> >> >short queries running against this database. Here is the code that
> >> >made all the rollback segments in my database:
> >> >
> >> >CREATE ROLLBACK SEGMENT RB01
> >> > TABLESPACE RBS
> >> > STORAGE (INITIAL 262144
> >> > NEXT 262144
> >> > MINEXTENTS 10
> >> > MAXEXTENTS 500
> >> > OPTIMAL )
> >> >
> >> >What should optimal be?
> >> >
> >> >Also, what should my normal response be when a rollback segment runs
> >> >out of room like that?
> >> >
> >> >Thanks for your help.
> >> >
> >> >Arthur
> >> >amerar_at_unsu.com
> >> >
> >> >
> >
> >
Received on Sun Mar 14 1999 - 16:38:19 CST