Re: Restricting user's resource access

Hi Jonathan, Pete and all,

What I was trying to say is that by using Resource Manager you can prevent expensive/inefficient code from even starting if Oracle predicts it will take too long.

This is *NOT* dependent at all on current CPU usage. You could be the only person logged on but if the code you're about to execute is too expensive, Oracle will issue an error and not even attempt to start it. The beauty with this of course is that you don't waste resources on half completing a query that's doomed to die.

Yes you can set up Resource Manager to "switch" this expensive code into another consumer group which may have lower priority CPU (rather than just failing the code). However the default is to have "SWITCH_GROUP" set to null.

To test, I assigned a user to a consumer group which was in a resource plan with a max_est_exec_time = 2 (meaning 2 seconds, I only run "really" efficient code here).

After activating the plan, I connected as this user and tried to run a piece of code but received the following error:

ERROR at line 1:
ORA-07455: estimated execution time (67 secs), exceeds limit (2 secs)

even though the user was the only one logged on. CPU was at zip. Oracle aborted this sql before it even started !!

Going back to the original poster's question, this is a very nice way to restrict expensive code from running. You can only do so via estimated execution times (which means the CBO needs to get it right so that's an important issue) but in many ways this has advantages over using a profile.

Hope this makes sense.

BTW Jonathan, I'm ready for you this time (I've got a lovely umbrella and a water proof jacket to match :)

Regards

Richard

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:1027249781.7369.1.nnrp-10.9e984b29_at_news.demon.co.uk...
>
> I may be wrong here, but the pre-emptive option
> allows the resource manager to pre-emptively switch
> your resource group if it thinks you are going to be
> too greedy, rather than switching you after you have
> proved to be too greedy. Is there an option to block
> execution completely ?
>
> The problem that I've found with resource manager is
> that the CPU scheduling method is very lenient - it
> only restricts your CPU usage when the resource is
> dramatically overloaded. So I have engineered cases
> where the demand on CPU is 200%, but two 'low
> resource' users still manage to get almost as much
> CPU as two 'high resource' users (specifically on
> 80/20 CPU2 values).
>
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminars
> UK Sept
> Australia August
> Malaysia September
> USA x 2 November
>
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Richard Foote wrote in message ...
> >Hi Pete,
> >
> >No, I think this could be very useful. This restriction kicks in
> >"immediately" in that it will prevent an expensive SQL from executing,
> >regardless of current CPU usage. This means you can proactively prevent
> >expensive code that could hurt your CPU performance from ever running.
> >
> >It might not be exactly what Red was after but it might be worth
> >investigating.
> >
> >Cheers
> >
> >Richard
>
>
>
Received on Sun Jul 21 2002 - 22:24:13 CDT