Re: Limiting Resource for a Oarticular User

...and when you've implemented profiles like this, sit back and wait for the guy_you_dont_like to chew through far *more* resources than he ever would have done if you'd just left him alone.

What I mean is that Richard is spot on: when the resource limit is reached, the session is terminated (or the job is, if you use call-level limits) without warning... at which point Oracle has to rollback any transaction the user was, say, 75% of the way through. Already, 75% doing it, plus 75% rollback is 150% of the work! But it gets worse: the user is likely not to understand why his session just got booted, and assume it was just a cosmic ray strike or the like.... so he'll just log straight back in, and re-launch the job he was doing (you know, the one Oracle's just had to roll back!).

Guess what... he'll get booted again, the job will roll back once more, and we're up to 300% of the work.

And it's kinda downhill thereafter, if you've a particularly curmudgeonly user that just keeps on trying...

I have to say that I think profiles were invented for Oracle 7... and that's where they belong!

But YMMV
Regards
HJR "Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:_T009.44000$Hj3.133529_at_newsfeeds.bigpond.com...
> Hi Ronnie,
>
> Regarding using Resource Manager, it all depends on what you want to do.
> Some things it does well, some things not so well. The fact that you're on
> 8.1.7 means you don't have access to some of the new capabilities of
> resource manager such as estimated execution time, undo quotas and the
such.
> I would recommend researching RM's options and consider their suitability
to
> your particular environment.
>
> In answer to your question of whether there are other options, 'Profiles'
is
> probably what you're looking for. A profile is an object that contains
> various session and statement limitations that can be assigned to a
user(s).
> These resource limitations include CPU, logical I/O, no of sessions, idle
> time, connect time and mts session memory assigned to the SGA and CPU ,
> logical I/O for individual statements.A profile can also be used to change
> password settings such as expiry times and the such.
>
> Steps are:
>
> 1) CREATE PROFILE guys_I_dont_like LIMIT
> CPU_PER_CALL 1000
> LOGICAL_READS_PER_SESSION 1000;
>
> as an example (note any resources not listed remain unlimited)
>
> 2) ALTER USER guy_who_scratched_my_car
> PROFILE guys_I_dont_like;
>
> to assign a profile to a user
>
> 3) ALTER SYSTEM SET RESOURCE_LIMIT=true;
>
> to enable the policing of these resource limits. Once PMON detects a
session
> or statement that exceeds a resource limit, the session or statement is
> doomed (note though that PMON may not detect these infringements
> immediately).
>
> It's all good stuff and is particular useful to get back at people that
make
> life difficult.
>
> This is documented in the concepts and administration guides.
>
> Good Luck
>
> Richard
>
>
> "Ronnie Yours" <ronnie_yours_at_yahoo.com> wrote in message
> news:ahprtl$ok4$1_at_nntp-m01.news.aol.com...
> > Hi,
> >
> > Ours is an OLTP system running on 8.1.7.
> >
> > How do I limit the resources used by a Particular user (In my Case only
> one
> > known user) so that it doesnt hog up all the resources.
> >
> > I know this can be done using Resource manager, but after reading some
> posts
> > here that Resource manager is not the Best way to go, I dont want to use
> > it... atleast on 8i.
> >
> > Is there any other way I can do it.
> >
> > Please suggest
> >
> > Ronnie
> >
> >
> >
>
>
Received on Fri Jul 26 2002 - 02:32:36 CDT