Re: Limiting Resource for a Oarticular User

Thanks Richard,Howard,
If thats the case then Profiles are out for me... because I still want the session to continue and not terminate but not use all the resources.

What I am looking for is that the user in question should not take up more than... lets say 50% of the CPU no matter how big or expensive its queries/statements are. This will obviously slow down its performance but that doesnt matter because its not running any high priority stuff. In this way atleast 50% is still available for the other users.In case when the user is not connected all the 100% resources are available to the other users.

I think we can do this in Resource manager but wanted to take your opinions on it . It would be nice to know about any pitfalls if any. I think I came across HJR's post some time ago regarding the success/failure stories on Oracle Resource Manager but am not able to search for it on Google. Was i dreaming....

Also with Oracle Resource manager we can manage the CPU only. Is there a way to manage the memory usage also or not .

Please suggest
Ronnie

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:ahqtt8$tg$1_at_lust.ihug.co.nz...
> ...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 - 09:13:19 CDT