Re: Rollback Segment Trouble

That is wrong: A SELECT writes data into the rollback because in tnis way oracle guarantees consistent read (cursor stability). This is the second task of rollback in oracle (look for detailled explanation of error ORA-1555). Another important information is, that the rollback segment will shrink only when it is used again. IMHO the resolution will be, to increase the size of the roolback tablespace. So there are some points to pay attation to: - the size of the rollback tablespace - the number of rollback segments - the size of the rollback segments (i.e. optimal, next, maxextents) It is difficult to adjust and there is no formula to calculate these parameters. I myselfe have still an unresolved ORA-1555-problem :-( . Here some ideas: . TS should be max(hwmsize) * count(rollback segments) + x (e.g. 20 percent) . optimal should be increased if you have lots of shrinks . the number of rollback segments should be big enough to avoid ORA-1555, but not so many that rollbacks blown up by large transactions stay long in the TS until they are reused. Good luck Johannes

In article <36EC3A5A.F60D5F89_at_deere.com>,   "John P. Higgins" <jh33378_at_deere.com> wrote:
> 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
> > >> >
> > >> >
> > >
> > >
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Mar 16 1999 - 11:18:01 CST