Re: set a rollback segment for a specific user

Richard Foote wrote:

> Hi Daniel,
>
> I think the answer to your question is the very big danger to performance
> for the great many who would abuse such an option.
>
> Currently, Oracle does a pretty good job of automatically spreading the load
> across the available rollback segments (even better so in 9i). Potential
> issues with specifying a RBS for a user includes:
>
> 1) Performance issues if users are not correctly spread across the RBSs
> (note if you can set it for one user, you can set it for all)
>
> 2) How to determine the amount of undo a particular group of users will
> generate at a particular point in time. Real danger of specific RBS being
> overloaded, while other RBS have no load if this is not calculated correctly
> (and it is virtually impossible to calculate correctly)
>
> 3) Issues of specific RBS being available
>
> When I close my eyes and imagine all the issues that can arise if RBSs were
> assigned to individual users, it's not a pretty sight. The fact that Oracle
> spreads undo load per transaction is actually a very clever and efficient
> way to manage things. The fact that undo segments now can interact and
> "borrow" storage from each other is very nice (when it works :(.
>
> The option to set a transaction to use a specific RBS was the way to have
> some control over this (which is now of course unnecessary in 9i (when it
> works :( again.
>
> I for one would really question such a user option that assigned an RBS to a
> user. It would cause many more issues than it solved.
>
> Then again, I could be wrong :)
>
> Richard
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3D41797B.8E889314_at_exesolutions.com...
> > Pete Sharman wrote:
> >
> > > In article <ahrlbh$vi73t$1_at_ID-82797.news.dfncis.de>, "Steven" says...
> > > >
> > > >Hi,
> > > >
> > > >I'm wondering if it is possible to set a specific rollback segment for
> a
> > > >user so that user only uses the specified rollback segment. My problem
> is
> > > >that sometimes I perform large transactions and want to use the large
> > > >rollback segment. However, I cannot specify the rollback segment using
> "set
> > > >transaction......" because I am accessing the data through an
> application.
> > > >
> > > >Any help will be appreciated,
> > > >Regards,
> > > >Steve.
> > > >
> > > >
> > > >
> > > Nope, Niall's approach is the only way that this will work. Even then,
> you're
> > > not guaranteed that other users won't use the same rollback segment.
> > >
> > > HTH. Additions and corrections welcome.
> > >
> > > Pete
> > >
> > > SELECT standard_disclaimer, witty_remark FROM company_requirements;
> >
> > Which leads to the obvious question. Why hasn't Oracle implemented private
> > rollback segments? I can assign a user a default tablespace, a temp
> tablespace,
> > why not user-specific rollback? It seems to me it would be a technical
> no-brainer.
> >
> > CREATE USER xyz
> > IDENTIFIED BY xyz
> > DEFAULT TABLESPACE abc
> > TEMPORARY TABLESPACE def
> > ROLLBACK SEGMENT ghi
> > QUOTA ... ON
> >
> > It would just be one more way for DBAs to control resource utilization and
> I/O.
> >
> > Daniel Morgan
> >

I would totally agree with you with one exception.

That is the very large number of systems on which I have been called in where they used:

SET TRANSACTION ... to try to beat the rollback segments and ended up making such a huge and horrific mess as to be worthy of little but pity.

Nothing Oracle could do could be worse that what developers do when they find a tool, misunderstand it, misapply it, and misuse it.

It would be nice to have an option that actually did what people seem to think they need.

For me ... I'd like it for doing those 2:00am batch jobs that need one massive RBS when everything else on the system is OLTP.

Daniel Morgan Received on Mon Jul 29 2002 - 10:54:10 CDT