Re: Limiting Resource for a Oarticular User

Hi Howard,

As usual I generally agree.

I see profiles that restrict CPU and I/O to be more beneficial when controlling queries rather than DML. In your example I agree that potentially more resources could be gobbled up enforcing these restrictions. When applied to users who have query only access and who could easily run inefficient or long running queries at inappropriate production times, then profiles could still be useful. If it means stopping these queries at the 1% mark or after a number of minutes (rather than 75% or after a number of hours) then even more so. However, I agree with your general argument that you still must run 1% of the work at least before a profile has any effect. That's 1% wasted effort. And that could be 1% wasted effort repeated many times until the user eventually gives up and tries again a 8:00pm when the resource_limit is turned off.

I like the new max_est_exec_time limit implemented via Resource Manager in 9i. This prevents expensive code from even starting (as the CBO now calculates an estimated time value and compares this with the m.e.e.t.) and as such offers real benefits over profiles. I've recently discussed this in another post.

Thanks for keeping me honest :)

Richard

"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 - 07:04:37 CDT