Re: Stop users from connecting to database

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:5P2za.39780$1s1.563671_at_newsfeeds.bigpond.com...
>
> "Peter" <peter_at_nomorenewsspammin.ca> wrote in message
> news:f8uncvsoe5qfp33jv0bi2vgnhghjfr4vtd_at_4ax.com...
> > On Tue, 20 May 2003 11:17:24 GMT, b.sandmann_at_gmx.net (Bernd Sandmann)
> > wrote:
> >
> > How about creating a role and only allow that role to do batch jobs?
> >
>
>
> Since a role is a collection of privileges, and there's no privilege to
> distinguish a 'batch job' from a non-batch job, this isn't going to get
you
> anywhere.
>
> The best I can think of is to use 8i's Resource Manager. There, you create
> groups of users, such as "DBAs", "BATCH" and "OLTP". You can't stop a
group
> member logging on, but by creating a Resource Plan that says 'BATCH users
> get 0% of the CPU during normal hours', you can lock them up so they can't
> actually do anything. (The options get subtler in 9i... let them log on,
let
> them do work, but if one of their jobs is *estimated* to take longer than
X
> seconds, lock them up, for example).
>
> You can then create another plan for non-normal hours, where BATCH members
> get 100% of the CPU.
>
> Resource Manager seems to me to be the way to go.
>

Hi Howard,

Hate to disagree :(

Resource Manager is only effective (from a CPU point of view) once the CPU starts to max out. Oracle takes the (somewhat) reasonable attitude that if I've got spare CPU capacity, why not give it to a poor bugger that may only have a low CPU priority. Only when the resource is scarce am I going to get picky with it.

I'm currently looking at implementing this at my current site but it's important to understanding it's limitations.

For example:

SQL> execute dbms_resource_manager.create_pending_area();

PL/SQL procedure successfully completed.

SQL> execute dbms_resource_manager.create_consumer_group -
> (consumer_group => 'THE_HAVES', comment => 'Get access');

PL/SQL procedure successfully completed.

SQL> execute dbms_resource_manager.create_consumer_group -
> (consumer_group => 'THE_HAVE_NOTS', comment => 'Dont get access');

PL/SQL procedure successfully completed.

SQL> execute dbms_resource_manager.create_plan -
> (plan => 'TEST', comment => 'Test');

PL/SQL procedure successfully completed.

SQL> execute dbms_resource_manager.create_plan_directive -
> (plan => 'TEST', group_or_subplan => 'THE_HAVES', -
> comment => 'Testing', cpu_p1 => 100);

SQL> execute dbms_resource_manager.create_plan_directive -
> (plan => 'TEST', group_or_subplan => 'THE_HAVE_NOTS', -
> comment => 'Testing', cpu_p1 => 0);

PL/SQL procedure successfully completed.

• so I've created a plan directive that gives the poor have nots zip CPU

  ---

SQL> execute dbms_resource_manager.create_plan_directive -
> (plan => 'TEST', group_or_subplan => 'OTHER_GROUPS', -
> comment => 'Testing', cpu_p2 => 100);

PL/SQL procedure successfully completed.

SQL> execute dbms_resource_manager.validate_pending_area();

PL/SQL procedure successfully completed.

SQL> execute dbms_resource_manager.submit_pending_area();

PL/SQL procedure successfully completed.

SQL> execute dbms_resource_manager_privs.grant_switch_consumer_group -
> (grantee_name => 'BOWIE', consumer_group => 'THE_HAVE_NOTS', -
> grant_option => false);

PL/SQL procedure successfully completed.

SQL> execute dbms_resource_manager.set_initial_consumer_group -
> (user => 'BOWIE', consumer_group => 'THE_HAVE_NOTS');

PL/SQL procedure successfully completed.

• so poor Bowie is in a group that should have no CPU. Does this mean he can effectively do nothing ???? ****

SQL> alter system set resource_manager_plan = test;

System altered.

SQL> connect bowie/password
Connected.

SQL> select count(*) from big_bowie;

  COUNT(*)

    290262

So he's fine. There's plenty of free CPU so even though the group that Bowie group belongs to "looks" like having no CPU, that doesn't have any effect in this case.

However, we do have a plan B with 9i but even plan B has it's limitations ...

SQL> execute dbms_resource_manager.create_pending_area();

PL/SQL procedure successfully completed.

SQL> execute dbms_resource_manager.create_plan -
> (plan => 'TEST2', comment => 'Use 9i Feature');

PL/SQL procedure successfully completed.

SQL> execute dbms_resource_manager.create_plan_directive -
> (plan => 'TEST2', group_or_subplan => 'THE_HAVES', -
> comment => 'Testing', cpu_p1 => 100);

PL/SQL procedure successfully completed.

SQL> execute dbms_resource_manager.create_plan_directive -
> (plan => 'TEST2', group_or_subplan => 'THE_HAVE_NOTS', -
> comment => 'Testing', cpu_p1 => 0, max_est_exec_time => 0);

PL/SQL procedure successfully completed.

• now we're going to prevent the have nots from running any operation that has an estimated execution time > 0. This should do the trick .... *****

SQL> execute dbms_resource_manager.create_plan_directive -
> (plan => 'TEST2', group_or_subplan => 'OTHER_GROUPS', -
> comment => 'Testing', cpu_p2 => 100);

PL/SQL procedure successfully completed.

SQL> execute dbms_resource_manager.validate_pending_area();

PL/SQL procedure successfully completed.

SQL> execute dbms_resource_manager.submit_pending_area();

PL/SQL procedure successfully completed.

SQL> alter system set resource_manager_plan = test2;

System altered.

SQL> connect bowie/password
Connected.
SQL> select count(*) from big_bowie;
select count(*) from big_bowie
*
ERROR at line 1:
ORA-07455: estimated execution time (56 secs), exceeds limit (0 secs)

Hey, way to go. This time poor Bowie has be told to nick off !!

However, the problem here is that Oracle kinda rounds *down* the estimate execution time so providing I'm not being too greedy with my resources, Oracle can be a little lenient ...

SQL> select count(*) from little_bowie;

  COUNT(*)

      9216

and he's OK.

So Resource Manager really isn't the way to go ...

Cheers

Richard Received on Thu May 22 2003 - 09:09:05 CDT