Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Limiting Resource for a Oarticular User

Re: Limiting Resource for a Oarticular User

From: Pete Sharman <peter.sharman_at_oracle.com>
Date: 26 Jul 2002 07:58:53 -0700
Message-ID: <ahro3d01ukt@drn.newsguy.com>


In article <ahrlbq$69r$1_at_nntp-m01.news.aol.com>, "Ronnie says...
>
>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

Ronnie

I can't think of anything that will do this in 8i. As I think Richard pointed out, you do have control over this in 9i with Resource Manager, but not in the 8i version. Whether this is sufficiently good reason to upgrade is of course something that only you can decide.

Pete
>
>
>
>
>"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
>> > >
>> > >
>> > >
>> >
>> >
>>
>>
>
>

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Fri Jul 26 2002 - 09:58:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US