Re: set a rollback segment for a specific user

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
>
Received on Sat Jul 27 2002 - 20:10:02 CDT