Tuesday, April 04, 2006

Using Resource Profiles

I never had the need to use Resource Profiles extensively. Recently, though, I've had the opportunity to investigate this feature.

First things first, the resouce_limit parameter must be set to TRUE. You can either set it in the init.ora or via ALTER SYSTEM.

Next, you create the profile and assign limits to it. Read the descriptions carefully, though, some of the resource parameters may sound self-explanatory, but aren't. For example, you would think SESSIONS_PER_USER would mean the number of times a particular user can login. In fact, it's the number of concurrent sessions that can run at one time.
SQL> create profile really_small limit
2 sessions_per_user 1
3 cpu_per_session 100
4 cpu_per_call 100
5 connect_time 5
6 /

Profile created.

Then you assign the profile to a particular user:
SQL> alter user jh profile really_small;

User altered.

Just for kicks, you can check that your profile is assigned to your user.
SQL> select username, profile from dba_users where username = 'JH';

USERNAME PROFILE
------------ ---------------
JH REALLY_SMALL

SQL> select resource_name, resource_type, limit
2 from dba_profiles
3 where profile = 'REALLY_SMALL';

RESOURCE_NAME RESOURCE LIMIT
-------------------------------- -------- ------------------
COMPOSITE_LIMIT KERNEL DEFAULT
SESSIONS_PER_USER KERNEL 1
CPU_PER_SESSION KERNEL 100
CPU_PER_CALL KERNEL 100
LOGICAL_READS_PER_SESSION KERNEL DEFAULT
LOGICAL_READS_PER_CALL KERNEL DEFAULT
IDLE_TIME KERNEL DEFAULT
CONNECT_TIME KERNEL 5
PRIVATE_SGA KERNEL DEFAULT
FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
PASSWORD_LIFE_TIME PASSWORD DEFAULT
PASSWORD_REUSE_TIME PASSWORD DEFAULT
PASSWORD_REUSE_MAX PASSWORD DEFAULT
PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
PASSWORD_LOCK_TIME PASSWORD DEFAULT
PASSWORD_GRACE_TIME PASSWORD DEFAULT

16 rows selected.


Your user connects to the database, starts running his monster query, and is promptly disconnected:

$ sqlplus jh/jh@mydb

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Apr 4 20:34:23 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

SQL> select count(*) from all_objects, all_objects, all_objects;
select count(*) from all_objects, all_objects, all_objects
*
ERROR at line 1:
ORA-02392: exceeded session limit on CPU usage, you are being logged off

Sweet.

8 comments:

Anonymous said...

Cool! Thanks for this example!
I just tested it - in 9i it is resource_limit= true.

regards
katrin

Jeff Hunter said...

You are absolutely right, not sure what I was thinking...

Howard J. Rogers said...

Yikes. Not sweet at all, actually. Usually at the point the user gets disconnected, he simply doesn't read the error message, thinks the disconnection is 'one of those things' and logs back on to repeat the exact same query/DML... only to be disconnected again... and again... and again.

Usually, in my experience, it's the fourth or fifth time before the person concerned rings to ask what's going on, which means that a substantial piece of query work has been repeated four or five times, or a huge bit of DML has been performed and rolled back four or five times! It would have been cheaper on the database just to let the guy finish first time!

Understand, too, that Resource Profiles are positively archaic. They've been around since at least version 7, and they are largely deprecated now in favour of resource manager, which is a much more sensible animal: jobs can be stopped with RM before they ever run simply because the optimiser can estimate how long they are likely to run. Alternatively, jobs can be allowed to run, but 'throttled back' so that they don't swamp everything else running on the database. Resource manager is subtler and more flexible than a resource profile, and infinitely preferable.

(Profiles used to enforce password complexity or account locking rules etc. are a different matter again and are in no sense deprecated).

Jeff Hunter said...

Hey, I'm not saying it's the answer to all problems. It's just a feature. How you choose to implement it obviously affects its usability.

Beth said...

I vote for a combination of both profiles and resource manager.

We use profiles to enforce the number of concurrent sessions and to snipe sessions that sit idle too long. Otherwise there's a tendency to a) share userids and b) leave a screen with confidential data up when going to lunch. (Yes, I know a well-managed domain could manage the latter, but I don't work in a nicely controlled corporate environment any more....)

Resource Manager looks way cool for controlling run time and cpu priority. Sadly, our testing has hit a bug that Oracle's trying to resolve. If it ever _does_ work for us I'll post an example on my blog.

Howard J. Rogers said...

There is no longer a need to use a profile to kill off idle sessions, since that's a new feature of 10g's implementation of Resource Manager,

I do agree, however, that setting a limit on the maximum number of sessions is a good security measure, and that remains about the only thing profiles (excepting, again, their password-y bits) are good for. A quick 'alter profile default limit sessions_per_user 2;' is all that's needed there, true enough.

And Jeff, I wasn't having a go at you, so I don't think we need to be defensive about it. I was just pointing out that profiles aren't "Just a feature" (which, together with its inevitable corollorary, 'How you implement it is down to you' always strikes me as a bit of a cop-out: describing features, how they work, their advantages and drawbacks is what Oracle commentators are in the business of, after all). They are a largely obsolete feature, and better, richer functionality can be found elsewhere.

Unspecified bugs affecting unspecified quirks of unspecified implementations of unspecified versions shouldn't be taken (necessarily) as a no vote for that other functionality, either.

Anonymous said...

As someone who has had multiple problems and run into some fairly serious oracle bugs using and/or attempting to use resource manager I would point out that while HJR has some academic points worth considering sanity check points make what Jeff noted well worth considering in certain circumstances. Archaic? Deprecated? Those are terms that describe almost every part of oracle technology except the new features in the latest release.

Anonymous said...

And there's the thing about Resource Manager not recommended for use on a system where more than one instance is running. Infinitely preferable except for such systems...

New features are buggy, but if you're not on the latest feature you're archaic?